深入理解MySQL（5）：详谈MySQL锁算法

七、MySQL的锁

首先区分一下“锁”的概念，在数据库中lock和latch都称为“锁”，其中latch称为闩锁（轻量级锁），锁定的时间短，在InonoDB中又可分为mutex（互斥量）和rwlock（读写锁），目的是保证并发线程操作临界资源的正确性，保护的对象是线程，保护的是内存数据结构，没有死锁检测和处理机制。可以通过命令show engine innodb mutex来进行查看详细信息。

而lock的对象是事务，锁定的是数据库中的对象，比如表、页、行等。lock对象一般在事务commit和rollback后进行释放，有死锁检测和处理机制。可以通过命令show engine innodb status 及information_schema下的表innodb_trx、innodb_locks（等待获取的锁）、innodb_lock_waits来观察锁的信息。

7.1锁的分类

这里的全局锁（flush table with read lock）等主要应用在不支持事务的存储引擎中的锁。

7.1.1表级锁与行级锁

MySQL 中的锁可以按照粒度分为锁定整个表的表级锁（table-level locking）和锁定数据行的行级锁（row-level locking）：

• 表级锁具有开销小、加锁快的特性；表级锁的锁定粒度较大，发生锁冲突的概率高，支持的并发度低；
• 行级锁具有开销大，加锁慢的特性；行级锁的锁定粒度较小，发生锁冲突的概率低，支持的并发度高。

InnoDB 存储引擎同时支持行级锁（row-level locking）和表级锁（table-level locking），默认情况下采用行级锁。
在 MySQL 中，行锁由各个存储引擎自行实现，并不由 MySQL Server 实现。因此，MyISAM 就不支持行锁，而 InnoDB 则支持行锁。

表级锁适用于并发较低、以查询为主的应用，例如中小型的网站；MyISAM 和 MEMORY 存储引擎采用表级锁。
行级锁适用于按索引条件高并发更新少量不同数据，同时又有并发查询的应用，例如 OLTP 系统；InnoDB 和 NDB 存储引擎实现了行级锁。

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
表锁的语法是 lock tables … read/write，与FTWRL（flush table with read lock）类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
另一类表级的锁是MDL（metadata lock)。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
因此，在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
• MDL 会直到事务提交才会释放

7.1.2共享锁与排它锁

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：允许获得该锁的事务读取数据行（读锁），同时允许其他事务获得该数据行上的共享锁，并且阻止其他事务获得数据行上的排他锁。
• 排他锁（X）：允许获得该锁的事务更新或删除数据行（写锁），同时阻止其他事务取得该数据行上的共享锁和排他锁。

如果一个事务已经获得了行r的共享锁，那么另外的事务可以立刻获得行r的共享锁，这种情况称为锁兼容（Lock Compatible）。共享锁和排它锁的兼容性如下：

锁类型	共享锁 S	排他锁 X
共享锁 S	兼容	冲突
排他锁 X	冲突	冲突

select ... for share 可用来获取行的共享锁
select ... for update 、update、delete 可用来获取行的排它锁

7.1.3意向锁

InnoDB 除了支持行级锁，还支持由 MySQL 服务层实现的表级锁（LOCK TABLES ... WRITE在指定的表加上表级排他锁）。当这两种锁同时存在时，可能导致冲突。例如，事务 A 获取了表中一行数据的读锁；然后事务 B 申请该表的写锁（例如修改表的结构）。如果事务 B 加锁成功，那么它就应该能修改表中的任意数据行，但是 A 持有的行锁不允许修改锁定的数据行。显然数据库需要避免这种问题，B 的加锁申请需要等待 A 释放行锁。

那么如何判断事务 B 是否应该获取表级锁呢？首先需要看该表是否已经被其他事务加上了表级锁，然后依次查看该表中的每一行是否已经被其他事务加上了行级锁。这种方式需要遍历整个表中的记录，效率很低。为此，InnoDB 引入了另外一种锁：意向锁（Intention Lock）。

意向锁属于表级锁，由 InnoDB 自动添加，不需要用户干预。意向锁也分为共享和排他两种方式：

• 意向共享锁（IS）：事务在给数据行加行级共享锁之前，必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务在给数据行加行级排他锁之前，必须先取得该表的 IX 锁。

此时，事务 A 必须先申请该表的意向共享锁，成功后再申请数据行的行锁。事务 B 申请表锁时，数据库查看该表是否已经被其他事务加上了表级锁；如果发现该表上存在意向共享锁，说明表中某些数据行上存在共享锁，事务 B 申请的写锁会被阻塞。

因此，意向锁是为了使得行锁和表锁能够共存，从而实现多粒度的锁机制。以下是表级\行级锁和表级意向锁的兼容性：

锁类型	共享锁 S	排他锁 X	意向共享锁 IS	意向排他锁 IX
共享锁 S	兼容	冲突	兼容	冲突
排他锁 X	冲突	冲突	冲突	冲突
意向共享锁 IS	兼容	冲突	兼容	兼容
意向排他锁 IX	冲突	冲突	兼容	兼容

意向锁和表锁之间只有共享锁兼容，意向锁和意向锁之间都可以兼容。意向锁的主要作用是表明某个事务正在或者即将锁定表中的数据行。

7.1.4插入意向锁

插入意向锁（Insert Intention Lock）是在插入数据行之前，由 INSERT 操作设置的一种间隙锁。插入意向锁表示一种插入的意图，如果插入到相同间隙中的多个事务没有插入相同位置，则不需要互相等待。假设存在值为 4 和 7 的索引记录。 事务A、B分别尝试插入值 5 和 6 ，在获得插入行的排他锁之前，事务A、B都使用插入意向锁锁定了 4 和 7 之间的间隙，但不会相互阻塞，因为插入的行是不冲突的（注意这里指的是事务AB都没有先去用select... for update去获取一般意义的间隙锁，不然插入时都会阻塞，同时插入还会发生死锁）。 插入意向锁之间不互斥，插入意向锁和间隙锁之间互斥。

以下示例演示了在获取插入记录的排他锁之前采用插入意向锁的事务。该示例涉及两个客户端 A 和 B。
客户端 A 创建一个包含两条索引记录（90 和 102）的表，然后启动一个事务，对 ID 大于 100 的索引记录放置排他锁。 排他锁包括记录 102 之前的间隙锁：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

客户端 B 开始一个事务以在间隙中插入一条记录。事务在等待获得排他锁时使用插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);
blocking.....

使用 SHOW ENGINE INNODB STATUS后监视器输出：事务B等待获取插入意向锁，此时如果A执行插入就会产生死锁，B在等待A释放间隙锁，A在等待B释放意图锁。

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

7.1.5行锁的算法

InnoDB 通过给索引上的索引记录加锁的方式实现行级锁。具体来说，InnoDB 实现了三种行锁的算法：记录锁（Record Lock）、间隙锁（Gap Lock）和 Next-key 锁（Next-key Lock）。

记录锁
记录锁（Record Lock）是针对索引记录（index record）的锁定。例如，SELECT * FROM t WHERE id = 1 FOR UPDATE;会阻止其他事务对表 t 中 id = 1 的数据执行插入、更新，以及删除操作。如果InnoDB表没有建立索引和主键，那么会用隐式的主键列生成的索引来进行锁定

间隙锁
间隙锁（Gap Lock）锁定的是索引记录之间的间隙，不包含索引记录本身。例如，SELECT * FROM t WHERE c1 BETWEEN 1 and 10 FOR UPDATE;会阻止其他事务将 1 到 10 之间的任何值插入到 c1 字段中，即使该列不存在这样的数据；因为这些值都会被锁定。

间隙锁的锁定范围：根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。RR级别下开启，RC级别下除外键约束和唯一性检查外不生效。

limit可以缩小间隙锁的范围，扫描到指定数目后就会停止加锁

Next-key 锁
Next-key 锁（Next-key Lock）：Record Lock+Gap Lock，相当于一个索引记录锁加上该记录之前的一个间隙锁，可用来解决当前读的幻读问题。RR级别下开启，RC级别下除外键约束和唯一性检查外不生效，等于退化成record lock。

InnoDB 实现行级锁的方式如下：当搜索或扫描表索引时，在遇到的索引记录上设置共享锁或排它锁。因此，InnoDB 行级锁实际上是索引记录锁。一个索引记录上的 next-key 锁也会影响该索引记录之前的“间隙”，如果一个会话在索引中的记录 R 上有共享锁或排它锁，则另一个会话不能在 R 之前的间隙中插入新的索引记录。

假设一个索引中包含数据 10、11、13 和 20。该索引中可能的 next-key 锁包含以下范围，其中圆括号表示排除端点值，方括号表示包含端点值：

-- 左开右闭
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity) -- 显示为 supermum

next key lock在各种条件下生成的锁范围：

测试表准备：

id(主键)	name	age(普通索引)
1	name1	15
5	lucy	18
11	南风	22
20	洛神赋	28

对于锁的释放时机，在不同的隔离级别中也并不相同，比如在“读未提交”级别中，是SQL执行完成后就立马释放锁，而在“可重复读”级别中，是在事务结束后才会释放。

7.1.5.1普通索引

首先明确一点，任何二级索引加锁时，会先对对应的聚簇索引加锁。为了便于说明，下面2个小结提到的检索都是指当前读。

• 检索单个值（存在值）

##############session 1

SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

START TRANSACTION;
SELECT * FROM USER WHERE age=22 FOR UPDATE 

############session 2

SET autocommit=0;
SET session transaction isolation level REPEATABLE read;

START TRANSACTION;
INSERT INTO user VALUE(3, 'gap lock', 18) #成功
INSERT INTO user VALUE(6, 'gap lock', 18) #阻塞
INSERT INTO user VALUE(3, 'gap lock', 20) #阻塞
INSERT INTO user VALUE(10, 'gap lock', 22) #阻塞
INSERT INTO user VALUE(14, 'gap lock', 28) #阻塞
INSERT INTO user VALUE(21, 'gap lock', 28) #成功

锁定区域示意图：

锁定的范围是：（18，22）（22，28）gap锁，22的record锁，主键11的record 锁。
插入时，当普通索引相同时，按主键排序，如果最终范围在（5,18）-（20,28)之间的都会被阻塞。
更新时，18和28行都会被阻塞。

• 检索单个值（不存在）

  同上，退化为gap lock，会锁定相邻的两个索引区间，如果左右哪一端没有数据，则范围到∞。

• 范围检索（a<=age<=b）

  • <=或>=可视作"> "and "="依次分析
  • 若a或b存在于表中，则以其作为边界
  • 否则，往增小/大的方向，找到第一条记录，以此为边界
  • in是多个单值范围结果并集，between等同

7.5.1.2唯一索引

主要区别是对唯一索引进行单一检索时，Next-key 锁会降级为Record Lock。
以上面的例子为例，如果age是唯一索引，那么只会给age=22的二级索引和对应的主键加record lock。
如果唯一索引是个组合索引，那么索引查询只查其中一个的话依然是范围查询，会有gap lock。

本小结主要参考自：MySQL 中的锁机制、MySQL间隙锁、next-key和行锁的时机。
由于我自己测试结果与这些文章中在边界上的结论有些差别，所以就没有过多提及边界锁定问题，有兴趣可自行测试update和insert下的各种情况。

7.5.1.3无索引或索引失效

会升级为表锁。

7.2死锁

在MySQL中只有事务commit或者rollback才会释放锁。

死锁产生：

• 死锁是指两个或多个事务在同一资源上相互占用且不释放，并请求锁定对方占用的资源，从而导致恶性循环
• 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
• 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
• 死锁的可能性不受隔离级别的影响，因为隔离级别改变了读操作的行为，而死锁的发生是因为写操作。（这是官方文档的说法：Deadlocks in InnoDB，但我觉得不太准确，因为gap lock在RR级别才开启，而且官方文档在处理死锁的建议中也提到了使用当前读时可以降低隔离级别来减少死锁概率：How to Minimize and Handle Deadlocks）

死锁产生举例：
数据准备：

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
mysql> INSERT INTO t (i) VALUES(1);

客户端 A开启一个事务，用共享锁锁定一行数据

mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
| 1    |
+------+

客户端 B 开始一个事务并尝试从表中删除该行:

mysql> START TRANSACTION;
mysql> DELETE FROM t WHERE i = 1;

客户端B获取表的意向排他锁（IX）后因与行上的共享锁（S）不兼容无法获取行的排它锁（X），因此，进入锁的请求队列处于block状态。

此时A尝试删除改行

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

因为事务B在等待获取行排它锁（X）队列前面且在等待事务A释放行的共享锁（S），所以此时事务A不能将行的共享锁（S）升级为行排它锁（X），形成了死锁。
执行 SHOW ENGINE INNODB STATUS查看死锁详情，具体的每行的含义可参考SHOW ENGINE INNODB STATUS的解读

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-08-22 06:19:54 0x7f23c4d65700
*** (1) TRANSACTION:
TRANSACTION 2875, ACTIVE 5 sec starting index read  //事务2875 活跃了5秒
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 418, OS thread handle 139791603504896, query id 2986 124.160.26.173 root updating
DELETE FROM t WHERE i = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED：//等待获取行锁  位置在space 44 page 3  类型是排它锁
RECORD LOCKS space id 44 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 2875 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000000b38; asc      8;;
 2: len 7; hex ad000001210110; asc     !  ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2876, ACTIVE 798 sec starting index read //事务2876 活跃了798秒
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 421, OS thread handle 139791602964224, query id 2990 124.160.26.173 root updating
DELETE FROM t WHERE i = 1
*** (2) HOLDS THE LOCK(S)://拥有   位置在space 44 page 3 的共享锁
RECORD LOCKS space id 44 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 2876 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000000b38; asc      8;;
 2: len 7; hex ad000001210110; asc     !  ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED://等待获取行锁  位置在space 44 page 3  类型是排它锁
RECORD LOCKS space id 44 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 2876 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000000b38; asc      8;;
 2: len 7; hex ad000001210110; asc     !  ;;
 3: len 4; hex 80000001; asc     ;;

//回滚事务1
*** WE ROLL BACK TRANSACTION (1)

死锁处理方式：

• 可以通过 innodb_lock_wait_timeout 这个参数来设置锁的等待时间，默认为 50s，等待超时自动解锁。锁等待时间不能设置的太小，因为有时候长事务执行时后续更新也需要等待，而这时候并没有死锁发生，因此需要根据业务设置合理超时时间。
• 开启死锁检测，通过 innodb_deadlock_detect = on 进行设置，那么当 InnoDB 发现有死锁发生时，直接回滚其中一个事务（持有最少行级排他锁的事务）。因为超时时间很难设置，所以实际使用时基本使用这种方法。

检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。死锁简单的来说就是循坏依赖，依赖和依赖之间所组成的有向图存在环。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。极端情况下，如果所有事务去更新同一行数据，那么死锁检测的时间复杂度是O(n2)

因此，开启死锁检测是有代价的，有些时候禁用死锁检测可能会更有效，这时只能依赖innodb_lock_wait_timeout设置进行事务回滚。

死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。

MyISAM避免死锁：

• 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

InnoDB避免死锁：

• 保持事务小且持续时间短，以减少它们发生冲突的可能性。
• 在进行一组相关更改后立即提交事务，以减少它们发生冲突的可能性。特别是，不要让交互式会话长时间处于打开状态并带有未提交的事务。
• 更少地使用锁。如果您可以允许从旧快照返回数据，请不要向其添加FOR UPDATEor LOCK IN SHARE MODE子句。
• 如果使用一致性锁定读（SELECT … FOR UPDATE或 SELECT … LOCK IN SHARE MODE），可以使用较低的隔离级别，例如READ COMMITTED。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，比如使用存储过程，这样可以大大降低产生死锁的机会
• 使用合适的索引减少锁定的范围，结合explain来确定哪些索引是更为合适的。可参考：死锁案例分析

如果出现死锁，可以用 show engine innodb status;命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

如果频繁的死锁突出了事务结构或应用程序错误处理的问题，则可启用 innodb_print_all_deadlocks将所有死锁的信息打印到 mysqld错误日志。

注：内容是从语雀上的学习笔记迁移过来的，有些参考来源已经无法追溯，侵权私删。