谈谈 MySQL 锁

一、前言

MySQL 的并发控制是在数据安全性和并发处理能力之间的权衡，通过不同的锁策略来决定对系统开销和性能的影响。

只要存在多个客户端同时修改更新数据，就会存在并发问题，MySQL 通过 MVCC 和锁来处理这一问题。

二、锁的粒度 Lock Type

MySQL 源码中定义了两种锁的粒度，分别是表锁和行锁。

/** Lock types */
#define LOCK_TABLE 16  /*!< table lock */
#define LOCK_REC 32    /*!< record lock */

1、表锁

表锁由 MySQL Server 控制，优点是开销小、加锁快，不会产生死锁，缺点是加锁粒度大，发生锁冲突的概率大，并发度比较低。

一般 DDL 语句会自动加表锁，也可以手动指定。表锁分为读锁和写锁。

//加读锁
lock table products read;
//加写锁
lock table products write;

当对表加了读锁，则会话只能读取当前被加锁的表，其它会话仍然可以对表进行读取但不能写入。当对表加了写锁，则会话可以读取或写入被加锁的表，其它会话不能对加锁的表进行读取或写入。

2、行锁

行锁由存储引擎实现，InnoDB 支持，而 MyISAM 不支持。行锁的优点是锁粒度小，发生锁冲突概率小，并发度高，缺点是开销大、加锁慢，并且可能产生死锁。

InnoDB 行锁是通过索引项加锁来实现的，只有通过索引条件检索数据，才能锁住指定的索引记录，否则将使用行锁锁住全部数据（有文章称会退化为表锁，是错误的理解）。

表级锁适合查询多、更新少的场景，行级锁适合按索引更新频率高的场景。InnoDB 默认使用行级锁。

三、锁的模式 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 */
 ...
};

1、共享锁和排它锁

共享锁和排它锁都是行级锁。

Shared Lock (S 锁)，共享锁，也称为读锁。当事务对行加共享锁后，允许其它事务对相同行加共享锁，但不允许加排它锁。

Exclusive Lock (X 锁)，排它锁，也称为写锁。当事务对行加排它锁后，不允许其它事务对相同行加共享锁或排它锁。

2、意向锁

意向锁分为意向共享锁和意向排它锁，意向锁是表锁。

Intention Shared Lock (IS)，意向共享锁，也称为意向读锁。意向共享锁表示有事务打算在行记录上加共享锁，在事务获取行 S 锁前，必须先获得 IS 锁或更高级别的锁。

Intention Exclusive Lock (IX)，意向排它锁，也称为意向写锁。意向排它锁表示有事务打算在行记录上加排它锁，在事务获取行 X 锁前，必须先获 IX 锁。

意向锁之间不会发生冲突，但共享锁、排它锁、意向锁之间会发生冲突，如下表所示。

     |      X       |       IX       |       S        |       IS    
X    |   Conflict   |    Conflict    |    Conflict    |    Conflict 
IX   |   Conflict   |   Compatible   |    Conflict    |   Compatible
S    |   Conflict   |    Conflict    |   Compatible   |   Compatible
IS   |   Conflict   |   Compatible   |   Compatible   |   Compatible

3、自增锁

AUTO-INC Locks，自增锁，它是一种特殊的表锁。当表有设置自增 auto_increment 列，在插入数据时会先获取自增锁，其它事务将会被阻塞插入操作，自增列 +1 后释放锁，如果事务回滚，自增值也不会回退，所以自增列并不一定是连续自增的。

四、行锁的分类

MySQL 中定义了四种行锁的分类：

#define LOCK_ORDINARY     0 
#define LOCK_GAP         512 
#define LOCK_REC_NOT_GAP   1024 
#define LOCK_INSERT_INTENTION  2048 

1、记录锁

Record Locks，记录锁是索引记录的锁定。例如 SELECT a FROM t WHERE a = 15 FOR UPDATE，对索引记录 15 进行锁定，防止其它事务插入、删除、更新值为 15 的记录行。

记录锁是通过索引加锁，如果列没有设置索引，则将使用聚簇索引，如果没有人为指定聚簇索引，MySQL 会自动建立一个聚簇索引。

2、间隙锁

Gap Locks，间隙锁是对索引记录之间的间隙的锁定。对于键值在条件范围内但并不存在的记录，叫做 间隙（gap）。例如 SELECT a FROM t WHERE a > 15 and a < 20 FOR UPDATE，且 a 存在的值为 1、2、5、10、15、20，则将 15,20 中的间隙锁住。

间隙锁和间隙锁之间是互不冲突的，间隙锁唯一的作用就是为了防止其他事务的插入，在 RR（可重复读）级别下解决了幻读的问题。

3、临键锁

Next-Key Lock，临键锁，是记录锁和间隙锁的合集。例如 SELECT a FROM t WHERE a > 15 FOR UPDATE，且 a 存在的值为 1、2、5、10、15、20，则将 (15,20]、(20, +∞] 的中 15、20 及其间隙锁住。

4、插入意向锁

Insert Intention Locks，插入意向锁，是一种特殊的间隙锁，只有在执行 INSERT 操作时才会加锁，插入意向锁之间不冲突，可以向一个间隙中同时插入多行数据，但插入意向锁与间隙锁是冲突的，当有间隙锁存在时，插入语句将被阻塞，正是这个特性解决了幻读的问题。 

五、何时加锁

• SELECT xxx 查询语句正常情况下为快照读，不加锁；

• SELECT xxx LOCK IN SHARE MODE 语句为当前读，加 S 锁；

• SELECT xxx FOR UPDATE 语句为当前读，加 X 锁；

• DML 语句（INSERT、DELETE、UPDATE）为当前读，加 X 锁；

• DDL 语句（AL

• TER、CREATE 等）加表级锁，且是隐式提交不能回滚；

当前读和快照读是什么东西，这又涉及到 MVCC（Mutil Version Concurrent Control） 多版本并发控制的概念。

在不同的事务隔离级别下，会有不同的锁机制，也可以说是通过不同的锁机制实现了不同的事务隔离级别。在 RC（读已提交）级别下，只会有记录锁，不存在间隙锁和 Next-Key 锁，RR（可重复读）级别下才会有间隙锁及 Next-Key 锁。

仅通过锁来控制实现事务隔离级别会存在一些问题，比如要实现 RC（读已提交）级别，事务 a 更新一行数据，需要对行（实际是索引记录）加 X 锁，阻塞其它事务对该行的读写，事务 b 想要读取该行必须等到 a 提交或回滚释放锁，这样的话就会很大程度上限制读写的并发能力。

MVCC 的原理是通过在每行记录上加了隐藏的三列（隐式的 ID 字段、事务 ID、回滚指针），事务在写一条记录时会将其拷贝一份生成这条记录的一个原始拷贝，写操作是会对原记录加锁，但是读操作会读取未加锁的拷贝快照记录，这就保证了读写并行。

在 RC 和 RR 级别下，才会使用 MVCC 机制，RC 级别下事务总是读取最新的快照版本，RR 级别下事务总是读取事务开启时的快照版本，这称为快照读。当前读是指读取数据的最新版本，而非快照，也称为加锁读。

六、加锁分析

选取最常用的几个查询语句，来分析加锁的过程，由于 RC RR 级别加锁区别大致是 RC 没有间隙锁、Next-Key 锁，所以以 innoDB 默认的 RR 级别来实验，RC 的加锁分析不赘述。

mysql> show create table test_lock;
CREATE TABLE `test_lock` (
  `id` int(11) NOT NULL,
  `card` varchar(32) NOT NULL,
  `name` varchar(32) NOT NULL,
  `money` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_card` (`card`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> select * from test_lock;
+----+------+------+-------+
| id | card | name | money |
+----+------+------+-------+
|  1 | N100 | a    |  1000 |
|  5 | N109 | e    |  2000 |
|  8 | N203 | h    |  3000 |
| 12 | N300 | l    |  4000 |
| 15 | N400 | p    |  5000 |
| 20 | N500 | v    |  6000 |
+----+------+------+-------+
6 rows in set (0.01 sec)

聚簇索引，查询命中：UPDATE test_lock SET money = 1500 WHERE id = 1;

聚簇索引，查询未命中：UPDATE test_lock SET money = 2500 WHERE id = 10;

二级唯一索引，查询命中：UPDATE test_lock SET money = 3500 WHERE card = 'N203';

二级唯一索引，查询未命中：UPDATE test_lock SET money = 4500 WHERE card = 'N299';

二级非唯一索引，查询命中：UPDATE test_lock SET money = 1001 WHERE name = 'h';

二级非唯一索引，查询未命中：UPDATE test_lock SET money = 1001 WHERE name = 't';

无索引：UPDATE test_lock SET money = 7000 WHERE money = 6000;

聚簇索引，范围查询：UPDATE test_lock SET money = 1100 WHERE id <= 15;

二级索引，范围查询：UPDATE test_lock SET money = 1100 WHERE card <= 'N300';

1、聚簇索引，查询命中

// 事务 1 执行
UPDATE test_lock SET money = 1500 WHERE id = 1;
// 事务 2 执行，被阻塞
UPDATE test_lock SET money = 1800 WHERE id = 1;

事务 A 更新 id = 1 的数据，事务 2 也更新此行时被阻塞等待。

查看锁情况， 1335 被阻塞等待 1333 的事务，加锁类型是 Record，加锁索引是主键索引，加锁数据是 1。 

2、聚簇索引，查询未命中

// 事务 1 执行
UPDATE test_lock SET money = 2500 WHERE id = 10;
// 事务 2 执行，被阻塞
INSERT INTO test_lock values (9, 'N250','j', 5000);

在 RR 级别下，更新 id = 10 的记录，记录不存在时，加了间隙锁 (8,12)，导致 id = 9 的记录插入也被阻塞。

3、二级唯一索引，查询命中

// 事务 1 执行
UPDATE test_lock SET money = 3500 WHERE card = 'N203';
// 事务 2 执行两次语句，均被阻塞
UPDATE test_lock SET money = 3800 WHERE card = 'N203';
UPDATE test_lock SET money = 3800 WHERE id = 8;

查看锁情况，可以看到二级索引的 N203 和主键索引的 8 均被加上了 X 锁。

4、二级唯一索引，查询未命中

// 事务 1 执行
UPDATE test_lock SET money = 4500 WHERE card = 'N299';
// 事务 2 执行，被阻塞
INSERT INTO test_lock values (9, 'N250','j', 5000);

查看锁情况，和主键索引查询未命中一样，对二级索引加了间隙锁 ('N203',''N300')，导致 N250 的记录插入也被阻塞。

5、二级非唯一索引，查询命中

// 事务 1 执行
UPDATE test_lock SET money = 1001 WHERE name = 'h';
// 事务 2 执行
UPDATE test_lock SET money = 1002 WHERE name = 'h'; //被阻塞
UPDATE test_lock SET money = 1002 WHERE id = 8; //被阻塞
UPDATE test_lock SET money = 1002 WHERE name = 'l'; //可执行
INSERT INTO  test_lock values(10, 'N250','f', 5000); //被阻塞
INSERT INTO  test_lock values(9, 'N250','j', 5000); //被阻塞
INSERT INTO test_lock values(11, 'N250','m', 5000); //可执行
INSERT INTO  test_lock values(10, 'N251','b', 5000); //可执行

查看锁情况，可以看到二级索引的 h 和主键索引的 8 均被加上了 X 锁，并且 (e,h)、(h,l) 之间的间隙也被加了间隙锁，加锁示意图如下：

6、二级非唯一索引，查询未命中

// 事务 1 执行
UPDATE test_lock SET money = 1001 WHERE name = 't';
// 事务 2 执行
INSERT INTO  test_lock values(16, 'N250','r', 5000); //被阻塞
INSERT INTO  test_lock values(17, 'N250','u', 5000); //被阻塞
INSERT INTO  test_lock values(17, 'N250','w', 5000); //可执行

查看锁情况，在 (p,v)之间加了间隙锁，p 和 v 之间的数据插入将被阻塞。

7、无索引

// 事务 1 执行
UPDATE test_lock SET money = 7000 WHERE money = 6000;
// 事务 2 执行
UPDATE test_lock SET money = 1001 WHERE name = 'a'; //被阻塞
UPDATE test_lock SET money = 1001 WHERE name = 'p'; //被阻塞
UPDATE test_lock SET money = 1001 WHERE id = 1; //被阻塞
INSERT INTO  test_lock values(99, 'N999','z', 5000);  //被阻塞

从执行结果可以看出，不仅在所有主键上加了 X 锁，主键之间的间隙都加上了 Gap 锁，由此也可以证实对于不走索引的更新语句需要慎用。

8、聚簇索引，范围查询

// 事务 1 执行
UPDATE test_lock SET money = 1100 WHERE id <= 12;
// 事务 2 执行
UPDATE test_lock SET money = 1001 WHERE id = 5; //被阻塞
INSERT INTO  test_lock values(3, 'N251','b', 5000); //被阻塞
INSERT INTO  test_lock values(13, 'N251','b', 5000); //被阻塞
UPDATE test_lock SET money = 1001 WHERE id = 15; //被阻塞
INSERT INTO  test_lock values(16, 'N251','b', 5000); //可执行

从执行结果来看，所有小于 12 的主键上加了 X 锁，主键之间的间隙都加上了 Gap 锁，并且(12,15)的间隙也被加了 Gap 锁和主键 15 组成一个 Next-Key 锁。按正常思路 (12,15] 的 Next-Key 锁并不需要加，这点有点不好理解。

9、二级索引，范围查询

// 事务 1 执行
UPDATE test_lock SET money = 1100 WHERE card <= 'N203';
// 事务 2 执行
UPDATE test_lock SET money = 1001 WHERE id = 5; //被阻塞
UPDATE test_lock SET money = 1001 WHERE id = 8; //被阻塞
UPDATE test_lock SET money = 1001 WHERE id = 12; // 被阻塞
UPDATE test_lock SET money = 1001 WHERE id = 15; // 可执行
INSERT INTO  test_lock values(3, 'N999','b', 5000); //可执行
UPDATE test_lock SET money = 1001 WHERE card = 'N100'; //被阻塞
UPDATE test_lock SET money = 1001 WHERE card = 'N300'; //被阻塞
INSERT INTO  test_lock values(99, 'N999','b', 5000); //可执行

从执行结果和锁情况来看，所有小于 N203 的索引上加了 X 锁，索引之间的间隙都加上了 Gap 锁，并且 (N203,N300) 的间隙也被加了 Gap 锁和索引 N300 组成一个 Next-Key 锁。idx_card 对应的主键索引值也加了 X 锁，如下图所示。

需要额外注意的是，如果执行 UPDATE test_lock SET money = 1100 WHERE card <= 'N300'; 则会用 X 锁和 Gap 锁将全表锁上，是因为表记录少且根据优化策略走了全表扫描。

总结如下：

七、死锁是怎么产生的

有多种场景会产生死锁，下面复现一种。

// 第一步：事务 1 执行
UPDATE test_lock SET money = 1001 WHERE id = 5;
// 第一步：事务 2 执行
UPDATE test_lock SET money = 1001 WHERE id = 12;
// 第二步：事务 1 执行
UPDATE test_lock SET money = 1001 WHERE id = 12;
// 第二步：事务 2 执行
UPDATE test_lock SET money = 1001 WHERE id = 5;

按照以上语句顺序执行，第一步执行后，事务 1 对 5 持有 X 锁，事务 2 对 12 持有 X 锁。执行第二步时，事务 1 在等待事务 2 对 12 的释放，事务 2 在等待事务 1 对 5 的释放，由此产生了死锁：Deadlock found when trying to get lock; try restarting transaction。

八、总结

MySQL 的锁机制非常复杂与精妙，了解到一些浅层知识对编写高效 SQL 及解决死锁问题也会有一定的帮助，在平时操作数据库的时候要避免或减少执行全表扫描及范围锁定的 SQL，以减少锁冲突提高并发。

原文链接：https://mp.weixin.qq.com/s/T9bZCKHlFLEkH-lvAOSo7g