MySQL死锁

一、什么是死锁

死锁是指两个或者两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

二、死锁产生的四个必要条件

1、 互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。

2、请求保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放

3、不可剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放

4、 循环等待条件： 指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源

三、锁类型

MySQL根据锁粒度来分有三种锁的级别：页级、表级、行级。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

根据加锁模式分：间隙锁（Gap）、记录锁（Recordlock）、临键锁(next KeyLocks)

• next KeyLocks锁，同时锁住记录(数据)，并且锁住记录前面的Gap
• Gap锁，不锁记录，仅仅记录前面的Gap
• Recordlock锁（锁数据，不锁Gap）
• 所以其实 Next-KeyLocks=Gap锁+ Recordlock锁

间隙锁

1、当我们用范围条件(between)而不是相等条件(=)检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；

2、对于键值在条件范围内但不存在的记录，叫做 “间隙(GAP)”，InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁(Gap)锁。

间隙锁用来解决幻读。针对insert不允许插入

什么时候会出现gap间隙锁

 用唯一索引查询唯一的行数据，并不会产生gap锁。比如：

SELECT * FROM child WHERE id = 100;

如果id是唯一索引，就不会产生gap锁；如果id不是索引或者id不是唯一索引，那么会产生gap锁。

四、MySQL死锁案例

1、环境准备

部署的是Mysql 5.7，数据库隔离级别为REPEATABLE-READ

创建一张user表，id为主键并且在age字段上建非唯一索引

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`age` int(11) DEFAULT NULL COMMENT '年龄', 
`name` varchar(255) DEFAULT NULL COMMENT '姓名', 
PRIMARY KEY (`id`), 
KEY `idx_age` (`age`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='用户信息表';

插入两条数据

INSERT INTO `test`.`user`(`id`, `age`, `name`) VALUES (1, 10, 'zhangsan'); 
INSERT INTO `test`.`user`(`id`, `age`, `name`) VALUES (2, 20, 'lisi');

2、模拟死锁案例

开启事务A,执行一条update语句

update user set name='wangwu' where age=20;

开启事务B, 执行update语句

update user set name='zhaoliu' where age=10;

事务A执行一条insert语句，执行后处于阻塞状态

insert into user values(null, 15, "tianqi");

事务B执行一条insert语句，执行成功，这时事务A抛出“ Deadlock found when trying to get lock”异常

insert into user values(null, 30, "wangba");

分别对事务A和事务B进行commit操作。

查看表数据

我们可以看到，事务B的所有操作都成功了，而事务A的操作都回滚了。

3、查看死锁日志

通过'show engine innodb status'指令查看最近一次死锁信息

*************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2023-08-10 17:07:15 0x75c INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 4 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 12709 srv_active, 0 srv_shutdown, 80783 srv_idle srv_master_thread log flush and writes: 93492 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 114127 OS WAIT ARRAY INFO: signal count 112855 RW-shared spins 0, rounds 74867, OS waits 33282 RW-excl spins 0, rounds 763837, OS waits 21788 RW-sx spins 6843, rounds 180899, OS waits 5200 Spin rounds per wait: 74867.00 RW-shared, 763837.00 RW-excl, 26.44 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2023-08-10 17:04:03 0x4f4 *** (1) TRANSACTION: TRANSACTION 48081, ACTIVE 73 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 MySQL thread id 31, OS thread handle 12964, query id 35557493 localhost ::1 root update insert into user values (null, 15, "tianqi") *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1990 page no 4 n bits 72 index idx_age of table `test`.`user` trx id 48081 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) TRANSACTION: TRANSACTION 48082, ACTIVE 37 sec inserting mysql tables in use 1, locked 1 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 MySQL thread id 32, OS thread handle 1268, query id 35557494 localhost ::1 root update insert into user values (null, 30, "wangba") *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1990 page no 4 n bits 72 index idx_age of table `test`.`user` trx id 48082 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1990 page no 4 n bits 72 index idx_age of table `test`.`user` trx id 48082 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 48085 Purge done for trx's n:o < 48085 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 283619002564968, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283619002563224, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283619002562352, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283619002564096, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: wait Windows aio (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 131129 OS file reads, 1782501 OS file writes, 174552 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 104, seg size 106, 24950 merges merged operations: insert 151096, delete mark 35, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 13795056174 Log flushed up to 13795056174 Pages flushed up to 13795056174 Last checkpoint at 13795056165 0 pending log flushes, 0 pending chkp writes 76654 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137297920 Dictionary memory allocated 10027219 Buffer pool size 8192 Free buffers 1024 Database pages 7165 Old database pages 2624 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 200433, not young 9918483 0.00 youngs/s, 0.00 non-youngs/s Pages read 131102, created 780427, written 1623290 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 7165, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=8716, Main thread ID=1568, state: sleeping Number of rows inserted 35647570, updated 6, deleted 0, read 176745 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================

事务A相关日志

事务A的ID为48081，正在执行的sql为‘insert into user values (null, 15, "tianqi")’。

事务A正在等待锁释放(WAITING FOR THIS LOCK TO BE GRANTED)：插入意向排他锁(lock_mode X locks gap before rec insert intention waiting)，普通索引(idx_age)

事务B相关日志

事务B的ID为48082，正在执行的sql为‘insert into user values (null, 30, "wangba")’

事务B持有锁(HOLDS THE LOCK)：间隙锁(lockmode X locks gap before rec)，普通索引(index idx_age)

事务B正在等待锁释放(WAITING FOR THIS LOCK TO BE GRANTED)：插入意向锁(lock_mode X insert intention waiting)

回滚事务

从死锁日中中我们可以很明显的看出TRANSACTION 48081(事务A)和TRANSACTION 48082(事务B)都在等对方的锁释放，所以导致了死锁问题，并且mysql回滚了TRANSACTION 48081(事务A)。

4、死锁分析

1、事务A的SQL产生了哪些锁

• 事务A的update语句产生哪些锁

我们先来看

update user set name = 'wangwu' where age= 20;

记录锁

因为是等值查询，所以这里会在满足age=20的所有数据请求一个记录锁。

间隙锁

因为这里是非唯一索引的等值查询, 所以会产生间隙锁(如果是唯一索引的等值查询那就不会产生间隙锁，只会有记录锁), 因为这里只有2条记录 所以左边为(10,20),右边因为没有记录了，所以请求间隙锁的范围就是(20,+∞),加一起就是(10,20) +（20,+∞)。

Next-Key锁（临键锁）

Next-Key锁=记录锁+间隙锁，所以该Update语句就有了（10,20]的 Next-Key锁。

这里多说一句，Next-Key并不是记录锁+所有间隙锁。

为了弄清Next-Key的定义，我翻阅了下Mysql官网在Mysql5.7中对于Next-Key的解释：索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合。

• 事务A的insert语句产生哪些锁

INSERT INTO user VALUES (NULL, 15, "tianqi");

间隙锁

因为age 15(在10和20之间)，所以需要请求加(10,20)的间隙锁

插入意向锁

插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，即事务A需要插入意向锁(10,20)

2、事务B的SQL产生了哪些锁

• 事务B的update语句产生哪些锁

我们先来看

update user set name = 'zhaoliu' where age= 10

记录锁

因为是等值查询，所以这里会在满足age=10的所有数据请求一个记录锁。

间隙锁

因为左边没有记录,右边有一个age=20的记录，所以间隙锁的范围是(-∞,10),右边为(10,20),一起就是(-∞,10)+(10,20)。

Next-Key锁(临键锁)

Next-Key锁=记录锁+间隙锁，所以该Update语句就有了（-∞,10]的 Next-Key锁

• 事务B的install语句产生哪些锁

INSERT INTO user VALUES (NULL, 30, "wangba")

间隙锁

• 因为age 30(左边是20，右边没有值)，所以需要请求加(20,+∞)的间隙锁

插入意向锁

• (20,+∞)

5、死锁的产生

这样一来产生整个死锁的原因也就清楚了，事务A持有（10,20]的临键锁和(20,+∞)的间隙锁，而事务B持有（-∞,10]的临键锁和(10,20)的间隙锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁。

五、如何解决死锁

1.MySQL有两种死锁处理方式

1.等待，直到超时（innodb_lock_wait_timeout=50s）。 

2.发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on）

2.如果需要手动解除死锁，有一种最简单粗暴的方式，那就是找到线程id之后，直接干掉

1.查看当前正在进行中的线程，找到ID;也可以使用information_schema.INNODB_TRX，找到trx_mysql_thread_id字段表示事务线程ID show processlist 
2.在MySQL的shell里面执行,杀掉进程对应的线程id kill 线程id

六、如何避免死锁

1）不同的应用访问同一组表时，应尽量约定以相同的顺序访问各表。对一个表而言，应尽量以固定的顺序存取表中的行。这点真的很重要,它可以明显的减少死锁的发生。

举例：好比有a,b两张表，如果事务1先a后b,事务2先b后a,那就可能存在相互等待产生死锁。那如果事务1和事务2都先a后b，那事务1先拿到a的锁，事务2再去拿a的锁，如果锁冲突那就会等待 事务1释放锁，那自然事务2就不会拿到b的锁，那就不会堵塞事务1拿到b的锁，这样就避免死锁了。

2）在主键等值更新的时候，尽量先查询看数据库中有没有满足条件的数据，如果不存在就不用更新，存在才更新。为什么要这么做呢，因为如果去更新一条数据库不存在的数据，一样会产生间隙锁。

举例：如果表中只有id=1和id=5的数据，那么如果你更新id=3的sql，因为这条记录表中不存在，那就会产生一个(1,5)的间隙锁，但其实这个锁就是多余的，因为你去更新一个数据都不存在的数据没有任何意义。

3）尽量使用主键更新数据,因为主键是唯一索引，在等值查询能查到数据的情况下只会产生记录锁，不会产生间隙锁，这样产生死锁的概率就减少了。当然如果是范围查询，一样会产生间隙锁。

4）避免长事务，小事务发送锁冲突的几率也小。这点应该很好理解。

5）在允许幻读和不可重复度的情况下，尽量使用RC的隔离级别，避免gap lock造成的死锁，因为产生死锁经常都跟间隙锁有关，间隙锁的存在本身也是在RR隔离级别来解决幻读的一种措施。