死锁是什么？模拟死锁并排查

死锁

两个或多个进程因为资源竞争导致相互等待的情况称为死锁。
死锁的发生条件（缺一不可）

• 持有并等待
• 互斥
• 不可抢占
• 循环等待

互斥、不可抢占都是独占锁的特征。互斥即一个资源只能被一个线程占用。不可抢占即资源被释放前不可再被抢占。
持有并等待、循环等待则是进程的行为特征。持有并等待：进程已经持有一个资源，继续申请另一个资源。循环等待：多个进程互相持有对方所要申请的资源，形成头尾相接的等待关系。

模拟死锁

employees表结构如下：

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+

通过控制台模拟死锁：
第一个会话：

start transaction;
-- 1
select * from employees where emp_no in (10001) for update;
-- 2
select * from employees where emp_no in (10002) for update;

第二个会话：

start transaction;
-- 3
select * from employees where emp_no in (10002) for update;
-- 4
select * from employees where emp_no in (10001) for update;

首先启动会话1，执行sql1，再启动会话2，执行sql3，接着会话1再执行sql2，会话2再执行sql4，此时死锁发生，sql立即报错：

mysql>  select * from employees where emp_no in (10001) for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

通过上面的实验，发现MySQL自动检测到了死锁并且自动回滚了会话2的事务。我们通过show engine innodb status; 可以找到MySQL检测到的死锁记录：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-05-18 10:59:12 0x7220
*** (1) TRANSACTION:
TRANSACTION 653343, ACTIVE 38 sec starting index read
-- 事务编号 653343,活跃秒数 38,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 4792, query id 56 localhost ::1 ODBC statistics
select * from employees where emp_no in (10002) for update
--当前正在等待锁的SQL语句.
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 529 page no 4 n bits 408 index PRIMARY of table `employees`.`employees` trx id 653343 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 00000009f272; asc      r;;
 2: len 7; hex f30000011e011d; 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 653344, ACTIVE 19 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 29216, query id 57 localhost ::1 ODBC statistics
select * from employees where emp_no in (10001) for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 529 page no 4 n bits 408 index PRIMARY of table `employees`.`employees` trx id 653344 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 00000009f272; asc      r;;
 2: len 7; hex f30000011e011d; 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 529 page no 4 n bits 408 index PRIMARY of table `employees`.`employees` trx id 653344 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 00000009f272; asc      r;;
 2: len 7; hex f30000011e0110; 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)

配合以下命令可以找到锁记录，对于那些等待时间很长的事务，可以直接kill。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+----------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+
| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table              | lock_index | lock_space | lock_page | lock_rec | lock_data |
+----------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+
| 653345:529:4:2 | 653345      | X         | RECORD    | `employees`.`employees` | PRIMARY    |        529 |         4 |        2 | 10001     |
| 653343:529:4:2 | 653343      | X         | RECORD    | `employees`.`employees` | PRIMARY    |        529 |         4 |        2 | 10001     |
+----------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 653345            | 653345:529:4:2    | 653343          | 653343:529:4:2   |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

总结

• 当执行导致死锁的sql时，mysql能够自动检测出锁并且自动回滚事务，通常回滚资源持有少的事务。
• 通过show engine innodb status; 可以找到死锁记录，从而发现哪些SQL会导致死锁。
• SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;和 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;可以找到锁以及锁等待记录的事务，然后通过kill命令杀死那些等待时间很长的事务
• mysql等待锁超时时会自动释放

避免死锁的方法

1. 按序申请资源：比如将资源散列成数值，从小到大依次申请
2. 减少锁的范围，提高粒度
3. 使用tryAcquire获取锁，获取失败采取补救措施
4. 设置超时时间
5. 避免嵌套锁