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

马谦马谦马谦 2020年1月22日15:52:53 发表评论

一、行锁和两阶段锁协议

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

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

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

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

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

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

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

首先,第一个事务执行修改:

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

然后,第二个事务也执行修改:

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

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

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

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

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

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

行锁变表锁

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

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

二、死锁

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

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

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

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

死锁产生后,MySQL有两种方法来解决死锁:

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

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

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

  1. 合理规划数据表的执行顺序,尽量避免多个事务以不同顺序更新同一个表。如果都是相同的顺序访问,是不会产生死锁的。这种情况下,可以关闭死锁检测。
  2. 控制并发量,在代码中限制同时执行事务的数量,控制在10以内,超过的排队执行。这样就减少了死锁检测的次数,虽然有排队的事务,但是排队的时间远远小于多个事务之间的死锁检测时间。
本文共执行64次查询,耗时0.596秒!
历史上的今天
一月
22
马谦马谦马谦

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: