mysql系列10—mysql锁

背景

mysql中锁机制核心是保证数据的一致性以及并发控制。锁机制的实现与存储引擎有关，本文介绍的是INNODB存储引擎的锁机制；其他存储引擎如myISAM和memory等仅支持表锁不支持行锁，不是本文关注的重点。
本文介绍mysql数据库提供的锁机制，包括共享锁和排它锁、表锁和行锁、间隙锁和next_key锁。理解本文后，有助于在不同业务场景设计出合理的索引结构。最后介绍死锁的检测和处理方式。
本文会结合案例进行介绍，表结构和数据如下所示:

CREATE TABLE `t_student` (
	`id`    INT(10)      NOT NULL COMMENT '学号，唯一ID',
	`name`  VARCHAR(50)  NOT NULL COMMENT '姓名',
    `score` INT(10)      NOT NULL COMMENT '分数',
	PRIMARY KEY (`id`) USING BTREE,
    INDEX `idx_score` (`score`) USING BTREE
)
ENGINE=InnoDB
;

mysql> select * from t_student;
+-----+-----------+-------+
| id  | name      | score |
+-----+-----------+-------+
|   2 | 测试2     |     2 |
|  10 | 测试10    |    10 |
| 100 | 测试100   |   100 |
| 101 | 测试101   |   101 |
| 200 | 测试200   |   200 |
+-----+-----------+-------+

1.共享锁和排他锁

mysql中加锁需要依次确定锁类型、锁粒度，即先确定是加共享锁还是排它锁，然后确定使用行锁还是表锁。
本章介绍mysql锁的类型，包括共享锁和排他锁, 定义如下:

共享锁(Share, 也称为S锁)，是一种允许多个事务同时对同一数据行或资源进行读操作的锁；它保证了多个事务可以并发地读取数据，但不允许其他事务对数据进行写操作。

排他锁(Exclude, 也称为X锁)是一种独占锁，用于写操作；当一个事务对某行数据加了排他锁后，其他事务不能对该行加任何类型的锁（包括共享锁和排他锁）。

mysql引入了MVCC解决了并发读写问题，因此普通的select语句不会加锁，特殊的select语句才会加锁：

-- 共享锁
select * from table_name where ... lock in share mode;

-- 排它锁
select * from table_name where ... for update;

delete、update、insert等修改修改语句会添加排它锁。

2.表锁和行锁

行锁以行为单位进行加锁；锁冲突小，并发度较高; 表锁对整个表进行加锁；锁冲突大，并发度较低。
加锁的范围由where语句确定, 通过以下三种场景进行介绍。
Note1：行锁添加在索引上，如果没有索引，会退化为表锁
案例：事务A将name='测试2'的列对应的name字段修改为’生产2’; 事务B将name='测试10'的列对应的name字段修改为’生产10’.

案例如上所示, 由于name字段上没有添加索引，所以where name = '测试2’条件的锁为表锁；此时，另一事务修改t_student中的其他记录时会阻塞。

重置数据库状态为初始状态，对name添加索引(UNIQUE INDEX unique_name(name) USING BTREE)，再次执行上述案例:

由于name字段上添加了唯一索引，所以where name = '测试2’条件的锁为行锁；此时，另一事务修改t_student中的其他记录时不会阻塞。

Note2：列必须是主键或者唯一索引，否者(普通索引)加的锁是next-key锁

next-key锁请参考章节3.间隙锁和next_key锁

案例：事务A将score=200的列对应的name字段修改为’生产200’, 事务B新增一条记录(id=150,name=‘测试150’, score=150).

案例如上所示, 由于score字段为普通索引(不是主键或者唯一索引)，所以where score= 200 条件的锁为next-key锁, 加锁范围为(101, 200]；此时，事务B新增的列score值为150在(101, 200]范围内，因此事务B被阻塞(直到事务A提交后才会执行)。

重置数据库至初始状态，如果将 where score = 200 修改为 id = 200(或name=‘测试200’), 结果如下:

id=200只会给当前记录加锁，不会获取id=150的记录锁；因此事务B不会被阻塞。

Note3：必须是精确匹配，否者(范围、模糊查询)加的锁是间隙锁

间隙锁请参考章节3.间隙锁和next_key锁

案例：事务A将满足id<=100条件的列对应的name字段修改为’生产’, 事务B新增一条记录(id=50,name=‘测试50’, score=50), 事务C新增一条记录(id=150,name=‘测试150’, score=150).

案例如上所示, id<=100条件的锁为间隙锁, 加锁范围为(-无穷, 100]；此时事务B新增的列score值为50在(-无穷, 100]范围内，因此事务B被阻塞(直到事务A提交后才会执行)；而事务C新增的列score值为150，不在(-无穷, 100]范围内，因此事务C不被阻塞。

3.间隙锁和next_key锁

间隙锁: 对于一个范围而不是一条记录添加索引，当对主键或者唯一索引使用范围查询时，mysql会对这个范围加锁。在章节2的Note3中，id<=100条件的锁为间隙锁, 加锁范围为(-无穷, 100].
next_key锁: 对于通索引(非主键和唯一索引)，会在一个范围加锁，称为next-key锁。next-key是一个前开后闭的区间，对于案例数据，如果需要操作score=200的数据，加锁范围为:（101,200]; 如果需要操作score=100的数据，加锁范围为:（10, 100];

mysql> select * from t_student;
+-----+-----------+-------+
| id  | name      | score |
+-----+-----------+-------+
|   2 | 测试2     |     2 |
|  10 | 测试10    |    10 |
| 100 | 测试100   |   100 |
| 101 | 测试101   |   101 |
| 200 | 测试200   |   200 |
+-----+-----------+-------+

说明：间隙锁和next_key锁通过在一个范围加锁，可以有效避免幻读的发生。

4.意向锁

略，Note: 意向锁的引入仅仅是为了提高mysql锁机制的判断效率，由Innodb内部使用(添加和释放)，与前面介绍的锁无任何冲突，用户无感知(可以理解为不存在这种锁)。

5.死锁问题

mysql事务在执行过程中会根据需要获取锁，锁被其他事务占据时会持续等待(或者超时报错退出);
获取的锁在事务结束的时候才会释放，因此当事务间锁相互持有或者循环持有的情况发送时就会导致死锁：

死锁检测

mysql中存在死锁检测机制，当检测到死锁时，会自动中止其中一个事务并释放锁，被中止的事务抛出ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction的异常。

案例如下所示:

执行过程如下所示:

执行SHOW ENGINE INNODB STATUS;可以在"LATEST DETECTED DEADLOCK"段中查看死锁信息:

# 剔除了一些不必要信息，突出重点
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-02-07 16:33:53 139664724940544
*** (1) TRANSACTION:
TRANSACTION 38235789, MySQL thread id 12587, OS thread handle 139658703853312, query id 323778 localhost root updating
update t_student set name='testA' where id=100

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 15112 page no 4 n bits 120 index PRIMARY of table `test`.`t_student` trx id 38235789 lock_mode X locks rec but not gap Record lock, 
heap no 48 PHYSICAL RECORD【用锁X48表示】

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15112 page no 4 n bits 120 index PRIMARY of table `test`.`t_student` trx id 38235789 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 PHYSICAL RECORD【用锁X51表示】


*** (2) TRANSACTION:
TRANSACTION 38235791, MySQL thread id 12588, OS thread handle 139664290703104, query id 323785 localhost root updating
update t_student set name='testB' where id=2

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 15112 page no 4 n bits 120 index PRIMARY of table `test`.`t_student` trx id 38235791 lock_mode X locks rec but not gap Record lock, 
heap no 51 PHYSICAL RECORD【用锁X51表示】

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15112 page no 4 n bits 120 index PRIMARY of table `test`.`t_student` trx id 38235791 lock_mode X locks rec but not gap waiting Record lock,
heap no 48 PHYSICAL RECORD【用锁X48表示】

*** WE ROLL BACK TRANSACTION (2)

信息比较清晰:
检测到死锁，对应两个事务标记为事务(1)和事务(2):
事务(1)的事务ID为38235789，已持有了锁X48; 然后执行update t_student set name=‘testA’ where id=100语句获取锁X51失败阻塞;
事务(2)的事务ID为38235791，已持有了锁X51; 然后执行update t_student set name=‘testB’ where id=2语句获取锁X48失败阻塞;
mysql选择回滚事务(2)以解决死锁问题。

避免死锁问题的策略

[1] 保持加锁顺序的一致性
上述案例中，如果事务A和事务B以相同的加锁顺序执行SQL语句，不会发送死锁现象;

[2] 减少事务的颗粒度
事务的锁在事务提交后才会释放，事务颗粒度越大，执行的SQL语句越多，获取的锁越多，约容易造成死锁现象;

[3] 设计合理的索引和SQL条件语句
间隙锁和next-key锁相对于行锁更容易发生死锁现象。