环境
- MySQL: 8.0.19
- Isolation Level： Repeatable Read
死锁分析需要开启的设置
- 查看如下三个系统变量是否被设置为ON
show variables like 'innodb_status_output';
show variables like 'innodb_status_output_locks';
show variables like 'innodb_print_all_deadlocks';
- 将值非'ON'的变量修改为'ON'
set global innodb_status_output = 'ON';
set global innodb_status_output_locks = 'ON';
set global innodb_print_all_deadlocks = 'ON';
表结构
这里对表结构做了脱敏，只把关键几个字段放了进来
CREATE TABLE `tb_demo1_appointment`
(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`title` varchar(256) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '---',
`appt_type` tinyint(4) DEFAULT NULL COMMENT '---',
`appt_status` tinyint(4) DEFAULT NULL COMMENT '---',
`appt_space_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '---',
`actual_start_time` bigint(20) DEFAULT NULL COMMENT '---',
`actual_end_time` bigint(20) DEFAULT NULL COMMENT '---',
`end_time` bigint(20) NOT NULL COMMENT '---',
`ts_meeting_id` varchar(256) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '---',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_end_time` (`end_time`) USING BTREE
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_bin;
导致出现死锁的SQL
-- 事务A
-- DEAD_LOCK_SQL_1
UPDATE tb_demo1_appointment SET actual_end_time = 1607491862000, appt_status = 1 WHERE id = 6;
-- DEAD_LOCK_SQL_2
UPDATE tb_demo1_appointment SET end_time = 1607491862000 WHERE is_deleted = 0 AND ts_meeting_id = '0b0fa4f8-3953-11eb-a83b-0242ac120002';
-- 事务B
-- DEAD_LOCK_SQL_3
UPDATE tb_demo1_appointment SET actual_end_time = 1607491862000 , appt_status = 1 WHERE id = 13;
-- DEAD_LOCK_SQL_4
UPDATE tb_demo1_appointment SET end_time = 1607491862000 WHERE is_deleted = 0 AND ts_meeting_id = '2db4db92-3953-11eb-a83b-0242ac120002';
执行顺序：
- DEAD_LOCK_SQL_1
- DEAD_LOCK_SQL_3
- DEAD_LOCK_SQL_2
- DEAD_LOCK_SQL_4
情景复现
开启事务A，执行DEAD_LOCK_SQL_1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE tb_demo1_appointment SET actual_end_time = 1607491862000, appt_status = 1 WHERE id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
我们使用下面的语句查看一下锁状态
-- MySQL5时，锁信息会存放在information_schema.INNODB_LOCKS表中，MySQL8之后存放在performance_schema.data_locks表中
SELECT * FROM performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139948125561144:1113:139948019752584 | 25396 | 1635 | 57 | demo | tb_demo1_appointment | NULL | NULL | NULL | 139948019752584 | TABLE | IX | GRANTED | NULL |
| INNODB | 139948125561144:52:4:7:139948019749704 | 25396 | 1635 | 57 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019749704 | RECORD | X,REC_NOT_GAP | GRANTED | 6 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
performance_schema.data_locks表字段
- ENGINE：持有或请求锁定的存储引擎
- ENGINE_LOCK_ID：存储引擎持有或请求的锁的ID，锁ID格式是内部的，随时可能更改。
- ENGINE_TRANSACTION_ID：请求锁定的事务存储引擎内部ID，可以将其视为锁的所有者
- THREAD_ID：对应事务的线程ID，如果需要获取更详细的信息，需要关联threads表的THREAD_ID
- EVENT_ID：指明造成锁的EVENT_ID，THREAD_ID+EVENT_ID对应parent EVENT，可以在以下几张表内获得信息
- events_waits_xx表查看等待事件
- events_stages_xxx查看到了哪个阶段
- events_statements_xx表查看对应的SQL语句
- events_transactions_current对应查看事务信息
- OBJECT_SCHEMA：对应锁表的schema名称
- OBJECT_NAME：对应锁的表名
- PARTITION_NAME：对应锁的分区名
- SUBPARTITION_NAME：对应锁的子分区名
- INDEX_NAME：锁对应的索引名称，InnoDB表不会为NULL
- OBJECT_INSTANCE_BEGIN：锁对应的内存地址
- LOCK_TYPE：对应的锁类型，对InnoDB而言，可为表锁或者行锁
- LOCK_MODE：锁模式，对应值可能为S[,GAP], X[, GAP], IS[,GAP], IX[,GAP], AUTO_INC和UNKNOWN
- LOCK_STATUS：锁状态，可能为GRANTED或者WAITING
- LOCK_DATA：锁对应的数据，例如如果锁定的是主键，那么该列对应的就是加锁的主键值
事务A(ID:25396)为表demo.tb_demo1_appointment加了IX锁，然后为记录(PRIMARY KEY:6)加上了行记录锁X
开启事务B，执行DEAD_LOCK_SQL_3
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139948125562016:1113:139948019758456 | 25397 | 1636 | 61 | demo | tb_demo1_appointment | NULL | NULL | NULL | 139948019758456 | TABLE | IX | GRANTED | NULL |
| INNODB | 139948125562016:52:4:14:139948019755576 | 25397 | 1636 | 61 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019755576 | RECORD | X,REC_NOT_GAP | GRANTED | 13 |
| INNODB | 139948125561144:1113:139948019752584 | 25396 | 1635 | 57 | demo | tb_demo1_appointment | NULL | NULL | NULL | 139948019752584 | TABLE | IX | GRANTED | NULL |
| INNODB | 139948125561144:52:4:7:139948019749704 | 25396 | 1635 | 57 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019749704 | RECORD | X,REC_NOT_GAP | GRANTED | 6 |
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
事务B(ID:25397)为表demo.tb_demo1_appointment加了IX锁，然后为记录(PRIMARY KEY:13)加上了行记录锁X
- 查看当前表中锁的等待情况
-- MySQL5在information_schema.INNODB_LOCK_WAITS表中
mysql> select * from performance_schema.data_lock_waits;
Empty set (0.00 sec)
发现当前并没有任何事务在等待锁
事务A(ID:25396)执行DEAD_LOCK_SQL_2
UPDATE tb_demo1_appointment SET end_time = 1607491862000 WHERE is_deleted = 0 AND ts_meeting_id = '0b0fa4f8-3953-11eb-a83b-0242ac120002';
- 查看data_locks
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139948125562016:1113:139948019758456 | 25397 | 1636 | 61 | demo | tb_demo1_appointment | NULL | NULL | NULL | 139948019758456 | TABLE | IX | GRANTED | NULL |
| INNODB | 139948125562016:52:4:14:139948019755576 | 25397 | 1636 | 61 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019755576 | RECORD | X,REC_NOT_GAP | GRANTED | 13 |
| INNODB | 139948125561144:1113:139948019752584 | 25396 | 1635 | 57 | demo | tb_demo1_appointment | NULL | NULL | NULL | 139948019752584 | TABLE | IX | GRANTED | NULL |
| INNODB | 139948125561144:52:4:7:139948019749704 | 25396 | 1635 | 57 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019749704 | RECORD | X,REC_NOT_GAP | GRANTED | 6 |
| INNODB | 139948125561144:52:4:2:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 1 |
| INNODB | 139948125561144:52:4:3:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 2 |
| INNODB | 139948125561144:52:4:4:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 3 |
| INNODB | 139948125561144:52:4:5:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 4 |
| INNODB | 139948125561144:52:4:6:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 5 |
| INNODB | 139948125561144:52:4:8:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 7 |
| INNODB | 139948125561144:52:4:9:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 8 |
| INNODB | 139948125561144:52:4:10:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 9 |
| INNODB | 139948125561144:52:4:11:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 10 |
| INNODB | 139948125561144:52:4:12:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 11 |
| INNODB | 139948125561144:52:4:13:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 12 |
| INNODB | 139948125561144:52:4:7:139948019750392 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750392 | RECORD | X,GAP | GRANTED | 6 |
| INNODB | 139948125561144:52:4:14:139948019750736 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750736 | RECORD | X | WAITING | 13 |
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
17 rows in set (0.00 sec)
分析:
end_time为普通二级索引，WHERE条件中没有索引存在，于是走了全表扫描，根据INNODB加锁规则，会将整张表都加上X锁，加锁方式为一条一条加
除了加上记录X锁，还会给二级索引对应的列加上GAP锁(RR隔离级别的行为，为了保证不出现幻读)
当事务A(ID:25396)给表中记录一条一条加锁时，发现事务B(ID:25937)已经持有了ID=13的记录的记录X锁，因此这里会等待事务B(ID:25937)释放该记录X锁
- 查看data_lock_waits
mysql> select * from performance_schema.data_lock_waits;
+--------+-----------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+-----------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
| ENGINE | REQUESTING_ENGINE_LOCK_ID | REQUESTING_ENGINE_TRANSACTION_ID | REQUESTING_THREAD_ID | REQUESTING_EVENT_ID | REQUESTING_OBJECT_INSTANCE_BEGIN | BLOCKING_ENGINE_LOCK_ID | BLOCKING_ENGINE_TRANSACTION_ID | BLOCKING_THREAD_ID | BLOCKING_EVENT_ID | BLOCKING_OBJECT_INSTANCE_BEGIN |
+--------+-----------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+-----------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
| INNODB | 139948125561144:52:4:14:139948019750736 | 25396 | 1635 | 58 | 139948019750736 | 139948125562016:52:4:14:139948019755576 | 25397 | 1636 | 61 | 139948019755576 |
+--------+-----------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+-----------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
1 row in set (0.00 sec)
performance_schema.data_lock_waits表字段
- ENGINE：请求的锁的引擎
- REQUESTING_ENGINE_LOCK_ID：请求的锁在存储引擎中的锁ID
- REQUESTING_ENGINE_TRANSACTION_ID：请求锁的事务对应的事务ID
- REQUESTING_THREAD_ID：请求锁的线程ID
- REQUESTING_EVENT_ID：请求锁的EVENT ID
- REQUESTING_OBJECT_INSTANCE_BEGIN：请求的锁的内存地址
- BLOCKING_ENGINE_LOCK_ID：阻塞的锁的ID，对应data_locks表的ENGINE_LOCK_ID列
- BLOCKING_ENGINE_TRANSACTION_ID：锁阻塞的事务ID
- BLOCKING_THREAD_ID：锁阻塞的线程ID
- BLOCKING_EVENT_ID：锁阻塞的EVENT ID
- BLOCKING_OBJECT_INSTANCE_BEGIN：阻塞的锁内存地址
**结果：**事务A(ID:25396)正在等待事务B(ID:25397)持有的记录X锁(139948125562016:52:4:14:139948019755576)
事务B(ID:25397)执行DEAD_LOCK_SQL_4
UPDATE tb_demo1_appointment SET end_time = 1607491862000 WHERE is_deleted = 0 AND ts_meeting_id = '2db4db92-3953-11eb-a83b-0242ac120002';
出现死锁错误，事务B(ID:25397)自动退出
mysql> UPDATE tb_demo1_appointment SET end_time = 1607491862000 WHERE is_deleted = 0 AND ts_meeting_id = '2db4db92-3953-11eb-a83b-0242ac120002';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>
- 再次查看data_locks
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+--------------+-----------------------+-----------+---------------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+--------------+-----------------------+-----------+---------------+-------------+------------------------+
| INNODB | 139948125561144:1113:139948019752584 | 25396 | 1635 | 57 | demo | tb_demo1_appointment | NULL | NULL | NULL | 139948019752584 | TABLE | IX | GRANTED | NULL |
| INNODB | 139948125561144:52:4:7:139948019749704 | 25396 | 1635 | 57 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019749704 | RECORD | X,REC_NOT_GAP | GRANTED | 6 |
| INNODB | 139948125561144:52:4:1:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | supremum pseudo-record |
| INNODB | 139948125561144:52:4:2:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 1 |
| INNODB | 139948125561144:52:4:3:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 2 |
| INNODB | 139948125561144:52:4:4:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 3 |
| INNODB | 139948125561144:52:4:5:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 4 |
| INNODB | 139948125561144:52:4:6:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 5 |
| INNODB | 139948125561144:52:4:8:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 7 |
| INNODB | 139948125561144:52:4:9:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 8 |
| INNODB | 139948125561144:52:4:10:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 9 |
| INNODB | 139948125561144:52:4:11:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 10 |
| INNODB | 139948125561144:52:4:12:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 11 |
| INNODB | 139948125561144:52:4:13:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 12 |
| INNODB | 139948125561144:52:4:15:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 14 |
| INNODB | 139948125561144:52:4:16:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 15 |
| INNODB | 139948125561144:52:4:17:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 16 |
| INNODB | 139948125561144:52:4:18:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 17 |
| INNODB | 139948125561144:52:4:19:139948019750048 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750048 | RECORD | X | GRANTED | 18 |
| INNODB | 139948125561144:52:4:7:139948019750392 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019750392 | RECORD | X,GAP | GRANTED | 6 |
| INNODB | 139948125561144:52:5:2:139948019751424 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | idx_end_time | 139948019751424 | RECORD | X,GAP | GRANTED | 20201208124315, 1 |
| INNODB | 139948125561144:52:5:20:139948019751424 | 25396 | 1635 | 58 | demo | tb_demo1_appointment | NULL | NULL | idx_end_time | 139948019751424 | RECORD | X,GAP | GRANTED | 1607491862000, 6 |
| INNODB | 139948125561144:52:4:14:139948019751768 | 25396 | 1635 | 59 | demo | tb_demo1_appointment | NULL | NULL | PRIMARY | 139948019751768 | RECORD | X | GRANTED | 13 |
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+----------------------+----------------+-------------------+--------------+-----------------------+-----------+---------------+-------------+------------------------+
23 rows in set (0.00 sec)
事务A(ID：25936)已经成功获取所有记录的锁了
查看/分析死锁日志
通过如下命令可以查看到最近一次出现死锁的日志，如果死锁太多，这种查看日志的方法就不适合了
SHOW ENGINE INNODB STATUS\G
日志如下：
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-12-08 14:56:53 0x7f4819cf5700
*** (1) TRANSACTION:
TRANSACTION 25396, ACTIVE 3280 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1136, 17 row lock(s), undo log entries 2
MySQL thread id 1596, OS thread handle 139947026831104, query id 30993 localhost root updating
UPDATE tb_demo1_appointment SET end_time = 1607491862000 WHERE is_deleted = 0 AND ts_meeting_id = '0b0fa4f8-3953-11eb-a83b-0242ac120002'
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 52 page no 4 n bits 88 index PRIMARY of table `demo`.`tb_demo1_appointment` trx id 25396 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52 page no 4 n bits 88 index PRIMARY of table `demo`.`tb_demo1_appointment` trx id 25396 lock_mode X waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
*** (2) TRANSACTION:
TRANSACTION 25397, ACTIVE 320 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1597, OS thread handle 139947029190400, query id 30994 localhost root updating
UPDATE tb_demo1_appointment SET end_time = 1607491862000 WHERE is_deleted = 0 AND ts_meeting_id = '2db4db92-3953-11eb-a83b-0242ac120002'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 52 page no 4 n bits 88 index PRIMARY of table `demo`.`tb_demo1_appointment` trx id 25397 lock_mode X locks rec but not gap
Record lock, heap no 14 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52 page no 4 n bits 88 index PRIMARY of table `demo`.`tb_demo1_appointment` trx id 25397 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
*** WE ROLL BACK TRANSACTION (2)
分析
- (1) HOLDS THE LOCK(S)：这里说明事务A当前持有了heap_no[2]的记录的记录X锁
- (1) WAITING FOR THIS LOCK TO BE GRANTED：这里说明事务A正在请求heap_no[14]的记录的记录X锁
- (2) HOLDS THE LOCK(S)：这里说明事务B持有了heap_no[14]的记录的记录X锁
- (2) WAITING FOR THIS LOCK TO BE GRANTED：这里说明事务B正在请求heap_no[2]的记录的记录X锁
很明显，这里必定会出现死锁，复习一下死锁出现的四个必要条件
- 请求和保持：进程起码已经持有了一个资源，同时在请求另外一个资源
- 互斥：一个资源一次只能被一个进程使用
- 环路等待：若干进程之间循环等待互相持有的资源
- 不可剥夺：持有资源的进程在使用完毕之前，资源不会被其他进程强行夺走
这里的资源为heap_no[2]和heap_no[14]的记录
结论
通过以上分析，可以得出导致死锁的原因就是因为在执行DEAD_LOCK_SQL_2和DEAD_LOCK_SQL_4时没有使用索引，从而导致全表所有数据被锁定，最终出现死锁
解决
通过给ts_meeting_id字段建立索引即可解决死锁问题
ALTER TABLE `tb_demo1_appointment` ADD INDEX idx_ts_meeting_id (`ts_meeting_id`)；