MySQL 锁(Lock)问题处理

第一部分 锁问题

1.1 丢失更新

丢失更新是一个经典的数据库问题，实际上，所有多用户计算机系统环境下都有可能产生这个问题，简单来说，出现下面的情况时，就会发生丢失更新：

1. 事务T1查询一行数据，放入本地内存，并显示给一个终端用户user1；

2. 事务T2也查询该行数据，并将取得的数据显示给终端user2；

3. User1修改这行记录，更新数据并提交；

4. User2修改这行记录，更新数据并提交。

  显然，这个时候user1的更新操作丢失了，这在生产环境是很糟糕的，设想是银行的账户金额操作，影响甚大。

要避免这种丢失更新，我们就要将并发的操作变成串行操作，即在上述第1种情况下，对用户读取的记录加上一个排他锁，同样，发生第2种情况的操作时，用户也需要加一个排他锁，这样，第2步就必须等到1、3完成，最后完成第4步的操作。如下图所示：

 1.2 脏读

脏页：脏页指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘，即数据库实例内存中的页和磁盘的页中的数据是不一致的，当然，在刷新到磁盘之前，日志都已经被写入了重做日志文件中。

脏数据：脏数据是指在缓冲池被修改的数据，并且还没有被提交。

所以，以上的脏读指的是读取未提交的脏数据，至于脏页的读取，是非常正确的，脏页是由于数据库实例内存和磁盘的异步写入造成的，但是并不影响数据的一致性，同时还会提高性能。而读取了脏数据，就违反了数据库的隔离性。

脏读指的是在不同的事务下，可以读到其他事务未提交的数据，简单来说，就是可以读到脏数据，如下如所示：

 以上例子，我们的隔离级别设置为READ UNCOMMITTED，因此在会话A中事务没有提交的前提下，会话B中两次select操作取到了不同的结果，并且这来给你个记录是在会话A中未提交，即产生了脏读，违反了事务的隔离性。

脏读现象在生产环境中不常发生，而且脏读的条件是隔离界别设置未READ

UNCOMMITTED，而目前大部分的数据库都至少设置为READ COMMITTED，InnoDB存储引擎默认为READ REPEATABLE，SQL server和Oracle使用的都是READ COMMITTED。

1.3 不可重复读

不可重读读是指在一个事务内多次读取到同一数据。在这个事务还没有结束时，另外一个事务也访问该数据。那么，在第一个事务两次访问数据之间，由于第二个事务的修改，第一个事务两次读取到的结果不一样，因此称为不可重复读。

不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读是读已提交的数据，但是违反了事务一致性的原则，如下图所示：

 

 以上例子，我们的隔离级别设置为READ COMMITTED，一般情况下，很多厂商也会选择READ COMMITTED，所以不可重复读是允许的。因为事务已经提交。

其实，不可重复读也是可以避免的，在MySQL官方文档中，利用Next-key Lock的算法，对于索引的扫描，不仅仅是锁住扫描到的索引，而且还会锁住这些索引覆盖的范围（gap）。因此在这个范围内的插入都是不允许的，也就避免了不可重复读的问题。而Innodb默认引擎READ REPEATABLE就是采用了Next-key Lock算法，避免了不可重复读现象。

第二部分 阻塞 

因为不同锁之间的兼容性关系，所以在有些时刻，一个事务中的锁需要等待另一个事务中的锁释放它所占的资源。

在innodb存储引擎中，参数innodb_lock_wait_timeout用来控制等待时间，默认是50秒，innodb_rollback_on_timeout用来设定时头在等待超时时对进行中的事务进行回滚操作，默认off，代表不回滚。innodb_lock_wait_timeout是动态的，可以在MySQL数据库运行时进行调整，而innodb_rollback_on_timeout是静态的，不可以在MySQL运行时调整。

mysql> show global variables like '%innodb_lock_wait_timeout%';
mysql> show global variables like 'innodb_rollback_on_timeout';

 

第三部分 死锁

如果程序是串行的，那么不可能发生死锁。死锁只发生在并发的情况下，数据库就像一个并发进行着的程序，因此可能会发生死锁。在前面的文章里，我们已经知道，INNODB存储引擎有一个后台的锁监控线程，该线程负责查看可能的死锁问题，并自动告知用户。如下图：

 

通过上图我们捕获到了1213这个错误，也就是发生了死锁。为什么发生死锁，相比大家都很清楚，是因为会话A和会话B的资源在互相等待。但是在1213异常抛出后，会话A为什么会立刻获取记录为2的结果呢？难道是因为会话B回滚了？可是前一章节刚刚讲了锁超时会话不回滚的啊……

其实，大家猜的没错，会话B 的确回滚了，锁超时会话不回滚也是对的，但是，死锁例外。

也就是说，当抛出1213异常后，我们不需要人为的对事务进行回滚。在Oracle中，产生死锁的原因大多是因为没有对外键建立索引，而在innodb存储引擎会自动为其添加，如果人为删除外键上的索引就会抛出异常。