InnoDB 死锁和问题排查

死锁（dead lock）

两个及以上的事务各自持有对方需要的锁，导致双方都无法继续执行，只能互相等待（循环等待）。因为双方都在等待对方释放锁，而各自又无法释放锁，从而导致了死循环。这就是死锁（dead lock）。

当两个事务锁定了多张表的多条记录（如 UPDATE 或者 SELECT … FOR UPDATE），且它们各自锁定的顺序相反时，就会出现死锁。当 sql 语句锁定了多行记录和间隙时，也容易出现死锁。

关于 InnoDB 锁的类型和兼容性，可参考 https://blog.csdn.net/lamp_yang_3533/article/details/129291575

产生死锁的四个条件：

• 互斥条件：一个资源每次只能被一个进程（线程）使用。
• 请求与保持条件：一个进程（线程）因请求资源而阻塞时，对已获得的资源保持不放。
• 不剥夺条件：进程（线程）已获得的资源，在末使用完之前，不能被剥夺。
• 循环等待条件：多个进程（线程）之间形成一种头尾相接的循环等待的资源争用关系。

死锁检测功能 innodb_deadlock_detect 默认是开启的。当出现死锁时，InnoDB 就会检测到状态，并及时回滚导致死锁的多个事务中的一个。

mysql> show variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set, 1 warning (0.00 sec)

如果关闭了死锁检测功能，InnoDB 就依赖锁等待超时时间 innodb_lock_wait_timeout，当锁等待超时（默认 50 秒）后，对事务进行回滚。

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)

说明： 以下示例基于 MySQL 8.0，如果是之前的版本会有所不同。

示例 1

CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户名称',
  `department_id` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '用户所属的部门id',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '用户的状态: 1启用, 2禁用',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `department_id_index` (`department_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

# 插入部分测试数据
INSERT t (`id`, `name`, `department_id`, `status`) VALUES 
('1', 'test', '8', '1'),
('2', 'test', '1', '1'),
('3', 'test', '8', '1'),
('7', 'test', '2', '1'),
('8', 'test', '2', '1'),
('9', 'test', '8', '1');

首先，在第一个连接中，开启事务 A。

# 事务 A
BEGIN;
UPDATE t SET department_id=3 WHERE id BETWEEN 3 AND 7;

由于在 MySQL 8.0 中，只要持有锁（不管是否出现锁争用），locks 表中就会记录数据。

故可以先简单看一下事务中锁的信息。

SELECT 
	trx_mysql_thread_id AS sql_thread_id,trx_id, OBJECT_NAME as `table`, 
	INDEX_NAME as `index`, LOCK_DATA as data, LOCK_MODE as mode, 
	LOCK_STATUS as status, LOCK_TYPE as type 
FROM performance_schema.data_locks as lk
LEFT JOIN information_schema.innodb_trx as trx ON trx.trx_id=lk.ENGINE_TRANSACTION_ID;

+---------------+--------+-------+---------+------+------+---------+--------+
| sql_thread_id | trx_id | table | index   | data | mode | status  | type   |
+---------------+--------+-------+---------+------+------+---------+--------+
|            30 | 28220  | t     | NULL    | NULL | IX   | GRANTED | TABLE  |
|            30 | 28220  | t     | PRIMARY | 3    | X    | GRANTED | RECORD |
|            30 | 28220  | t