关于MySQL锁的两个知识点
- 2019 年 11 月 6 日
- 笔记
MySQL快照读和当前读
在MySQL中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
这里我们首先给出快照读和当前读的例子：
快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)
select * from table where id>10;
当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
select * from table where id>10 lock in share mode; select * from table where id>10 for update; insert into table values (…); update table set id=11 where id=10; delete from table where id>10;
读取之后，需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句明确指出了lock in share mode之外，也就是对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。
这里我们给出一个update操作过程中，mysql server和innodb存储引擎进行交互的过程如下：
从上图中，我们可以看出一个update操作的具体流程。当update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。待MySQL Server收到这条加锁的记录之后，会再发起一个update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，update操作内部，就包含了一个当前读。同理，delete操作也一样。insert操作会稍微有些不同，简单来说，就是insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。
2
关于死锁
死锁是指两个或者两个以上的事务在执行的过程中，因争夺资源而造成的一种互相等待的现象。若无外力作用，这两个事务将保持等待状态，无法推进下去。很明显，这是我们不想看到的。
从上面的概念可以看出，死锁的关键点在于互相等待，如果我们要解决死锁的问题，就要从“等待”这个关键词上面入手，如果我们将等待都转化为回滚操作，并且事务都重新开始，这种方法无疑可以避免死锁问题的产生。但是会导致数据库并发性能的降低，这样的问题也是我们无法接受的。
为了解决这一问题，我们采用一种超时的方法进行折中进行处理，超时是指当两个事务互相等待时，当某一方的等待时间超过一个阈值，我们将它进行回滚，这样，另一个事务就能够继续进行，在innodb存储引擎中，我们使用参数innodb_lock_wait_timeout来设置超时时间，这个参数如下：
mysql> show variables like "innodb_lock_wait_timeout"; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+ 1 row in set, 1 warning (0.11 sec)
为了加深印象，我们模拟一个死锁的现象，让大家感受一下。
首先，要模拟死锁，程序必须并发运行，串行的方法是无法模拟死锁的，这里我们采用两个连接会话进行模拟：
会话A
我们先开启事务，然后锁定id=3的行；
mysql> select * from t; +----+-----+ | id | age | +----+-----+ | 1 | 5 | | 2 | 4 | | 3 | 3 | | 4 | 2 | | 5 | 1 | +----+-----+ 5 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where id=3 for update; +----+-----+ | id | age | +----+-----+ | 3 | 3 | +----+-----+ 1 row in set (0.02 sec)
会话B
在会话B上锁定id=2的行
mysql> begin -> ; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where id=2 for update; +----+-----+ | id | age | +----+-----+ | 2 | 4 | +----+-----+ 1 row in set (0.00 sec)
会话A
我们在会话A上获取id=2的记录的锁，发现无法获取，产生了等待：
mysql> select * from t where id=2 for update; ##产生等待 mysql>
会话B
在会话A进行等待的过程中，我们在会话B上面获取id=3的记录的锁，我们发现了两个变化：
第一、会话B上输出了死锁的提示信息，如下；
mysql> select * from t where id=3 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting traction mysql>
第二、会话A上输出了id=2的记录，也就是A会话得到了特定的资源，但是产生了9s的延迟，如下；
会话A
mysql> select * from t where id=2 for update; +----+-----+ | id | age | +----+-----+ | 2 | 4 | +----+-----+ 1 row in set (9.04 sec) mysql>
在上述操作中，会话B抛出了1213这个错误状态码，它代表事务发生了死锁，死锁的原因是会话A和B的资源进行了相互等待，但是此时我们发现会话B中抛出死锁提示信息之后会话A中立即得到了记录为2的这个资源，这其实是因为会话B中的事务发生了回滚，否则的话，会话A中的事务是不可能得到相应的资源的。
这里又不得不提innodb的一个特性，那就是它会回滚死锁情况下的一个事务，因此当我们在程序中捕获了一个1213的错误，其实不需要我们手动进行回滚。
