【MySQL】MySQL中的死锁

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

死锁是必然发生在多操作者（M>=2 个）情况下，争夺多个资源（N>=2 个，且N<=M）才会发生这种情况。很明显，单线程自然不会有死锁；单资源只会产生激烈竞争，也不会产生死锁。同时，死锁还有几个要求：

1. 争夺资源的顺序不对，如果争夺资源的顺序是一样的，也不会产生死锁；
2. 争夺者拿到资源不放手。

死锁学术化的定义

死锁的发生必须具备以下四个必要条件：

1. 互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。
2. 请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。
3. 不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
4. 环路等待条件：指在发生死锁时，必然存在一个进程有资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。

理解了死锁的原因，尤其是产生死锁的四个必要条件，就可以最大可能地避免、预防和解除死锁。只要打破四个必要条件之一就能有效预防死锁的发生：

• 打破互斥条件：改造独占性资源为虚拟资源，大部分资源已无法改造。
• 打破不可抢占条件：当一进程占有一独占性资源后又申请一独占性资源而无法满足，则退出原占有的资源。
• 打破占有且申请条件：采用资源预先分配策略，即进程运行前申请全部资源，满足则运行，不然就等待，这样就不会占有且申请。
• 打破循环等待条件：实现资源有序分配策略，对所有设备实现分类编号，所有进程只能采用按序号递增的形式申请资源。

避免死锁常见的算法有有序资源分配法、银行家算法。

MySQL中的死锁

所以MySQL中的死锁的成因是一样的。

演示死锁的产生

T1	T2
begin;
select * from t_emp where emp_no=‘10001’ for update; – 成功
	begin;
	select * from t_emp where emp_no=‘10002’ for update; – 成功
	select * from t_emp where emp_no=‘10001’ for update; – 阻塞
select * from t_emp where emp_no=‘10002’ for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

MySQL检测到了死锁，并结束了会话1中事务的执行，此时，切回会话2，发现原本阻塞的SQL语句执行完成了。

查询事务加锁的情况

可以通过

show engine innodb status\G

查看事务加锁的情况，不过一般情况下，看不到哪个事务对哪些记录加了那些锁，需要修改系统变量innodb_status_output_locks（MySQL5.6.16 引入），缺省是OFF。

mysql> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output_locks | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)

我们需要设置为ON，

set global innodb_status_output_locks = ON;

然后开启事务，并执行上面死锁产生的语句，同时通过show engine innodb status\G可以看见死锁的详细情况：

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2022-01-17 02:39:50 0x7fbf640c8700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3 srv_active, 0 srv_shutdown, 3302 srv_idle
srv_master_thread log flush and writes: 3305
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4
OS WAIT ARRAY INFO: signal count 4
RW-shared spins 0, rounds 4, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 4.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-01-17 02:39:43 0x7fbf64086700
*** (1) TRANSACTION:
TRANSACTION 6424, ACTIVE 22 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 2, OS thread handle 140459994023680, query id 64 localhost root statistics
select * from t_emp where emp_no='10002' for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48 page no 4 n bits 408 index PRIMARY of table `employees`.`t_emp` trx id 6424 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 80002712; asc   ' ;;
 1: len 6; hex 0000000008c4; asc       ;;
 2: len 7; hex ca000001da011c; asc        ;;
 3: len 3; hex 8f58c2; asc  X ;;
 4: len 7; hex 42657a616c656c; asc Bezalel;;
 5: len 6; hex 53696d6d656c; asc Simmel;;
 6: len 1; hex 02; asc  ;;
 7: len 3; hex 8f8375; asc   u;;

*** (2) TRANSACTION:
TRANSACTION 6425, ACTIVE 16 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 140459993753344, query id 65 localhost root statistics
select * from t_emp where emp_no='10001' for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 48 page no 4 n bits 408 index PRIMARY of table `employees`.`t_emp` trx id 6425 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 80002712; asc   ' ;;
 1: len 6; hex 0000000008c4; asc       ;;
 2: len 7; hex ca000001da011c; asc        ;;
 3: len 3; hex 8f58c2; asc  X ;;
 4: len 7; hex 42657a616c656c; asc Bezalel;;
 5: len 6; hex 53696d6d656c; asc Simmel;;
 6: len 1; hex 02; asc  ;;
 7: len 3; hex 8f8375; asc   u;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48 page no 4 n bits 408 index PRIMARY of table `employees`.`t_emp` trx id 6425 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 80002711; asc   ' ;;
 1: len 6; hex 0000000008c4; asc       ;;
 2: len 7; hex ca000001da0110; asc        ;;
 3: len 3; hex 8f4322; asc  C";;
 4: len 6; hex 47656f726769; asc Georgi;;
 5: len 7; hex 466163656c6c6f; asc Facello;;
 6: len 1; hex 01; asc  ;;
 7: len 3; hex 8f84da; asc    ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 6426
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421934991043320, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6424, ACTIVE 29 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 2, OS thread handle 140459994023680, query id 66 localhost root starting
show engine innodb status
TABLE LOCK table `employees`.`t_emp` trx id 6424 lock mode IX
RECORD LOCKS space id 48 page no 4 n bits 408 index PRIMARY of table `employees`.`t_emp` trx id 6424 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 80002711; asc   ' ;;
 1: len 6; hex 0000000008c4; asc       ;;
 2: len 7; hex ca000001da0110; asc        ;;
 3: len 3; hex 8f4322; asc  C";;
 4: len 6; hex 47656f726769; asc Georgi;;
 5: len 7; hex 466163656c6c6f; asc Facello;;
 6: len 1; hex 01; asc  ;;
 7: len 3; hex 8f84da; asc    ;;

RECORD LOCKS space id 48 page no 4 n bits 408 index PRIMARY of table `employees`.`t_emp` trx id 6424 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 80002712; asc   ' ;;
 1: len 6; hex 0000000008c4; asc       ;;
 2: len 7; hex ca000001da011c; asc        ;;
 3: len 3; hex 8f58c2; asc  X ;;
 4: len 7; hex 42657a616c656c; asc Bezalel;;
 5: len 6; hex 53696d6d656c; asc Simmel;;
 6: len 1; hex 02; asc  ;;
 7: len 3; hex 8f8375; asc   u;;

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (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
631 OS file reads, 53 OS file writes, 7 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 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, 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 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)
0.00 hash searches/s, 0.09 non-hash searches/s
---
LOG
---
Log sequence number 378949186
Log flushed up to   378949186
Pages flushed up to 378949186
Last checkpoint at  378949177
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 287170
Buffer pool size   8192
Free buffers       7649
Database pages     543
Old database pages 220
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 509, created 34, written 36
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 543, 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=1, Main thread ID=140459573761792, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 16
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.07 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

说明：

RECORD LOCKS space id 48 page no 4 n bits 408 index PRIMARY of table `employees`.`t_emp` trx id 6425 lock_mode X locks rec but not gap

表示一个内存中的锁结构：

• space id 48：表空间id为48
• page no 4：页编号为4
• index PRIMARY：对应的索引是t_emp的主键索引
• lock_mode X locks rec but not gap：存放的是一个X型的记录锁

间隙锁导致的死锁问题

T1	T2
begin;
select * from lock_test where a=14 for update;
	begin;
	select * from lock_test where a=16 for update;
	insert into lock_test value(19,19,19,19); – 阻塞
insert into lock_test value(18,18,18,18); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

不同于写锁相互之间是互斥的原则，间隙锁之间不是互斥的，如果一个事务T1获取到了(10,20)之间的间隙锁，另一个事务T2也可以获取到(10,20)之间的间隙锁，这时就可能会发生死锁问题。