=====================================

2020-10-26 12:14:30 7fd2642f5700 INNODB MONITOR OUTPUT
=====================================
...省略...
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-10-26 12:12:03 7fd2846ed700
*** (1) TRANSACTION:
TRANSACTION 1795660514, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3

LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 21829887, OS thread handle 0x7fd28d14a700, query id 178279444 172.21.0.15 username updating
UPDATE tbl_deadlock SET col1= 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6247476) AND (id2 = 74354)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660514 lock_mode X waiting
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00012272; asc   "r;;
 1: len 4; hex 00721f45; asc  r E;;

*** (2) TRANSACTION:
TRANSACTION 1795660513, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
20 lock struct(s), heap size 2936, 40 row lock(s)
MySQL thread id 21905203, OS thread handle 0x7fd2846ed700, query id 178279443 172.21.0.15 username updating
UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6249219) AND (id2 = 74354)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00012272; asc   "r;;
 1: len 4; hex 00721f45; asc  r E;;

Record lock, heap no 430 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00012272; asc   "r;;
 1: len 4; hex 00721fe3; asc  r  ;;

Record lock, heap no 431 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00012272; asc   "r;;
 1: len 4; hex 0072218f; asc  r! ;;


...省略很多 Record lock...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 344554 n bits 120 index `PRIMARY` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 44; compact format; info bits 0
 0: len 4; hex 00722663; asc  r&c;;
...省略无关的两行...
 3: len 4; hex 005f5434; asc  _T4;;
 4: len 4; hex 00012272; asc   "r;;
 ...省略很多行...

*** WE ROLL BACK TRANSACTION (1)
...省略...

*** (1) TRANSACTION:

UPDATE tbl_deadlock SET col1= 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6247476) AND (id2 = 74354)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660514 lock_mode X waiting
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00012272; asc   "r;;    //74354
 1: len 4; hex 00721f45; asc  r E;;     //7479109

如上为事务一信息，当时执行的SQL是一条update语句，状态：等锁中。

信息可以看出来表和索引是index 

 0: len 4; hex 00012272; asc   "r;;    //74354
 1: len 4; hex 00721f45; asc  r E;;     //7479109

因为如上使用的是二级索引id2，所以这里的0是二级索引，1是主键（通常用于回表时找到对应行数据）

该字段占用 4 个字节，通常对应 Java/C 中的 

hex 00012272和hex 00721f45分别对应二级索引值和主键ID值，这里是十六进制，转换为十进制后如下：
hex 00012272转换为十进制为74354
hex 00721f45转换后为7479109

*** (2) TRANSACTION:

UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6249219) AND (id2 = 74354)        //当时正在执行的sql

*** (2) HOLDS THE LOCK(S):        //表示当时事务二持有的锁

RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
//当时事务二持有的表，二级索引，并且为X锁

 0: len 4; hex 00012272; asc   "r;;        //二级索引值id2=74354
 1: len 4; hex 00721f45; asc  r E;;     //主键值转换后为7479109

Record lock, heap no 430 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00012272; asc   "r;;

 1: len 4; hex 00721fe3; asc  r  ;;     //7479267

Record lock, heap no 431 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00012272; asc   "r;;
 1: len 4; hex 0072218f; asc  r! ;;     //7479695


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 344554 n bits 120 index `PRIMARY` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 44; compact format; info bits 0

//等锁，因为这里是根据主键ID所以展示所有的字段。

 0: len 4; hex 00722663; asc  r&c;;      //primary=7480931
...省略无关的两行...
 3: len 4; hex 005f5434; asc  _T4;;
 4: len 4; hex 00012272; asc   "r;;        //id2=74354
 ...省略很多行...

*** WE ROLL BACK TRANSACTION (1)
...省略...

事务一：持有了 PRIMARY 为 7480931 的锁，正在请求二级索引 id2 = 74354

事务二：持有了二级索引 id2 = 74354，主键 ID为 7479109的锁（见日志 (2) HOLDS THE LOCK(S)），正在请求 PRIMARY 索引 ID 7480931 的锁。

事务1——->持有 —->  PRIMARY=7480931；等待———>id2 = 74354，PRIMARY=7479109的锁

事务2——→持有——>PRIMARY=7479109，id2=74354；等待———>PRIMARY=7480931 的锁

在 MySQL 的死锁日志中，事务一持有的锁并不总是被全部列出。这是分析死锁日志时最容易产生困惑的地方。

以下是为什么事务一持有主键 7480931 锁  的  推导：

1. 核心原理：日志的“非对称性”

事务二：因为它是触发死锁检测的时刻正在运行的事务，日志会详细列出它“当前持有的所有锁”（HOLDS THE LOCK(S)）和“正在等待的锁”。

事务一：日志通常只列出它“正在等待的那个锁”（WAITING FOR THIS LOCK TO BE GRANTED），而它已经持有的锁（只要不是导致事务二等待的那个直接原因）往往不会被完整打印出来。

事务二（trx id 1795660513） 正在 WAITING FOR 主键 hex 00722663 (7480931) 的 X 锁。

在死锁关系中，如果事务二在等待这个锁，那么必然有另一个参与死锁的事务已经持有了这个锁。在这个双事务死锁案例中，这个持锁者只能是事务一。

UPDATE tbl_deadlock SET ... WHERE (id1 = 6247476) AND (id2 = 74354)

命中关系：查看事务二等待的记录详情，会发现字段 4: hex 00012272（即 id2 列）的值正是 74354。

执行顺序：这意味着主键为 7480931 的这行数据，完全符合事务一 UPDATE 语句的过滤条件。

锁定机制：InnoDB 在执行 UPDATE 时，必须先获取该行在 PRIMARY 索引上的 X 锁。

*** (1) TRANSACTION:
TRANSACTION 1795660514, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 21829887, OS thread handle 0x7fd28d14a700, query id 178279444 172.21.0.15 username updating

UPDATE tbl_deadlock SET col1= 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6247476) AND (id2 = 74354)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660514 lock_mode X waiting
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00012272; asc   "r;;      //id2=74354
 1: len 4; hex 00721f45; asc  r E;;     //RIMARY=7479109

*** (2) TRANSACTION:
TRANSACTION 1795660513, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
20 lock struct(s), heap size 2936, 40 row lock(s)
MySQL thread id 21905203, OS thread handle 0x7fd2846ed700, query id 178279443 172.21.0.15 username updating

UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6249219) AND (id2 = 74354)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00012272; asc   "r;;    //id2=74354
 1: len 4; hex 00721f45; asc  r E;;     //PRIMARY=7479109

Record lock, heap no 430 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00012272; asc   "r;;
 1: len 4; hex 00721fe3; asc  r  ;;     //RIMARY=7479267

Record lock, heap no 431 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00012272; asc   "r;;
 1: len 4; hex 0072218f; asc  r! ;;     //RIMARY=7479695

...省略很多 Record lock...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 344554 n bits 120 index `PRIMARY` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 44; compact format; info bits 0
 0: len 4; hex 00722663; asc  r&c;;      //PRIMARY=7480931
...省略无关的两行...
 3: len 4; hex 005f5434; asc  _T4;;
 4: len 4; hex 00012272; asc   "r;;     //id2=74354
 ...省略很多行...

*** WE ROLL BACK TRANSACTION (1)

由于(now)没有死锁日志拿来分析，此文引用wenan大佬

https://cloud.tencent.com/developer/article/1730535

信息可以看出来表和索引是index id2 of table deadlock.tbl_deadlock

死锁分析（个人总结）

后端

数据库

安全

MySQL死锁分析：事务1持有主键7480931锁并等待id2=74354锁，事务2持有id2=74354锁并等待主键7480931锁，形成循环等待导致死锁。通过分析死锁日志可定位并发冲突点，优化SQL和索引设计避免死锁发生。

MySQL

Java

腾讯云数据库特惠，新用户首单0.5折起


文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

死锁分析（个人总结）

死锁分析（个人总结）

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐