MySQL中的锁机制

MySQL锁的分类：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

MyISAM与InnoDB表锁和行锁的解释

MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。

InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候，InnoDB是检测不到的。只能依靠锁定超时来解决。

MySQL中的乐观锁和悲观锁

悲观锁：
  每次去拿数据的时候都认为别人会修改，屏蔽一切可能违反数据完整性的操作。所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。 Java synchronized 就属于悲观锁的一种实现，每次线程要修改数据时都先获得锁，保证同一时刻只有一个线程能操作数据，其他线程则会被block。

流程如下：

在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。

如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。

如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。

其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

优点与不足：

  悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

乐观锁：
  每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。

乐观锁一般来说有以下2种方式：

• 使用数据版本（Version）记录机制实现：
    这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
• 使用时间戳（timestamp）：
    乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

优点与不足：

  乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

MyISAM的锁调度

  MyISAM存储引擎的读和写锁是互斥，读操作是串行的。那么，如果一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，则写进程先获得锁。不仅如此，即使读进程先请求先到锁等待队列，写请求后到，写锁也会插到读请求之前！这是因为MySQL认为写请求一般比读请求重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！我们可以通过一些设置来调节MyISAM的调度行为。

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

  另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL变暂时将写请求的优先级降低，给读进程一定获得锁的机会。
  上面已经讨论了写优先调度机制和解决办法。这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题。因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

InnoDB锁问题

  InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。 行级锁和表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

1.事务（Transaction）及其ACID属性 事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

• 原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2.并发事务带来的问题 相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

• 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。

基本两种思路，一种是悲观锁，另外一种是乐观锁； 简单的说就是一种假定这样的问题是高概率的，最好一开始就锁住，免得更新老是失败；另外一种假定这样的问题是小概率的，最后一步做更新的时候再锁住，免得锁住时间太长影响其他人做有关操作。

1.悲观锁：

a）传统的悲观锁法（不推荐）：

  以上面的例子来说明，在弹出修改的页面初始化时（这种情况下一般会去从数据库查询出来），在这个初始化查询中使用select ……for update nowait， 通过添加for update nowait语句，将这条记录锁住，避免其他用户更新，从而保证后续的更新是在正确的状态下更新的。然后在保持这个链接的状态下，在做更新提交。当然这个有个前提就是要保持链接，就是要对链接要占用较长时间，这个在现在web系统高并发高频率下显然是不现实的。

b）现在的悲观锁法（推荐优先使用）：

  在修改工资这个页面做提交时先查询下，当然这个查询必须也要加锁（select ……for update nowait），有人会说，在这里做个查询确认记录是否有改变不就行了吗，是的，是要做个确认，只是你不加for update就不能保证你在查询到更新提交这段时间里这条记录没有被其他会话更新过，所以这种方式也需要在查询时锁定记录，保证在这条记录没有变化的基础上再做更新，若有变化则提示告知用户。

2.乐观锁：

a）旧值条件（前镜像）法：

  就是在sql更新时使用旧的状态值做条件，SQL大致如下 Update table set col1 = newcol1value， col2 = newcol2value…。 where col1 = oldcol1value and col2 = oldcol2value…。，在上面的例子中我们就可以把当前工资作为条件进行更新，如果这条记录已经被其他会话更新过，则本次更新了0行，这里我们应用系统一般会做个提示告知用户重新查询更新。这个取哪些旧值作为条件更新视具体系统实际情况而定。（这种方式有可能发生阻塞，如果应用其他地方使用悲观锁法长时间锁定了这条记录，则本次会话就需要等待，所以使用这种方式时最好统一使用乐观锁法。）

b）使用版本列法（推荐优先使用）：

  其实这种方式是一个特殊化的前镜像法，就是不需要使用多个旧值做条件，只需要在表上加一个版本列，这一列可以是NUMBER或 DATE/TIMESTAMP列，加这列的作用就是用来记录这条数据的版本（在表设计时一般我们都会给每个表增加一些NUMBER型和DATE型的冗余字段，以便扩展使用，这些冗余字段完全可以作为版本列用），在应用程序中我们每次操作对版本列做维护即可。在更新时我们把上次版本作为条件进行更新。在对一行进行更新的时候 限制条件=主键+版本号，同时对记录的版本号进行更新。

  事务提交以后，看最后一步更新操作的记录更新数是否为1，如果不是，则在业务上提示重试。（表明此时更新操作的并发度较高。）

伪代码如下：

start transaction;  
select attributes, old_version from table where primary_key = ?  
attribute Merge operations  
update table set version = old_verison + 1 , attributes_column = attributes_value where primary_key = ? and version = old_version  
commit;   

• 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务并未提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。

  解决办法：如果在第一个事务提交前，任何其他事务不可读取其修改过的值，则可以避免该问题。

• 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

  解决办法：如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。

• 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

  解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题

事务隔离级别

• 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

  即便是事务没有commit，但是我们仍然能读到未提交的数据，这是所有隔离级别中最低的一种，会造成脏读

• 提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

  当前会话只能读取到其他事务提交的数据，未提交的数据读不到。我们在同一个事务中，会读取到两次不同的结果，造成了不可重复读。

• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

  当前会话可以重复读，就是每次读取的结果集都相同，而不管其他事务有没有提交。

• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

  将当前会话的隔离级别设置为serializable的时候，其他会话对该表的写操作将被挂起。这是隔离级别中最严格的，但是这样做势必对性能造成影响。所以在实际的选用上，要根据当前具体的情况选用合适的。