A situation where different transactions are unable to proceed, because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither one ever releases the locks it holds.
A deadlock can occur when the transactions lock rows in multiple tables (through statements such as UPDATE or SELECT ... FOR UPDATE), but in the opposite order. A deadlock can also occur when such statements lock ranges of index records and gaps, with each transaction acquiring some locks but not others due to a timing issue.
MySQL 使用了几个特殊的表名来作为监控的开关，比如在数据库中创建一个表名为 innodb_monitor 的表开启标准监控，创建一个表名为 innodb_lock_monitor 的表开启锁监控。MySQL 通过检测是否存在这个表名来决定是否开启监控，至于表的结构和表里的内容无所谓。相反的，如果要关闭监控，则将这两个表删除即可。这种方法有点奇怪，在 5.6.16 版本之后，推荐使用系统参数的形式开启监控。
-- 开启标准监控
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
-- 关闭标准监控
DROP TABLE innodb_monitor;
-- 开启锁监控
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
-- 关闭锁监控
DROP TABLE innodb_lock_monitor;
在 MySQL 5.6.16 之后，可以通过设置系统参数来开启锁监控，如下：
-- 开启标准监控
SET GLOBAL innodb_status_output=ON;
-- 关闭标准监控
SET GLOBAL innodb_status_output=OFF;
-- 开启锁监控
SET GLOBAL innodb_status_output_locks=ON;
-- 关闭锁监控
SET GLOBAL innodb_status_output_locks=OFF;
另外，MySQL 提供了一个系统参数 innodb_print_all_deadlocks 专门用于记录死锁日志，当发生死锁时，死锁日志会记录到 MySQL 的错误日志文件中。
SET GLOBAL innodb_print_all_deadlocks=ON;
当我们遇到死锁问题的时候，可以通过 show engine innodb status 命令来获取死锁信息
mysql> SHOW engine innodb STATUS\G
*************************** 1. ROW ***************************
TYPE: InnoDB
Name:
STATUS:
......
-----------------------
-
LATEST DETECTED DEADLOCK
------------------------
2022-04-19 19:09:28 0x7f021c318700
*** (1) TRANSACTION:
TRANSACTION 555929313, ACTIVE 359 sec inserting
mysql TABLES IN USE 1, locked 1
LOCK WAIT 3 LOCK struct(s), heap SIZE 1136, 3 ROW LOCK(s), undo log entries 1
MySQL thread id 3628452, OS thread handle 139647055644416, query id 1752933820 localhost root UPDATE
INSERT INTO t1 (a, b) VALUE (10, 10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555929313 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;;
*** (2) TRANSACTION:
TRANSACTION 555930276, ACTIVE 273 sec inserting
mysql TABLES IN USE 1, locked 1
3 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3628453, OS thread handle 139647039670016, query id 1752934132 localhost root UPDATE
INSERT INTO t1 (a, b) VALUE (11, 10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555930276 lock_mode X
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; ASC supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555930276 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 (2)
HOLDS THE LOCK(S)
WAITING FOR THIS LOCK TO BE GRANTED