MySQL中的行锁、两阶段锁协议以及死锁

2020 年 01 月 22 日

21 次浏览

2715字数

一、行锁和两阶段锁协议

行锁：顾名思义，就是对某一行加锁，修改的时候不会锁住整个表。相对于表锁来说，行锁的开销更大（因为涉及到MVCC等需要保存快照），但是粒度更小，更适合于高并发场景。行锁是每个引擎单独实现的，但是并不是所有的引擎都实现了行锁。例如MyISAM就没有实现行锁，它只支持表锁，而InnoDB支持行锁，因此高并发场景基本都会选择InnoDB作为数据库引擎。

行锁是系统自动加上的，无需手动干预。当多个事务同时更新一行数据时，后来事务的更新操作会被阻塞，直到行锁被释放才能更新。而行锁并不是在事务一启动就加上了，而是在真正需要的时候才加锁，也就是说只有在更新的时候才对行加锁。这样做有两个好处：

减小锁的粒度和加锁时长，提高并发度。
事务启动的时候，并没有明确说明需要修改什么行，此时如果如果要锁定行必须锁定整个表才行。

和加锁时机不同，行锁不是在更新完行之后立马就解锁，而是在事务执行完成（执行了commit或者rollback）之后才解锁。这两个加锁的时机被称为两阶段锁协议。

例如以下表包含了学生信息：

# 创建表
CREATE TABLE stu_info  (
  `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `age` tinyint(0) UNSIGNED NOT NULL DEFAULT 20,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8;
# 插入三行数据
insert into stu_info(name, age) values('maqian', 24), ('zed', 10), ('yasuo', 15);

启动两个事务，同时修改id为2的学生的年龄，其中事务一先修改但不提交，事务二也修改同一行内容，事务二就会被阻塞。具体的执行流程为：

首先，第一个事务执行修改：

事务1

然后，第二个事务也执行修改：

此时事务二被阻塞，因此这一条记录已经被事务一锁定了，要等待事务一释放锁后才能修改。

一直到一段时间过去后事务二会弹出报错，意思时等待锁超时了：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

此时事务二虽然不能修改id为2的记录，但是可以修改其他id的记录：

这说明，两个事务都只是对各自修改的记录行加锁了，并没有对整个表加锁。两个事务执行完成后的表：

+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | maqian |  24 |
|  2 | zed    |  16 |
|  3 | yasuo  |  18 |
+----+--------+-----+

行锁变表锁

当行锁涉及的索引失效时，会导致行锁变成表锁。例如上面的示例中修改条件都是id = 2，因为id是主键，默认会生成索引。因此两个事务更新记录时使用的是行锁，事务一锁住id = 2的行的时候，事务二还是能更新id = 3的行。

但是一旦把条件改成其他列，即where后面的条件改成其他（如where name 'zed'）时，行锁会升级成表锁。即使事务一只修改name = 'zed'的列，事务二也无法修改name = 'yasuo'这一列。

二、死锁

在事务中，多个事务间同时对不同的行加锁，可能会导致死锁。例如以下场景：

事务一先修改id = 3的用户的年龄并锁住该行，然后事务二修改id = 2的年龄也锁住这行。接着事务一修改id = 2的用户年龄，因为这一行已经被事务二锁住了，所以这条语句会被阻塞，要等到事务二释放锁才能继续。但是此时事务二想修改id = 3的用户的年龄，刚好这行又被事务一给锁住。两个事务互相都要等待对方先释放锁，产生了死锁。

这个场景比较类似《unix环境高级编程》中对死锁产生原因的描述：当多个线程以不当的顺序同时对多个锁加锁就会导致死锁。

死锁产生后，MySQL有两种方法来解决死锁：

等待锁超时。如上面测试的一样，当更新语句等待锁一段时间后会超时退出，不会无休止等待下去。但是这个超时时间默认是50S，太长了，在高并发系统中是无法接受的。
设置死锁检测。通过设置innodb_deadlock_detect = on，开启MySQL的死锁检测，系统会自动检测死锁的事务并回滚改动。

大部分时候使用的都是方式二，主动检测死锁来释放锁，因为方式一超时时间太长了。但是方式二也有缺点是检测时间是O($n^2$)。例如，有100个事务在执行的时候，每个事务执行的时候都要和另外99个线程检测是否存在死锁。此时需要执行10000次死锁检测，当事务的数量再上升的时候，死锁的检测又会上升一个量级。

如何解决这个问题呢？一般有以下几种办法：

合理规划数据表的执行顺序，尽量避免多个事务以不同顺序更新同一个表。如果都是相同的顺序访问，是不会产生死锁的。这种情况下，可以关闭死锁检测。
控制并发量，在代码中限制同时执行事务的数量，控制在10以内，超过的排队执行。这样就减少了死锁检测的次数，虽然有排队的事务，但是排队的时间远远小于多个事务之间的死锁检测时间。

MySQL中的行锁、两阶段锁协议以及死锁