怎么在mysql中写一个死锁

前言

发现错误请指正

mysql有哪些锁

• 官方文档中，mysql的锁主要有四种：

  1. X锁（独占锁），只能有一个事务拥有
  2. S锁（共享锁），多个事务可以同时拥有
  3. IX锁（意向独占锁），表级锁，上X行锁之前先上IX锁，IX锁之间不冲突（告诉其他想上X表锁的事务，这里已经有IX锁了）
  4. IS锁（意向共享锁），表级锁，上S行锁之前先上IS锁，IS锁之间不冲突（告诉其他想上S表锁的事务，这里已经有IS锁了）

• 为了方便理解，我们可以把上面的锁看作两种：

  1. 读锁（S锁，IS锁），同一时间可以有多个事务对指定数据上读锁，某行上了读锁，意味着其他事务不能对其上写锁，但是依然可以通过简单的select查询
  2. 写锁（X锁，IX锁），同一时间只能有一个事务对指定数据上写锁，某行上了写锁，意味着其他事务不能对其上写锁或读锁，但是依然可以通过简单的select查询

• 而我们平时所说的“锁”，大部分情况下是指“写锁”，mysql中的写锁类型主要有以下三种：

  1. Record Lock（或者LOCK_REC_NOT_GAP），即行锁，只锁住某一行，read committed隔离级别下，会对查询匹配到到行使用该锁
  2. Gap Lock（或者LOCK_GAP），即区间锁，锁住某行上下的区间，但不包括行本身，repeatable read会使用该锁
  3. Next-Key Locks（或者LOCK_ORDINARY），即临键锁，锁住和其上下区间，repeatable read会使用该锁

什么情况下会触发锁？

• 显式触发
  1. 执行select ... for update触发写锁
  2. 执行select ... in share mode触发读锁
• 隐式触发
  1. 执行update .../insert .../delete ...都会触发写锁

怎么写一个死锁

死锁产生的方式有很多种，本文只描述一种工作中很可能会遇到到情况：在mysql默认对repeatable read隔离级别（大部分同学应该都是用默认的），同一个事务中，对同一张表多次进行update/delete/insert

在该隔离级别下，区间锁和临键锁生效，根据mysql官方文档的描述，在对某一行执行update/delete/insert的时候，如果条件语句中用到的是唯一索引或主键索引，则只触发行锁，当遇到普通索引，则临键锁会被触发。

假设一张表结构如下，其中user_id为普通索引，id为自增主键：

image.png

演示写锁：

1. 第一阶段

• 事务A：

begin;
delete from table where user_id = 10;    --理论上此时user_id区间(-∞, 10] 和(10, 20)都被锁住了

• 事务B:

begin;
insert into table(user_id) values (13);  --尝试往事务A锁住的区间中插入数据，此时事务B阻塞，因为事务A还没提交

2. 第二阶段

• 事务A

commit;  --事务A提交，事务B解除阻塞，成功插入数据

演示死锁：

假设表结构和数据和上述一致：

image.png

1. 第一阶段

• 事务A：

begin;
delete from table where user_id = 10;    --理论上此时user_id区间(-∞, 10] 和(10, 20)都被事务A锁住了

• 事务B：

begin;
delete from table where user_id = 30;    --理论上此时user_id区间[20, 30)和[30, +∞)被事务B锁住了

2. 第二阶段

• 事务A：

insert into table(user_id) values (24);   --事务A尝试插入数据，但是该区间已经被事务B锁住，因此阻塞，等待事务B释放锁

• 事务B：

insert into table(user_id) values (13);  --事务B尝试插入数据，但是该区间已经被事务A锁住，因此阻塞，等待事务A释放锁

• 结果：事务A和事务B互相等待对方释放锁，导致死锁

  
  image.png

总结

在同一个事务中对同一张表进行多次写操作时要谨慎，在高并发情况下会出现死锁，如何尽可能避免死锁：

• 大事务拆小事务，大事务更容易发生死锁
• 事务中尽可能一次锁定所有需要的资源，减少死锁概率
• 降低隔离级别（如果能接受脏读幻读）
• 添加合理的索引