Mysql 锁机制-阿里云开发者社区

锁机制用于管理对共享资源的并发访问，实现事务的隔离级别。

Mysql 事务采用的是粒度锁：针对表（B+ 树）、页（B+ 树叶子节点）、行（B+ 树叶子节点当中某一记录行）三种粒度加锁。允许事务在行级锁和表级锁的锁同时存在。

1、锁类型

根据锁的粒度，分为全局锁、表级锁和行级锁。全局锁是针对数据库加锁，表级锁是针对表或页进行加锁；行级锁是针对表的索引加锁。

1.1、全局锁

锁数据库。

全局锁用于全库逻辑备份。这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。但是在备份期间，业务只能读数据，不能更新数据，造成业务停滞。

-- 全局锁，整个数据库处于只读状态，其他操作均阻塞
 FLUSH TABLES WITH READ LOCK
 -- 释放全局锁
 UNLOCK TABLES

备份数据库时，采用其他什么方式可以避免影响业务？

如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View。

1.2、表级锁

表锁

锁整张表。尽量避免使用表锁，因为表锁的粒度大，影响并发

LOCK TABLES 表名 READ|WRITE
 UNLOCK TABLES

元数据锁

元数据锁 (MDL) ：避免 DML 和 DDL 冲突，防止表的结构改变，维护元数据一致性。

当对数据库的表进行操作时，自动添加 MDL。当事务提交后，MDL 释放。事务执行期间 MDL 一直存在。

对一张表进行 CRUD 操作时，加的是 MDL 读锁
对一张表做结构变更操作的时候，加的是 MDL 写锁

例如：当有线程在执行 select 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。反之，当有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。

意向锁

当一个事务想要获得一张表某些行（记录）的锁，必须先获得对应表的意向锁。可以快速判断表里是否有行记录加锁，从而避免表锁逐行检查行锁。

由于 innoDB 存储引擎支持的是行级锁，因此意向锁不会阻塞除全表扫描以外的任何请求。意向锁互相兼容，与表级 S | X 锁互斥，与行级的 S | X 锁兼容（意向锁不会和行锁冲突）。

意向共享锁 IS：事务想要获取一张表某些行的 S 锁，必须先获得表的 IS 锁。
意向排他锁 IX：事务想要获取一张表某些行的 X 锁，必须先获得表的 IX 锁。

例如：事务A 获取保持表中某一行的 X 锁，此时表中有两把锁：X 锁和 IX 锁。此时，事务 B 想要获得表中某一行的 X 锁，检测到表中存在 IX 锁，得知表中某些行必然存在 X 锁，事务 B 阻塞。这样，无需检测表中的每一行数据是否存在 X 锁。

自增锁

AUTO-INC锁，实现自增约束 AUTO_INCREMENT，插入语句执行完后释放锁，并非事务结束后释放锁。

例如：在插入数据时，加自增锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把自增锁释放掉。这样，在一个事务在持有自增锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。

但是，自增锁在对大量数据进行插入操作时，阻塞其他事务的插入操作，影响性能。因此，在 Mysql 5.1.22 版本后仅对 AUTO_INCREMENT字段加上轻量级锁，当字段自增后，立即释放锁，而不需要等待整个插入语句执行完后才释放锁。

1.3、行级锁

事务提交后，锁被释放。

行级锁的类型有

记录锁，也就是仅仅把一条记录锁上；
间隙锁，锁定一个范围，但是不包含记录本身；
临键锁：记录锁 + 间隙锁的组合，锁定一个范围，并且锁定记录本身。

记录锁

Record Lock，锁住一条行记录。

S 锁：共享锁，读锁，允许其他事务读取，不允许修改
X 锁：排他锁，写锁，不允许其他事务读取和修改

	X 锁	S 锁
X 锁	×	×
S 锁	×	√

间隙锁

Gap Lock，锁定一个范围，但不包含记录本身，RR级别及以上支持，目的是为了部分解决幻读问题

间隙锁间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系。

部分解决了幻读问题，解决了快照读的幻读问题。对于当前读，仍需要手动加锁，防止其他事务在记录间插入新的记录，避免幻读问题。

插入意向锁

一种间隙锁形式的意向锁，表中INSERT操作时产生。在索引记录间的间隙上的锁，在查询索引未命中，或查询辅助非唯一索引时添加。

多事务同时写入不同数据至同一索引间隙，并不需要等待其他事务完成，不会发生锁等待。因为它只是代表想插入的意向。

例如：假设有一个记录索引包含键值 4 和 7。若两个不同的事务分别插入 5 和 6，每个事务都会获取加在 (4, 7) 之间的插入意向锁，获取在对应插入行上的排他锁，此时并不会互相锁住，因为数据行并不冲突；若两个不同事务都插入 5，同理每个事务都会产生一个加在 (4, 7) 之间的插入意向锁，意向锁并不冲突，再获取插入行的排他锁，后获取插入行排他锁的事务会被阻塞。

临键锁

Next-Key Lock，记录锁 + 间隙锁的组合，锁定一个范围，并且锁住记录本身。左开右闭，RR级别及以上支持，解决了幻读问题。

例如：一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。从而既能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

2、锁兼容

锁	Gap 持有	Insert Intention 持有	Record 持有	Next-key 持有
Gap 请求	兼容	兼容	兼容	兼容
Insert Intention 请求	冲突	兼容	兼容	冲突
Record 请求	兼容	兼容	冲突	冲突
Next-key 请求	兼容	兼容	冲突	冲突

横向：表示已经持有的锁；纵向：表示正在请求的锁。

注：

一个事务已经获取了插入意向锁，对其他事务没有任何影响。
一个事务想要获取插入意向锁，若其他事务加了 gap lock 或 next-key lock 会阻塞

3、锁与事务

3.1、查询

MVCC：undo log 实现历史版本记录
S 锁：lock in share mode
X 锁：for update
不做任何处理：RU 隔离级别

3.2、删除更新

自动添加 X 锁

3.3、插入

插入意向锁：特殊的间隙锁，同时会使用 X 锁。
自增锁：特殊表锁实现

4、锁的对象

分类讨论：向表中更新数据 UPDATE。

聚集索引，查询命中

RC 级别和 RR 级别：聚集索引 B+ 树的行加 X 锁。

聚集索引，查询未命中：

RC 级别：不加锁；
RR级别：聚集索引 B+ 树索引间隙加 gap 锁。

辅助唯一索引，查询命中：

RC 级别和 RR 级别：聚集索引 B+ 树的行加 X 锁，辅助索引 B+ 树的行加 X 锁

辅助唯一索引，查询未命中：

RC 级别：聚集索引 B+ 树的行加 X 锁；
RR 级别：聚集索引 B+ 树索引间隙加 gap 锁

辅助非唯一索引，查询命中：

RC 级别：聚集索引 B+ 树的行加 X 锁
RR级别：聚集索引 B+ 树的行和索引间隙加 next-key lock 锁 (record 锁 + gap 锁)，辅助索引 B+ 树对应的行加 X 锁。

辅助非唯一索引，查询命中

辅助非唯一索引，查询未命中：

RC 级别：不加锁；
RR级别：聚集索引 B+ 树的索引间隙加 gap 锁

无索引

RC 级别：聚集索引 B+ 树的所有行加 X 锁；
RR级别：聚集索引 B+ 树的所有行和索引间隙加 next-key lock 锁 (record 锁 + gap 锁)。

在无索引的情况下，全表查询，按扫描顺序，逐行加锁，效率最低。

无索引

聚集索引，范围查询

RC 级别：聚集索引 B+ 树的范围行加 X 锁；
RR级别：聚集索引 B+ 树的范围行和索引间隙加 next-key lock 锁（X 锁 + gap 锁）。

辅助索引，范围查询（死锁问题）

RC 级别：聚集索引 B+ 树的范围行加 X 锁，辅助索引 B+ 树的范围行加 X 锁
RR级别：聚集索引 B+ 树的范围行和索引间隙加 next-key lock 锁（record 锁 + gap 锁）。

注意：若两个事务对辅助索引 B+ 树的加锁顺序相反，会造成死锁；事务对聚集索引 B+ 树的范围查询是按序的，不会有死锁，但是对于辅助索引 B+ 树的修改却不一定有序，可能会导致死锁。

辅助索引，范围查询

修改索引值

RC 级别和RR级别：聚集索引 B+ 树和非聚集索引 B+ 树的对应行加 X 锁

5、死锁

死锁：并发事务，因竞争资源而造成的相互等待的现象。Mysql 中采用等待图 wait-for graph 的方式来进行死锁检测。

5.1、死锁原因

5.1.1、相反加锁顺序死锁

不同表加锁顺序相反
相同表不同行加锁顺序相反

解决：调整加锁顺序

5.1.2、锁冲突死锁

RR 隔离级别下，插入意向锁与间隙锁，锁冲突死锁

描述：一个事务想要获取插入意向锁，但是有其他事务已经加了间隙锁或临键锁则会阻塞；

解决：降低隔离级别至RC

5.2、避免死锁

尽可能以相同顺序来访问索引记录和表
如果能确定幻读和不可重复读对应用影响不大，考虑将隔离级别降低为 RC
添加合理的索引，不走索引将会为每一行记录加锁，死锁概率非常大
尽量在一个事务中锁定所需要的所有资源，减小死锁概率
避免大事务，将大事务分拆成多个小事务；大事务占用资源多，耗时长，冲突概率变高
避免同一时间点运行多个对同一表进行读写的概率；

5.3、测试代码

并发死锁：session A 执行事务1，session B 执行事务2

DROP TABLE IF EXISTS `account_t`;
 CREATE TABLE `account_t` (
     `id` INT(11) NOT NULL,
     `name` VARCHAR(225) DEFAULT NULL,
     `money` INT(11) DEFAULT 0,
     PRIMARY KEY(`id`),
     KEY `idx_name` (`name`)
 ) ENGINE = innoDB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;
 SELECT * FROM `account_t`;
 INSERT INTO `account_t` VALUES (1, 'A', 1000), (2, 'B', 1000), (3, 'B', 1000);
 ROLLBACK;
 -- 1、相反加锁顺序死锁
 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 BEGIN
 -- 死锁事务1
 UPDATE `account_t` SET `money` = `money` - 100 WHERE `id` = 1;
 -- 死锁事务2
 UPDATE `account_t` SET `money` = `money` + 100 WHERE `id` = 1;
 COMMIT;
 -- 2、锁冲突死锁
 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 BEGIN
 -- 死锁事务1
 UPDATE `account_t` SET `money` = `money` - 100 WHERE `name` = 'C';
 -- 死锁事务2
 INSERT INTO `account_t` (`id`, `name`, `money`) VALUES (4, 'D', 1000);
 COMMIT;

Mysql 锁机制