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 锁：排他锁，写锁，不允许其他事务读取和修改

间隙锁

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 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;