Mysql查看死锁与解除死锁的深入讲解

什么是死锁

在多个并发事务中，每个事务都需要访问其他事务持有的资源时，如果某个事务因为等待资源而被阻塞，同时它又持有其他事务需要的资源，就会发生死锁现象。

查看死锁

可以使用以下命令查看Mysql中的死锁信息：

SHOW ENGINE INNODB STATUS;

该命令会返回一个INNODB STATUS的输出，其中包含有关当前事务和锁定状态的信息，包括死锁信息。

在输出中找到“LATEST DETECTED DEADLOCK”这一行，它将提供有关最新检测到的死锁的信息。例如：

LATEST DETECTED DEADLOCK
------------------------
2020-07-14 12:00:00 0x7f0f4b909700
*** (1) TRANSACTION:
TRANSACTION 21387481, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 1234567, OS thread handle 47151658949888, 

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 134463 page no 51 n bits 288 index ux_id of table `db`.`test` trx id 21387481 lock_mode X waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 9; compact format; info bits 32

*** (2) TRANSACTION:
TRANSACTION 21387485, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 6
MySQL thread id 1234568, OS thread handle 47131778237296, 

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 134463 page no 51 n bits 288 index ux_id of table `db`.`test` trx id 21387485 lock_mode X
Record lock, heap no 8 PHYSICAL REDO n_fields 9; compact format; info bits 32

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 134463 page no 51 n bits 288 index ux_id of table `db`.`test` trx id 21387485 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 9; compact format; info bits 32

*** WE ROLL BACK TRANSACTION (2)

解除死锁

当发生死锁时，需要手动解除死锁。可以在Mysql客户端使用以下语句来解除死锁：

KILL <thread_id>;

其中<thread_id>为发生死锁的线程ID。

同时，也可以将innodb_lock_wait_timeout设置为一个较小的值，以尽快检测和解除死锁。例如：

SET innodb_lock_wait_timeout = 120;

当等待锁定时间超过120秒时，将自动解除死锁。

示例说明

下面是两个关于Mysql死锁的示例：

示例一

假设表t1和t2中都有数据，执行以下事务：

-- 事务一
START TRANSACTION;
UPDATE t1 SET c1 = c1 + 1 WHERE id = 1;
UPDATE t2 SET c2 = c2 + 1 WHERE id = 1;
COMMIT;

-- 事务二
START TRANSACTION;
UPDATE t2 SET c2 = c2 + 1 WHERE id = 1;
UPDATE t1 SET c1 = c1 + 1 WHERE id = 1;
COMMIT;

如果以上两个事务同时进行，就会陷入死锁现象。可以通过查看死锁信息来找到故障源并解除死锁。假设输出如下：

LATEST DETECTED DEADLOCK
------------------------
2020-07-14 12:00:00 0x7f0f4b909700
*** (1) TRANSACTION:
TRANSACTION 21387481, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 1234567, OS thread handle 47151658949888, 

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 134463 page no 51 n bits 288 index ux_id of table `db`.`t1` trx id 21387481 lock_mode X waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 9; compact format; info bits 32

*** (2) TRANSACTION:
TRANSACTION 21387485, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 6
MySQL thread id 1234568, OS thread handle 47131778237296, 

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 134463 page no 51 n bits 288 index ux_id of table `db`.`t1` trx id 21387485 lock_mode X
Record lock, heap no 8 PHYSICAL REDO n_fields 9; compact format; info bits 32

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 134463 page no 51 n bits 416 index PRIMARY of table `db`.`t2` trx id 21387485 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (2)

第一个事务（线程ID为21387481）正在等待表t1的锁，而第二个事务（线程ID为21387485）则正在持有表t1的锁，并在等待表t2的锁。因此，第二个事务被回滚以解除死锁。

示例二

假设表t中有数据，执行以下单个事务：

START TRANSACTION;
UPDATE t SET c = c + 1 WHERE id = 1;
UPDATE t SET c = c + 1 WHERE id = 2; -- 将where条件改为id = 1
COMMIT;

在第二个UPDATE语句中，将WHERE条件从id = 2更改为id = 1，这会导致在更新表之前发生死锁。

当执行该事务时，Mysql可能会输出以下消息：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

此时可以使用SHOW ENGINE INNODB STATUS来查看死锁信息，然后手动解除死锁。需要注意的是，Mysql在发生死锁时会自动回滚修改，因此不需要手动恢复数据。