Mysql的锁机制

表锁和行锁

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

读锁和写锁

MySQL 将锁分成两类：锁类型（lock_type）和锁模式（lock_mode）。锁类型就是上文中介绍的表锁和行锁两种类型，当然行锁还可以细分成记录锁和间隙锁等更细的类型，锁类型描述的锁的粒度，也可以说是把锁具体加在什么地方；而锁模式描述的是到底加的是什么锁，譬如读锁或写锁。锁模式通常是和锁类型结合使用的，锁模式在 MySQL 的源码中定义如下：

/* Basic lock modes */
enum lock_mode {
    LOCK_IS = 0, /* intention shared */
    LOCK_IX,    /* intention exclusive */
    LOCK_S,     /* shared */
    LOCK_X,     /* exclusive */
    LOCK_AUTO_INC,  /* locks the auto-inc counter of a table in an exclusive mode*/
    ...
};

LOCK_IS：读意向锁；
LOCK_IX：写意向锁；
LOCK_S：读锁；
LOCK_X：写锁；
LOCK_AUTO_INC：自增锁；

意向表锁

表锁锁定了整张表，因此表锁和行锁之间也会冲突，为了方便检测表锁和行锁的冲突引入了意向表锁。
意向锁分为意向读锁(IS)和意向写锁(IX)。
意向锁是表级锁，但表示事务试图读或写某一行记录，而不是整个表。所以意向锁之间不会产生冲突，真正的冲突在加行锁时检查。
在给一行记录加锁前，首先要给该表加意向锁。也就是要同时加表意向锁和行锁。

读锁写锁

读锁，又称共享锁（Share locks，简称 S 锁），加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。写锁，又称排他锁（Exclusive locks，简称 X 锁），或独占锁，对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。（注意：这里说的读都是当前读，快照读是无需加锁的，记录上无论有没有锁，都可以快照读）

自增锁

为一个AUTO_INCREMENT列生成自增值前，必须先为该表加 AUTO_INC 表锁。AUTO_INC 表锁有些特别的地方：

每个表最多只能有一个自增锁
为了提高并发插入的性能，自增锁不遵循二阶段锁协议，加锁释放锁不跟事务而跟语句走，insert开始时获取，结束时释放
自增值只要分配了就会+1，不管事务是否提交了都不会撤销，所以可能出现空洞。

从5.1.22开始，MySQL 提供了一种可选的轻量级锁(mutex)机制代替AUTO_INC表锁，参数 innodb_autoinc_lock_mode 控制分配自增值时的并发策略。介绍该参数之前先引入几个insert相关的概念：

Simple inserts：通过分析insert语句可以确定插入数量的insert语句，如INSERT, INSERT … VALUES(1,2),VALUES(3,4)
Bulk inserts：通过分析insert语句无法知道插入数量的insert语句，INSERT … SELECT, REPLACE … SELECT, LOAD DATA
Mixed-mode inserts：不确定是否需要分配auto_increment id，一般是下面两种情况

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')
有些指定了id，有些没
INSERT … ON DUPLICATE KEY UPDATE

参数innodb_autoinc_lock_mode可以取下列值：

innodb_autoinc_lock_mode=0 （traditional lock mode）
使用传统的 AUTO_INC 表锁，并发性比较差；
innodb_autoinc_lock_mode=1 （consecutive/连续 lock mode）默认值
折中方式，bulk 不能确定插入数用表锁，simple、mix用mutex，只锁住预分配自增ID的过程，不锁整张表。Mixed-mode inserts 会直接分析语句，获得最坏情况下需要插入的数量，一次性分配足够的auto_increment id，缺点是会分配过多的id，导致“浪费”和空洞。
这种模式既平衡了并发性，又能保证同一条insert语句分配的自增id是连续的。
innodb_autoinc_lock_mode=2 （interleaved/交叉 lock mode）
全部都用mutex，并发性能最高，id一个一个分配，不会预分配。缺点是不能保证同一条insert语句内的id是连续的，但是在replication中，当binlog_format为statement-based时（基于语句的复制）存在问题，因为是来一个分配一个，同一条insert语句内获得的自增id可能不连续，主从数据集会出现数据不一致。

行锁的分类

行锁从mode上分为X、S，type上进一步细分为以下类型：

LOCK_GAP：GAP锁，锁两个记录之间的GAP，防止记录插入；
LOCK_ORDINARY：官方文档中称为 “Next-Key Lock” ，锁一条记录及其之前的间隙，这是RR级别用的最多的锁，从名字也能看出来；
LOCK_REC_NOT_GAP：只锁记录；
LOCK_INSERT_INTENSION：插入意向GAP锁，插入记录时使用，是LOCK_GAP的一种特例。

记录锁（Record Locks）

记录锁是最简单的行锁，并没有什么好说的。譬如下面的 SQL 语句（id 为主键）：

mysql> UPDATE accounts SET level = 100 WHERE id = 5;

这条 SQL 语句就会在 id = 5 这条记录上加上记录锁，防止其他事务对 id = 5 这条记录进行修改或删除。记录锁永远都是加在索引上的，就算一个表没有建索引，数据库也会隐式的创建一个索引。如果 WHERE 条件中指定的列是个二级索引，那么记录锁不仅会加在这个二级索引上，还会加在这个二级索引所对应的聚簇索引上。

注意，如果 SQL 语句无法使用索引时会走主索引实现全表扫描，这个时候 MySQL 会给整张表的所有数据行加记录锁。如果一个 WHERE 条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由 MySQL Server 层进行过滤。不过在实际使用过程中，MySQL 做了一些改进，在 MySQL Server 层进行过滤的时候，如果发现不满足，会调用 unlock_row 方法，把不满足条件的记录释放锁（显然这违背了二段锁协议）。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。可见在没有索引时，不仅会消耗大量的锁资源，增加数据库的开销，而且极大的降低了数据库的并发性能，所以说，更新操作一定要记得走索引。

间隙锁（Gap Locks）

还是看上面的那个例子，如果 id = 5 这条记录不存在，这个 SQL 语句还会加锁吗？答案是可能有，这取决于数据库的隔离级别。

还记得我们在上一篇博客中介绍的数据库并发过程中可能存在的问题吗？其中有一个问题叫做幻读，指的是在同一个事务中同一条 SQL 语句连续两次读取出来的结果集不一样。在 read committed 隔离级别很明显存在幻读问题，在 repeatable read 级别下，标准的 SQL 规范中也是存在幻读问题的，但是在 MySQL 的实现中，使用了间隙锁的技术避免了幻读。

间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。有时候又称为范围锁（Range Locks），这个范围可以跨一个索引记录，多个索引记录，甚至是空的。使用间隙锁可以防止其他事务在这个范围内插入或修改记录，保证两次读取这个范围内的记录不会变，从而不会出现幻读现象。很显然，间隙锁会增加数据库的开销，虽然解决了幻读问题，但是数据库的并发性一样受到了影响，所以在选择数据库的隔离级别时，要注意权衡性能和并发性，根据实际情况考虑是否需要使用间隙锁，大多数情况下使用 read committed 隔离级别就足够了，对很多应用程序来说，幻读也不是什么大问题。

回到这个例子，这个 SQL 语句在 RC 隔离级别不会加任何锁，在 RR 隔离级别会在 id = 5 前后两个索引之间加上间隙锁。

值得注意的是，间隙锁和间隙锁之间是互不冲突的，间隙锁唯一的作用就是为了防止其他事务的插入，所以加间隙 S 锁和加间隙 X 锁没有任何区别。

Next-Key Locks

Next-key 锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含
10、11、13 和 20 这几个值，可能的 Next-key 锁如下：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。继续拿上面的 SQL 例子来说，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下会加什么锁呢？答案就是 Next-key 锁，如下：

(a, 5]
(5, b)

其中，a 和 b 是 id = 5 前后两个索引，我们假设 a = 1、b = 10，那么此时如果插入一条 id = 3 的记录将会阻塞住。之所以要把 id = 5 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 5 可能会有多条记录，为了防止再插入一条 id = 5 的记录，必须将下面标记 ^ 的位置都锁住，因为这些位置都可能再插入一条 id = 5 的记录：

1 ^ 5 ^ 5 ^ 5 ^ 10 11 13 15

可以看出来，Next-key 锁确实可以避免幻读，但是带来的副作用是连插入 id = 3 这样的记录也被阻塞了，这根本就不会引起幻读问题的。
关于 Next-key 锁，有一个比较有意思的问题，比如下面这个 orders 表（id 为主键，order_id 为二级非唯一索引）：

+-----+----------+
|  id | order_id |
+-----+----------+
|   1 |        1 |
|   3 |        2 |
|   5 |        5 |
|   7 |        5 |
|  10 |        9 |
+-----+----------+

事务 A 执行下面的 SQL：

mysql> begin;
mysql> select * from orders where order_id = 5 for update;
+-----+----------+
|  id | order_id |
+-----+----------+
|   5 |        5 |
|   7 |        5 |
+-----+----------+
2 rows in set (0.00 sec)

这个时候不仅 order_id = 5 这条记录会加上 X 记录锁，而且这条记录前后的间隙也会加上锁，加锁位置如下：

1 2 ^ 5 ^ 5 ^ 9

可以看到 (2, 9) 这个区间都被锁住了，这个时候如果插入 order_id = 4 或者 order_id = 8 这样的记录肯定会被阻塞，这没什么问题，那么现在问题来了，如果插入一条记录 order_id = 2 或者 order_id = 9 会被阻塞吗？答案是可能阻塞，也可能不阻塞，这取决于插入记录主键的值。

插入意向锁（Insert Intention Locks）

插入之前，对插入的间隙加插入意向GAP锁；
插入意向GAP锁表明将向某个间隙插入记录，如果该间隙已被加上了GAP Lock或Next-Key Lock，则加锁失败。
不同事务加的插入意向GAP锁互相兼容，否则就无法并发insert了。

插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，那么间隙锁是如何防止幻读的呢？正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

行锁的兼容矩阵

image.png

间隙锁不和其他锁（不包括插入意向锁）冲突；
记录锁和记录锁冲突，Next-key 锁和 Next-key 锁冲突，记录锁和 Next-key 锁冲突；

参考链接：https://www.aneasystone.com/archives/2017/11/solving-dead-locks-two.html