关于mysql死锁的两个详细经典案例

时间：2024-03-29 09:51:02

       写博客至今一年有余，感觉还没写出几篇比较有质量的文章，首先确实是自己有点懒，没有及时更新博客，但是还有一方面是有些笔记不适合分享出来，所以造成了没有几篇是比较有质量的，今天要整理的文章是关于这两天学习的mysql死锁和锁等待笔记，笔者觉得是比较有趣的，也算是比较有质量的文章，希望读者看完之后有所收获。

       关于mysql锁的一些基础知识后期再更新一篇博客，这里先直接进入死锁的案例，不明白基础知识的读者请先去查找其他相关的博客学习以下。

 案例1：Record Lock导致的死锁——两个不同的事务对两个不同的资源获取排他锁的顺序不一致导致的死锁；（这边使用select for update进行学习，实际企业应用中很少有该应用场景，更多的是update或者insert或者两者混合操作，该操作同理）

表结构：cre_course（primary key CSE_ID，name）

  • 在sessionA开启事务，设置set autocommit = 0;
  • 执行加排他锁操作：select * from cre_course WHERE CSE_ID = "1" for UPDATE ，此时相当于在sessionA锁定了资源1；
  • 在sessionB开启事务，设置set autocommit = 0;
  • 执行加排他锁操作：select * from cre_course WHERE CSE_ID = "2" for UPDATE ，此时相当于在sessionB锁定了资源2；
  • 在sessionA执行sessionB的语句，select * from cre_course WHERE CSE_ID = "2" for UPDATE ，获取资源2，此时由于sessionB未执行commit，所以还没释放资源2，所以session1此时进入锁等待，等待sessionB事务提交释放资源2；即sessionA等sessionB的资源2
  • 在sessionB执行sessionA的语句，select * from cre_course WHERE CSE_ID = "1" for UPDATE ，获取资源1，此时由于sessionA未执行commit，所以还没释放资源1，所以sessionB此时进入锁等待，等待sessionA事务提交释放资源1；即sessionB等sessionA的资源1
  • 结果：sessionB出现死锁现象：Deadlock found when trying to get lock; try restarting transaction；此时，由于mysql的死锁自动检测机制，会将sessionB进行事务回滚结束事务，释放资源2，所以sessionA就可以正常获取到资源2；但是此时sessionA还是需要提交事务， 这样其所作的update才算最终持久化；

 

    案例2：Next-key Lock+Record Lock导致的死锁——sessonA等待sessionB的Record Lock，sessionB等sessionA的Next-key Lock

表结构：user_info（primary key id，name，age key，tel unique key） 表结构分析：id为 Record Lock， age为Next-key Lock，tel为 Record Lock

  • 已知表中存在 age有：10，20，30，40，50，60，70； tel有：11，22，33，44，55，66，77，这两个字段组合关系没有限制
  • 步骤1：sessionA执行：delete FROM user_info WHERE age = 55； 结果：正常执行，分析：此时sessionA获取了age范围为[50，60]的Next-key Lock；
  • 步骤2：sessionB执行：INSERT INTO user_info VALUES(REPLACE(UUID(),"-",""),"1028 session A",65,"88") ；结果：正常执行；分析：因为age=65不在sessionA锁定的Next-key Lock范围内，且tel=88不存在主键冲突，所以session正常执行；且获得tel=88的Record Lock；
  • 步骤3：sessionA执行：INSERT INTO user_info VALUES(REPLACE(UUID(),"-",""),"1028 session A",65,"88") ；结果：锁等待， 分析：因为age为普通索引，所以不存在age的锁冲突，但是tel为Record Lock，且在sessionB已经执行了未提交的tel=88的事务，获得了88的Record Lock，这里再执行tel=88的动作，就会进入tel的锁等待；即A等B的telX锁
  • 步骤4：sessionB执行：INSERT INTO user_info VALUES(REPLACE(UUID(),"-",""),"1028 session A",52,"99") ；结果：两个session进入死锁状态，，mysql主动放弃sessionA事务，对事务A进行rollback，所以sessionB正常执行；分析：tel=99不存在冲突，所以不影响，但是age=52，处于sessionA锁定的[50，60]的Next-key Lock；开始进入锁等待，即B等A的age的Next-key Lock，与sessionA的锁等待形成死锁；如下图所示：
  • sessionA:
  • 关于mysql死锁的两个详细经典案例
  • sessionB:
  • 关于mysql死锁的两个详细经典案例
  • 对比：如果步骤2到步骤4insert的age不变，而tel全部不一样，此时还会死锁？ 答案：此时不会死锁，但是会进入B等A的锁等待，直至A事务提交或者B锁等待超时；因为此时不存在tel的锁冲突，而步骤2和步骤3的age虽然同为65，但是因为age只是普通的索引，所以步骤2锁定的只是自己插入的age=65的意向排他锁，而步骤3也是锁定自己插入的age=65的意向排他锁，两个的排他锁不是同一个锁，所以步骤3正常执行；但是步骤4的age=52再步骤1的[50，60]的Next-key Lock范围内，所以进入锁等待； 

 

相关文章