MySQL死锁分析
2021/6/25 19:27:07
本文主要是介绍MySQL死锁分析,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
一、实验复现
MySQL版本8.0,隔离级别RR和RC均能复现。
1.创建表,构造数据
CREATE TABLE `t2` ( `a` int NOT NULL, `b` int DEFAULT NULL, `c` int DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci insert into t2 values(1,1,1);
2.操作步骤
time | session1 | session2 | session3 |
---|---|---|---|
t1 | begin;update t2 set c=2 where b=1; | ||
t2 | begin;delete from t2 where a=1; | ||
t3 | begin;delete from t2 where b=1; | ||
t4 | rollback; | ||
t5 | Query OK, 1 row affected | (1213, 'Deadlock found when trying to get lock; try restarting transaction') |
二、死锁分析
t1时刻
MySQL root@127.0.0.1:performance_schema> select ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_S -> TATUS,LOCK_DATA from performance_schema.data_locks; +-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | 6689 | 68 | test | t2 | <null> | TABLE | IX | GRANTED | <null> | | 6689 | 68 | test | t2 | b | RECORD | X,REC_NOT_GAP | GRANTED | 1, 1 | | 6689 | 68 | test | t2 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 | +-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
t2时刻
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | 6690 | 67 | test | t2 | <null> | TABLE | IX | GRANTED | <null> | | 6690 | 67 | test | t2 | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 1 | | 6689 | 68 | test | t2 | <null> | TABLE | IX | GRANTED | <null> | | 6689 | 68 | test | t2 | b | RECORD | X,REC_NOT_GAP | GRANTED | 1, 1 | | 6689 | 68 | test | t2 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 | +-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
t3时刻
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | 6691 | 66 | test | t2 | <null> | TABLE | IX | GRANTED | <null> | | 6691 | 66 | test | t2 | b | RECORD | X,REC_NOT_GAP | WAITING | 1, 1 | | 6690 | 67 | test | t2 | <null> | TABLE | IX | GRANTED | <null> | | 6690 | 67 | test | t2 | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 1 | | 6689 | 68 | test | t2 | <null> | TABLE | IX | GRANTED | <null> | | 6689 | 68 | test | t2 | b | RECORD | X,REC_NOT_GAP | GRANTED | 1, 1 | | 6689 | 68 | test | t2 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 | +-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
t4时刻
session1事务回滚
t5时刻
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | 6690 | 67 | test | t2 | <null> | TABLE | IX | GRANTED | <null> | | 6690 | 67 | test | t2 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 | | 6690 | 67 | test | t2 | b | RECORD | X,REC_NOT_GAP | GRANTED | 1, 1 | +-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
show engine innodb status\G
死锁信息
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-06-25 13:18:39 0x7f7dd6fd0700 *** (1) TRANSACTION: TRANSACTION 6691, ACTIVE 84 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 27, OS thread handle 140179212433152, query id 147 127.0.0.1 root updating delete from t2 where b=1 *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 4 page no 5 n bits 72 index b of table `test`.`t2` trx id 6691 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 4; hex 80000001; asc ;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `test`.`t2` trx id 6691 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000001a22; asc ";; 2: len 7; hex 020000011e0151; asc Q;; 3: len 4; hex 80000001; asc ;; 4: len 4; hex 80000001; asc ;; *** (2) TRANSACTION: TRANSACTION 6690, ACTIVE 111 sec updating or deleting 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 28, OS thread handle 140179212105472, query id 146 127.0.0.1 root updating delete from t2 where a=1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `test`.`t2` trx id 6690 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000001a22; asc ";; 2: len 7; hex 020000011e0151; asc Q;; 3: len 4; hex 80000001; asc ;; 4: len 4; hex 80000001; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 4 page no 5 n bits 72 index b of table `test`.`t2` trx id 6690 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 4; hex 80000001; asc ;; *** WE ROLL BACK TRANSACTION (1)
4、总结
这篇关于MySQL死锁分析的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-09-21MySQL集群部署资料:新手入门教程
- 2024-09-21MySQL集群资料:初学者入门指南
- 2024-09-21部署MySQL集群资料:新手入门教程
- 2024-09-20MySQL集群部署教程:新手入门指南
- 2024-09-20MySQL集群教程:初学者必备指南
- 2024-09-20部署MySQL集群项目实战:新手入门教程
- 2024-09-20如何部署MySQL集群:简单教程
- 2024-09-20MySQL集群部署:新手入门指南
- 2024-09-20部署MySQL集群学习:入门指南
- 2024-09-20部署MySQL集群入门教程