MySQL 死锁分析与解决实战

前言

💡 痛点：并发写入时突然报死锁错误？业务日志里频繁出现 Deadlock found when trying to get lock？不知道如何定位是哪两条 SQL 导致的？

🎯 解决方案：掌握 MySQL 死锁分析 — 从日志定位死锁、了解 InnoDB 锁机制、编写死锁安全的代码。

MySQL 死锁排查流程图：

#mermaid-svg-6CakMS1hxe40vqp0{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-6CakMS1hxe40vqp0 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-6CakMS1hxe40vqp0 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-6CakMS1hxe40vqp0 .error-icon{fill:#552222;}#mermaid-svg-6CakMS1hxe40vqp0 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-6CakMS1hxe40vqp0 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-6CakMS1hxe40vqp0 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-6CakMS1hxe40vqp0 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-6CakMS1hxe40vqp0 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-6CakMS1hxe40vqp0 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-6CakMS1hxe40vqp0 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-6CakMS1hxe40vqp0 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-6CakMS1hxe40vqp0 .marker.cross{stroke:#333333;}#mermaid-svg-6CakMS1hxe40vqp0 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-6CakMS1hxe40vqp0 p{margin:0;}#mermaid-svg-6CakMS1hxe40vqp0 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-6CakMS1hxe40vqp0 .cluster-label text{fill:#333;}#mermaid-svg-6CakMS1hxe40vqp0 .cluster-label span{color:#333;}#mermaid-svg-6CakMS1hxe40vqp0 .cluster-label span p{background-color:transparent;}#mermaid-svg-6CakMS1hxe40vqp0 .label text,#mermaid-svg-6CakMS1hxe40vqp0 span{fill:#333;color:#333;}#mermaid-svg-6CakMS1hxe40vqp0 .node rect,#mermaid-svg-6CakMS1hxe40vqp0 .node circle,#mermaid-svg-6CakMS1hxe40vqp0 .node ellipse,#mermaid-svg-6CakMS1hxe40vqp0 .node polygon,#mermaid-svg-6CakMS1hxe40vqp0 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-6CakMS1hxe40vqp0 .rough-node .label text,#mermaid-svg-6CakMS1hxe40vqp0 .node .label text,#mermaid-svg-6CakMS1hxe40vqp0 .image-shape .label,#mermaid-svg-6CakMS1hxe40vqp0 .icon-shape .label{text-anchor:middle;}#mermaid-svg-6CakMS1hxe40vqp0 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-6CakMS1hxe40vqp0 .rough-node .label,#mermaid-svg-6CakMS1hxe40vqp0 .node .label,#mermaid-svg-6CakMS1hxe40vqp0 .image-shape .label,#mermaid-svg-6CakMS1hxe40vqp0 .icon-shape .label{text-align:center;}#mermaid-svg-6CakMS1hxe40vqp0 .node.clickable{cursor:pointer;}#mermaid-svg-6CakMS1hxe40vqp0 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-6CakMS1hxe40vqp0 .arrowheadPath{fill:#333333;}#mermaid-svg-6CakMS1hxe40vqp0 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-6CakMS1hxe40vqp0 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-6CakMS1hxe40vqp0 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-6CakMS1hxe40vqp0 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-6CakMS1hxe40vqp0 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-6CakMS1hxe40vqp0 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-6CakMS1hxe40vqp0 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-6CakMS1hxe40vqp0 .cluster text{fill:#333;}#mermaid-svg-6CakMS1hxe40vqp0 .cluster span{color:#333;}#mermaid-svg-6CakMS1hxe40vqp0 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-6CakMS1hxe40vqp0 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-6CakMS1hxe40vqp0 rect.text{fill:none;stroke-width:0;}#mermaid-svg-6CakMS1hxe40vqp0 .icon-shape,#mermaid-svg-6CakMS1hxe40vqp0 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-6CakMS1hxe40vqp0 .icon-shape p,#mermaid-svg-6CakMS1hxe40vqp0 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-6CakMS1hxe40vqp0 .icon-shape .label rect,#mermaid-svg-6CakMS1hxe40vqp0 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-6CakMS1hxe40vqp0 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-6CakMS1hxe40vqp0 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-6CakMS1hxe40vqp0 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

死锁发生

查看错误日志

分析死锁信息

定位相关表和索引

分析 SQL 执行顺序

定位锁冲突点

优化业务逻辑

避免循环等待

使用低隔离级别

调整索引

死锁 vs 锁等待：

一、InnoDB 锁机制

1.1 锁类型概览

#mermaid-svg-VZQTdSbJzN7VCzmf{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-VZQTdSbJzN7VCzmf .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-VZQTdSbJzN7VCzmf .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-VZQTdSbJzN7VCzmf .error-icon{fill:#552222;}#mermaid-svg-VZQTdSbJzN7VCzmf .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-VZQTdSbJzN7VCzmf .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-VZQTdSbJzN7VCzmf .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-VZQTdSbJzN7VCzmf .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-VZQTdSbJzN7VCzmf .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-VZQTdSbJzN7VCzmf .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-VZQTdSbJzN7VCzmf .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-VZQTdSbJzN7VCzmf .marker{fill:#333333;stroke:#333333;}#mermaid-svg-VZQTdSbJzN7VCzmf .marker.cross{stroke:#333333;}#mermaid-svg-VZQTdSbJzN7VCzmf svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-VZQTdSbJzN7VCzmf p{margin:0;}#mermaid-svg-VZQTdSbJzN7VCzmf .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-VZQTdSbJzN7VCzmf .cluster-label text{fill:#333;}#mermaid-svg-VZQTdSbJzN7VCzmf .cluster-label span{color:#333;}#mermaid-svg-VZQTdSbJzN7VCzmf .cluster-label span p{background-color:transparent;}#mermaid-svg-VZQTdSbJzN7VCzmf .label text,#mermaid-svg-VZQTdSbJzN7VCzmf span{fill:#333;color:#333;}#mermaid-svg-VZQTdSbJzN7VCzmf .node rect,#mermaid-svg-VZQTdSbJzN7VCzmf .node circle,#mermaid-svg-VZQTdSbJzN7VCzmf .node ellipse,#mermaid-svg-VZQTdSbJzN7VCzmf .node polygon,#mermaid-svg-VZQTdSbJzN7VCzmf .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-VZQTdSbJzN7VCzmf .rough-node .label text,#mermaid-svg-VZQTdSbJzN7VCzmf .node .label text,#mermaid-svg-VZQTdSbJzN7VCzmf .image-shape .label,#mermaid-svg-VZQTdSbJzN7VCzmf .icon-shape .label{text-anchor:middle;}#mermaid-svg-VZQTdSbJzN7VCzmf .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-VZQTdSbJzN7VCzmf .rough-node .label,#mermaid-svg-VZQTdSbJzN7VCzmf .node .label,#mermaid-svg-VZQTdSbJzN7VCzmf .image-shape .label,#mermaid-svg-VZQTdSbJzN7VCzmf .icon-shape .label{text-align:center;}#mermaid-svg-VZQTdSbJzN7VCzmf .node.clickable{cursor:pointer;}#mermaid-svg-VZQTdSbJzN7VCzmf .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-VZQTdSbJzN7VCzmf .arrowheadPath{fill:#333333;}#mermaid-svg-VZQTdSbJzN7VCzmf .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-VZQTdSbJzN7VCzmf .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-VZQTdSbJzN7VCzmf .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-VZQTdSbJzN7VCzmf .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-VZQTdSbJzN7VCzmf .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-VZQTdSbJzN7VCzmf .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-VZQTdSbJzN7VCzmf .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-VZQTdSbJzN7VCzmf .cluster text{fill:#333;}#mermaid-svg-VZQTdSbJzN7VCzmf .cluster span{color:#333;}#mermaid-svg-VZQTdSbJzN7VCzmf div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-VZQTdSbJzN7VCzmf .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-VZQTdSbJzN7VCzmf rect.text{fill:none;stroke-width:0;}#mermaid-svg-VZQTdSbJzN7VCzmf .icon-shape,#mermaid-svg-VZQTdSbJzN7VCzmf .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-VZQTdSbJzN7VCzmf .icon-shape p,#mermaid-svg-VZQTdSbJzN7VCzmf .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-VZQTdSbJzN7VCzmf .icon-shape .label rect,#mermaid-svg-VZQTdSbJzN7VCzmf .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-VZQTdSbJzN7VCzmf .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-VZQTdSbJzN7VCzmf .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-VZQTdSbJzN7VCzmf :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

InnoDB 锁

共享锁 S

排他锁 X

意向锁

记录锁

间隙锁

临键锁

插入意向锁

允许其他事务读取

不允许写操作

允许一个事务写

不允许其他读写

IS 意向共享

IX 意向排他

1.2 锁的兼容性矩阵

1.3 锁模式详解

— ===== 共享锁 vs 排他锁 =====

— 共享锁（S锁）：允许其他事务读取，但不允许写
SELECT * FROM orders WHERE id = 1 LOCK IN SHARE MODE;

— 排他锁（X锁）：不允许任何其他事务读写
SELECT * FROM orders WHERE id = 1 FOR UPDATE;

— INSERT/UPDATE/DELETE 自动加排他锁
UPDATE orders SET status = 'completed' WHERE id = 1;
DELETE FROM orders WHERE id = 1;
INSERT INTO orders (order_no, amount) VALUES ('ORD001', 100);

— ===== 记录锁（Record Lock）=====
— 锁定索引记录，非锁定整行
— 即使 WHERE 条件没有命中索引，也会锁定所有扫描过的行

— ===== 间隙锁（Gap Lock）=====
— 锁定索引之间的间隙，防止幻读
— RR 隔离级别下生效

— ===== 临键锁（Next-Key Lock）=====
— 记录锁 + 间隙锁的组合
— InnoDB 默认的锁算法

— ===== 插入意向锁（Insert Intention Lock）=====
— 插入操作在等待间隙释放时获取

二、死锁日志分析

2.1 开启死锁日志

— ===== 死锁日志配置 =====

— 查看当前配置
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

— 开启死锁日志（会输出到 error log）
SET GLOBAL innodb_print_all_deadlocks = ON;

— 查看死锁超时配置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
— 默认 50 秒

— 设置死锁超时（会话级）
SET innodb_lock_wait_timeout = 10;

— 查看当前锁等待
SHOW ENGINE INNODB STATUS;

— 查看锁信息
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_TRX;

2.2 死锁日志解读

— ===== 创建测试数据 =====

CREATE TABLE accounts (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
balance DECIMAL(12,2) NOT NULL DEFAULT 0,
version INT NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO accounts (user_id, balance) VALUES
(1, 1000),
(2, 2000),
(3, 3000);

— ===== 模拟死锁场景 =====

— 事务 A
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; — 锁定 id=1

— 事务 B
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; — 锁定 id=2

— 事务 A 尝试锁定 id=2（等待）
UPDATE accounts SET balance = balance – 100 WHERE id = 2;

— 事务 B 尝试锁定 id=1（死锁！）
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

— 死锁日志示例：
/*
========================
LATEST DETECTED DEADLOCK
————————
2024–01–15 10:30:45 0x7f8a12345678
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
LOCK HTABLE NO 123
—SQL—
UPDATE accounts SET balance = balance + 100 WHERE id = 1

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 3 n bits 72 index PRIMARY of table `test`.`accounts`
lock_mode X locks rec but not gap
Record lock: hrs=1, heap size 1136

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 3 n bits 72 index PRIMARY of table `test`.`accounts`
lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 5 sec
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
—SQL—
UPDATE accounts SET balance = balance – 100 WHERE id = 2

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 3 n bits 72 index PRIMARY of table `test`.`accounts`
lock_mode X locks rec but not gap
Record lock: hrs=2, heap size 1136

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 3 n bits 72 index PRIMARY of table `test`.`accounts`
lock_mode X locks rec but not gap waiting

2.3 死锁日志字段解读

— ===== 死锁日志字段详解 =====

— TRANSACTION: 事务信息
— – TRANSACTION ID: 事务 ID（system no）
— – ACTIVE: 活跃时间
— – thread id: 线程 ID

— LOCK WAIT: 锁等待信息
— – lock struct(s): 锁结构数量
— – heap size: 堆大小
— – row lock(s): 行锁数量

— HOLDS THE LOCK(S): 持有的锁
— – lock_mode: 锁模式（X/S, locks rec but not gap/gap/next-key）
— – lock_type: 锁类型（RECORD/BUFFER）
— – space id: 表空间 ID
— – page no: 页号
— – n bits: 位图大小

— WAITING FOR THIS LOCK TO BE GRANTED: 等待的锁
— – waiting: 表示正在等待

— SQL: 正在执行的 SQL

三、常见死锁场景

3.1 场景一：双向更新死锁

#mermaid-svg-R22gMFpTgtZqnw4p{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-R22gMFpTgtZqnw4p .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-R22gMFpTgtZqnw4p .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-R22gMFpTgtZqnw4p .error-icon{fill:#552222;}#mermaid-svg-R22gMFpTgtZqnw4p .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-R22gMFpTgtZqnw4p .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-R22gMFpTgtZqnw4p .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-R22gMFpTgtZqnw4p .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-R22gMFpTgtZqnw4p .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-R22gMFpTgtZqnw4p .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-R22gMFpTgtZqnw4p .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-R22gMFpTgtZqnw4p .marker{fill:#333333;stroke:#333333;}#mermaid-svg-R22gMFpTgtZqnw4p .marker.cross{stroke:#333333;}#mermaid-svg-R22gMFpTgtZqnw4p svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-R22gMFpTgtZqnw4p p{margin:0;}#mermaid-svg-R22gMFpTgtZqnw4p .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-R22gMFpTgtZqnw4p .cluster-label text{fill:#333;}#mermaid-svg-R22gMFpTgtZqnw4p .cluster-label span{color:#333;}#mermaid-svg-R22gMFpTgtZqnw4p .cluster-label span p{background-color:transparent;}#mermaid-svg-R22gMFpTgtZqnw4p .label text,#mermaid-svg-R22gMFpTgtZqnw4p span{fill:#333;color:#333;}#mermaid-svg-R22gMFpTgtZqnw4p .node rect,#mermaid-svg-R22gMFpTgtZqnw4p .node circle,#mermaid-svg-R22gMFpTgtZqnw4p .node ellipse,#mermaid-svg-R22gMFpTgtZqnw4p .node polygon,#mermaid-svg-R22gMFpTgtZqnw4p .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-R22gMFpTgtZqnw4p .rough-node .label text,#mermaid-svg-R22gMFpTgtZqnw4p .node .label text,#mermaid-svg-R22gMFpTgtZqnw4p .image-shape .label,#mermaid-svg-R22gMFpTgtZqnw4p .icon-shape .label{text-anchor:middle;}#mermaid-svg-R22gMFpTgtZqnw4p .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-R22gMFpTgtZqnw4p .rough-node .label,#mermaid-svg-R22gMFpTgtZqnw4p .node .label,#mermaid-svg-R22gMFpTgtZqnw4p .image-shape .label,#mermaid-svg-R22gMFpTgtZqnw4p .icon-shape .label{text-align:center;}#mermaid-svg-R22gMFpTgtZqnw4p .node.clickable{cursor:pointer;}#mermaid-svg-R22gMFpTgtZqnw4p .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-R22gMFpTgtZqnw4p .arrowheadPath{fill:#333333;}#mermaid-svg-R22gMFpTgtZqnw4p .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-R22gMFpTgtZqnw4p .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-R22gMFpTgtZqnw4p .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-R22gMFpTgtZqnw4p .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-R22gMFpTgtZqnw4p .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-R22gMFpTgtZqnw4p .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-R22gMFpTgtZqnw4p .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-R22gMFpTgtZqnw4p .cluster text{fill:#333;}#mermaid-svg-R22gMFpTgtZqnw4p .cluster span{color:#333;}#mermaid-svg-R22gMFpTgtZqnw4p div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-R22gMFpTgtZqnw4p .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-R22gMFpTgtZqnw4p rect.text{fill:none;stroke-width:0;}#mermaid-svg-R22gMFpTgtZqnw4p .icon-shape,#mermaid-svg-R22gMFpTgtZqnw4p .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-R22gMFpTgtZqnw4p .icon-shape p,#mermaid-svg-R22gMFpTgtZqnw4p .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-R22gMFpTgtZqnw4p .icon-shape .label rect,#mermaid-svg-R22gMFpTgtZqnw4p .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-R22gMFpTgtZqnw4p .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-R22gMFpTgtZqnw4p .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-R22gMFpTgtZqnw4p :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

事务 A: UPDATE id=1

锁定 id=1

尝试 UPDATE id=2

等待 id=2

事务 B: UPDATE id=2

锁定 id=2

尝试 UPDATE id=1

死锁!

— ===== 场景一：双向更新死锁 =====

— 死锁 SQL
— 事务 A:
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

— 事务 B:
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 2; — 锁定 id=2
UPDATE accounts SET balance = balance + 100 WHERE id = 1; — 死锁！
COMMIT;

— ===== 解决方案：统一更新顺序 =====

— 事务 A:
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 1; — 先 id=1
UPDATE accounts SET balance = balance + 100 WHERE id = 2; — 后 id=2
COMMIT;

— 事务 B:
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 1; — 同样先 id=1
UPDATE accounts SET balance = balance + 100 WHERE id = 2; — 同样后 id=2
COMMIT;

3.2 场景二：索引导致的死锁

— ===== 场景二：索引导致的死锁 =====

— 创建带索引的表
CREATE TABLE order_items (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
INDEX idx_order_id (order_id),
INDEX idx_product_id (product_id),
INDEX idx_order_product (order_id, product_id)
) ENGINE=InnoDB;

— 死锁场景：不同索引导致不同的锁顺序
— 事务 A:
BEGIN;
— 使用 idx_order_id 索引锁定订单
UPDATE order_items SET quantity = 10 WHERE order_id = 100;

— 事务 B:
BEGIN;
— 使用 idx_product_id 索引锁定商品
UPDATE order_items SET price = 99.9 WHERE product_id = 200;

— 事务 A: 尝试更新 product_id=200 的记录
UPDATE order_items SET quantity = 20 WHERE product_id = 200;

— 事务 B: 尝试更新 order_id=100 的记录
UPDATE order_items SET quantity = 30 WHERE order_id = 100;
— 死锁！

— ===== 解决方案：删除冗余索引 =====
ALTER TABLE order_items DROP INDEX idx_order_id;
ALTER TABLE order_items DROP INDEX idx_product_id;
— 只保留复合索引 idx_order_product

3.3 场景三：主键插入死锁

— ===== 场景三：Gap Lock 导致的插入死锁 =====

— 事务 A:
BEGIN;
— 锁定一个范围（等待插入）
SELECT * FROM accounts WHERE id > 100 FOR UPDATE;

— 事务 B:
BEGIN;
— 尝试在相同范围插入（也需要 gap lock）
INSERT INTO accounts (id, user_id, balance) VALUES (101, 1001, 500);
— 被阻塞

— 事务 A: 尝试在相同范围插入
INSERT INTO accounts (id, user_id, balance) VALUES (102, 1002, 600);
— 死锁！

— ===== 解决方案：使用低隔离级别 =====
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
— 或使用 ROW 模式 binlog
SET SESSION binlog_format = 'ROW';

3.4 场景四：外键死锁

— ===== 场景四：外键索引导致的死锁 =====

CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL UNIQUE,
customer_id BIGINT NOT NULL,
total_amount DECIMAL(12,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_customer_id (customer_id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

— 死锁场景
— 事务 A: 删除订单
BEGIN;
DELETE FROM orders WHERE id = 1;
— InnoDB 会在子表加锁

— 事务 B: 在子表插入记录
BEGIN;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 100, 2);
— 被阻塞

— 事务 A: 在子表插入记录（不同 order_id）
INSERT INTO order_items (order_id, product_id, quantity) VALUES (2, 101, 3);
— 死锁！

— ===== 解决方案：先操作子表，再操作主表 =====
— 事务 A:
BEGIN;
DELETE FROM order_items WHERE order_id = 1;
DELETE FROM orders WHERE id = 1;
COMMIT;

— 或禁用外键检查（不推荐生产环境）
SET foreign_key_checks = OFF;
— 操作完成后重新开启
SET foreign_key_checks = ON;

3.5 场景五：间隙锁死锁

— ===== 场景五：范围更新导致的间隙锁死锁 =====

— 事务 A:
BEGIN;
— 锁定 id 在 1-100 范围内的所有记录
UPDATE accounts SET balance = balance + 100 WHERE id BETWEEN 1 AND 100;

— 事务 B:
BEGIN;
— 锁定 id 在 50-150 范围内的所有记录
UPDATE accounts SET balance = balance – 100 WHERE id BETWEEN 50 AND 150;
— 死锁！（间隙重叠）

— ===== 解决方案一：使用主键范围 =====
BEGIN;
— 使用精确的主键范围
UPDATE accounts SET balance = balance + 100 WHERE id >= 1 AND id <= 100;
COMMIT;

BEGIN;
— 同样使用精确范围
UPDATE accounts SET balance = balance – 100 WHERE id >= 50 AND id <= 150;
COMMIT;

— ===== 解决方案二：使用记录锁替代间隙锁 =====
— 确保查询命中索引且返回确定记录
— 使用 FOR UPDATE SKIP LOCKED（跳过已锁定行）
SELECT * FROM accounts WHERE id >= 1 AND id <= 100 FOR UPDATE SKIP LOCKED;

— 或使用 NOWAIT（立即失败）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

四、死锁诊断工具

4.1 information_schema 查询

— ===== 锁信息查询 =====

— 1. 查看当前所有锁
SELECT
l.lock_id AS lock_id,
l.lock_mode AS lock_mode,
l.lock_type AS lock_type,
l.lock_table AS lock_table,
l.lock_index AS lock_index,
l.lock_space AS lock_space,
l.lock_page AS lock_page,
l.lock_rec AS lock_rec,
p.trx_id,
p.trx_state,
p.trx_started,
p.trx_rows_locked,
p.trx_rows_modified,
p.trx_query AS current_query
FROM information_schema.INNODB_LOCKS l
JOIN information_schema.INNODB_TRX p ON l.lock_trx_id = p.trx_id
ORDER BY p.trx_started;

— 2. 查看锁等待
SELECT
r.requesting_trx_id AS requesting_trx,
r.blocking_trx_id AS blocking_trx,
r.lock_id AS request_lock_id,
r.lock_mode AS request_mode,
r.lock_type AS request_type,
r.lock_table AS request_table,
b.trx_id AS blocking_trx_id,
b.trx_state AS blocking_state,
b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS r
JOIN information_schema.INNODB_TRX b ON r.blocking_trx_id = b.trx_id
ORDER BY r.requesting_trx_id;

— 3. 查看事务详细信息
SELECT
trx_id,
trx_state,
trx_started,
trx_requested_lock_id,
trx_wait_started,
trx_weight,
trx_mysql_thread_id AS thread_id,
trx_query,
trx_rows_locked,
trx_rows_modified,
trx_concurrency_tickets AS concurrency_tickets,
trx_isolation_level,
trx_unique_checks,
trx_foreign_key_checks
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

— 4. 查看锁的等待时间
SELECT
p.trx_mysql_thread_id AS thread_id,
p.trx_state,
TIMESTAMPDIFF(SECOND, p.trx_wait_started, NOW()) AS wait_seconds,
p.trx_query,
l.lock_mode,
l.lock_type,
l.lock_table
FROM information_schema.INNODB_TRX p
JOIN information_schema.INNODB_LOCKS l ON p.trx_id = l.lock_trx_id
WHERE p.trx_state = 'LOCK WAIT';

— 5. 杀掉阻塞的事务
— 先查询阻塞的线程 ID
SELECT
p.trx_mysql_thread_id AS thread_id,
p.trx_query
FROM information_schema.INNODB_TRX p
WHERE EXISTS (
SELECT 1 FROM information_schema.INNODB_LOCK_WAITS w
WHERE w.blocking_trx_id = p.trx_id
);

— 杀掉阻塞线程
— KILL <thread_id>;

4.2 performance_schema 监控

— ===== Performance Schema 锁监控 =====

— 开启锁监控（MySQL 8.0+）
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/lock%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_locks%';

— 查看锁事件
SELECT
EVENT_NAME,
COUNT_STAR AS total_count,
SUM_TIMER_WAIT / 1000000000000 AS total_seconds,
AVG_TIMER_WAIT / 1000000000000 AS avg_seconds,
MAX_TIMER_WAIT / 1000000000000 AS max_seconds
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/lock%'
ORDER BY total_count DESC
LIMIT 20;

— 查看当前锁等待
SELECT
THREAD_ID,
EVENT_ID,
EVENT_NAME,
SOURCE,
TIMER_START,
TIMER_END,
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
OBJECT_TYPE,
LOCK_TYPE,
LOCK_STATUS
FROM performance_schema.events_waits_current
WHERE EVENT_NAME LIKE 'wait/lock%';

— 查看最近的锁事件
SELECT
THREAD_ID,
EVENT_NAME,
OBJECT_SCHEMA,
OBJECT_NAME,
LOCK_TYPE,
LOCK_MODE,
LOCK_STATUS,
LOCK_DATA
FROM performance_schema.data_locks
ORDER BY TIMESTAMP;

— 查看锁依赖链
SELECT
REQUESTING_THREAD_ID,
REQUESTING_ENGINE_TRANSACTION_ID,
REQUESTING_LOCK_ID,
BLOCKING_THREAD_ID,
BLOCKING_ENGINE_TRANSACTION_ID,
BLOCKING_LOCK_ID
FROM performance_schema.data_lock_waits;

4.3 pt-deadlock-logger 工具

# ===== Percona Toolkit 死锁日志工具 =====

# 安装
# yum install percona-toolkit # CentOS/RHEL
# apt-get install percona-toolkit # Debian/Ubuntu

# 使用 pt-deadlock-logger
pt-deadlock-logger \\
–user=root \\
–password=xxx \\
–host=localhost \\
–engine=InnoDB \\
DSN=d=master,t=deadlocks

# 解析死锁日志
pt-deadlock-logger –print \\
–user=root \\
–password=xxx \\
/var/log/mysql/error.log

# 导出死锁历史
pt-deadlock-logger \\
–user=root \\
–password=xxx \\
–create-dbtable \\
–循环监控 \\
h=localhost,D=deadlock_history,t=deadlocks

# 查询死锁历史
SELECT * FROM deadlock_history.deadlocks
ORDER BY ts DESC LIMIT 10;

五、死锁解决策略

5.1 代码层面解决

— ===== 解决策略一：统一操作顺序 =====

— 错误写法（不同事务不同顺序）
— 事务 A: 先操作账户 A，再操作账户 B
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

— 事务 B: 先操作账户 B，再操作账户 A（可能死锁！）
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

— 正确写法（统一按 ID 升序操作）
— 事务 A:
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

— 事务 B:
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 1; — 同样先 id=1
UPDATE accounts SET balance = balance + 100 WHERE id = 2; — 同样后 id=2
COMMIT;

— ===== 解决策略二：减小锁范围 =====

— 错误写法（锁住整个表）
BEGIN;
UPDATE orders SET status = 'shipped' WHERE status = 'paid';
— 锁定大量行，可能超时

— 正确写法（分批处理）
BEGIN;
DECLARE done INT DEFAULT FALSE;
DECLARE cur_id BIGINT;
DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status = 'paid' LIMIT 1000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP
FETCH cur INTO cur_id;
IF done THEN
LEAVE read_loop;
END IF;

UPDATE orders SET status = 'shipped' WHERE id = cur_id;
COMMIT; — 每批提交，释放锁
BEGIN; — 开启新事务
END LOOP;

CLOSE cur;
COMMIT;

— ===== 解决策略三：使用乐观锁 =====

— 添加 version 字段
ALTER TABLE accounts ADD COLUMN version INT NOT NULL DEFAULT 0;

— 乐观锁更新
UPDATE accounts
SET balance = balance – 100, version = version + 1
WHERE id = 1 AND version = 5;

— 检查影响行数
— 如果 affected_rows = 0，说明版本不匹配，尝试重新读取重试

5.2 数据库层面解决

— ===== 解决策略四：调整隔离级别 =====

— 查看当前隔离级别
SELECT @@transaction_isolation;

— 设置为 READ COMMITTED（减少 Gap Lock）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

— 或 READ UNCOMMITTED（极少使用）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

— ===== 解决策略五：优化索引 =====

— 查看查询执行计划
EXPLAIN UPDATE accounts SET balance = balance – 100 WHERE id = 1;

— 检查是否使用索引
SHOW INDEX FROM accounts;

— 添加合适索引减少锁范围
CREATE INDEX idx_accounts_id ON accounts(id);

— ===== 解决策略六：调整锁超时 =====

— 查看当前超时
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

— 设置较短超时（快速失败重试）
SET innodb_lock_wait_timeout = 5;

— 永久配置（my.cnf）
— [mysqld]
— innodb_lock_wait_timeout = 5

— ===== 解决策略七：使用行锁而非表锁 =====

— 确保使用行锁（主键/索引查询）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; — 行锁

— 避免全表扫描（会导致表锁）
SELECT * FROM accounts WHERE balance > 1000 FOR UPDATE; — 可能表锁

5.3 应用层面解决

— ===== 解决策略八：重试机制 =====

— MyBatis 死锁重试示例
@Retryable(
value = {DeadlockTimeoutException.class, ConcurrentUpdateException.class},
maxAttempts = 3,
backoff = @Backoff(delay = 100, multiplier = 2)
)
public void updateAccount(Long id, BigDecimal amount) {
jdbcTemplate.update(
"UPDATE accounts SET balance = balance + ? WHERE id = ?",
amount, id
);
}

— Spring Retry 配置
@Configuration
@EnableRetry
public class RetryConfig {
@Bean
public RetryTemplate retryTemplate() {
ExponentialBackOffPolicy policy = new ExponentialBackOffPolicy();
policy.setInitialInterval(100);
policy.setMultiplier(2.0);
policy.setMaxInterval(3000);

SimpleRetryPolicy retryPolicy = new SimpleRetryPolicy();
retryPolicy.setMaxAttempts(3);

RetryTemplate template = new RetryTemplate();
template.setBackOffPolicy(policy);
template.setRetryPolicy(retryPolicy);

return template;
}
}

— ===== 解决策略九：分布式锁替代 =====

— 使用 Redis 分布式锁
public boolean transferWithRedisLock(Long fromId, Long toId, BigDecimal amount) {
String lockKey1 = "account:lock:" + Math.min(fromId, toId);
String lockKey2 = "account:lock:" + Math.max(fromId, toId);

String lock1 = redisTemplate.opsForValue().get(lockKey1);
String lock2 = redisTemplate.opsForValue().get(lockKey2);

// 按顺序获取锁
if (acquireLock(lockKey1, 5, TimeUnit.SECONDS) &&
acquireLock(lockKey2, 5, TimeUnit.SECONDS)) {
try {
// 执行转账
return doTransfer(fromId, toId, amount);
} finally {
releaseLock(lockKey1);
releaseLock(lockKey2);
}
}
return false;
}

— ===== 解决策略十：消息队列异步处理 =====

— 将并发写入转为顺序处理
public void transfer(Long fromId, Long toId, BigDecimal amount) {
// 发送消息到队列
kafkaTemplate.send("account-transfer", UUID.randomUUID().toString(),
new TransferMessage(fromId, toId, amount));
}

// 消费者顺序处理
@KafkaListener(topics = "account-transfer")
public void handleTransfer(TransferMessage message) {
// 使用单分区确保顺序
accountService.doTransfer(message.getFromId(),
message.getToId(), message.getAmount());
}

六、生产环境案例

6.1 订单扣库存死锁

— ===== 案例：库存扣减死锁 =====

CREATE TABLE products (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
stock INT NOT NULL DEFAULT 0,
version INT NOT NULL DEFAULT 0,
INDEX idx_name (name)
) ENGINE=InnoDB;

CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL UNIQUE,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE order_items (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

— 错误流程（死锁风险）
— 线程 A：创建订单并扣库存
BEGIN;
INSERT INTO orders (order_no) VALUES ('ORD001');
SET @order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity) VALUES (@order_id, 1, 2);
UPDATE products SET stock = stock – 2 WHERE id = 1;
COMMIT;

— 线程 B：创建订单并扣库存（并发）
BEGIN;
INSERT INTO orders (order_no) VALUES ('ORD002');
SET @order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity) VALUES (@order_id, 1, 3);
UPDATE products SET stock = stock – 3 WHERE id = 1; — 死锁！
COMMIT;

— 死锁原因：
— 1. 两个事务都先锁定 orders 主键
— 2. 然后尝试锁定 products
— 3. InnoDB 外键自动创建索引，导致锁定顺序不同

— ===== 正确流程 =====

— 方法一：先扣库存，再创建订单
BEGIN;
— 乐观锁扣库存
UPDATE products
SET stock = stock – 2, version = version + 1
WHERE id = 1 AND version = 0 AND stock >= 2;

— 检查影响行数
— IF affected_rows = 0 THEN ROLLBACK;

— 库存扣减成功后再创建订单
INSERT INTO orders (order_no) VALUES ('ORD001');
SET @order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity) VALUES (@order_id, 1, 2);
COMMIT;

— 方法二：使用单个事务，按固定顺序操作
BEGIN;
— 按 product_id 排序统一操作
UPDATE products SET stock = stock – 2 WHERE id = 1;
INSERT INTO orders (order_no) VALUES ('ORD001');
COMMIT;

— 方法三：减少锁定时间
BEGIN;
— 使用 SELECT FOR UPDATE NOWAIT 快速失败
SELECT * FROM products WHERE id = 1 FOR UPDATE NOWAIT;
— 检查是否成功，获取锁失败则直接返回错误

UPDATE products SET stock = stock – 2 WHERE id = 1;
— 快速完成订单创建
INSERT INTO orders (order_no) VALUES ('ORD001');
COMMIT;

6.2 余额变动死锁

— ===== 案例：余额变动死锁 =====

— 错误的转账实现
public void transferWrong(Long fromId, Long toId, BigDecimal amount) {
// 事务 A
@Transactional
public void transfer(Long fromId, Long toId, BigDecimal amount) {
// 先扣出
jdbcTemplate.update(
"UPDATE accounts SET balance = balance – ? WHERE id = ?",
amount, fromId
);

// 再转入
jdbcTemplate.update(
"UPDATE accounts SET balance = balance + ? WHERE id = ?",
amount, toId
);
}
}

— 问题：当 A 向 B 转账，B 向 A 转账时
— A: 锁定 1，尝试锁定 2
— B: 锁定 2，尝试锁定 1
— 死锁！

— ===== 正确实现：统一加锁顺序 =====

public void transferFixed(Long fromId, Long toId, BigDecimal amount) {
// 确保 ID 大的先锁定
Long firstId = fromId < toId ? fromId : toId;
Long secondId = fromId < toId ? toId : fromId;

@Transactional
public void doTransfer(Long firstId, Long secondId, BigDecimal amount) {
// 按 ID 顺序加锁
if (firstId.equals(fromId)) {
jdbcTemplate.update(
"UPDATE accounts SET balance = balance – ? WHERE id = ?",
amount, firstId
);
jdbcTemplate.update(
"UPDATE accounts SET balance = balance + ? WHERE id = ?",
amount, secondId
);
} else {
jdbcTemplate.update(
"UPDATE accounts SET balance = balance + ? WHERE id = ?",
amount, firstId
);
jdbcTemplate.update(
"UPDATE accounts SET balance = balance – ? WHERE id = ?",
amount, secondId
);
}
}
}

— ===== 最佳实现：乐观锁 + 重试 =====

@Transactional
public boolean transferOptimistic(Long fromId, Long toId, BigDecimal amount, int maxRetries) {
for (int i = 0; i < maxRetries; i++) {
try {
// 使用乐观锁扣款
int affected = jdbcTemplate.update("""
UPDATE accounts
SET balance = balance – ?, version = version + 1
WHERE id = ? AND version = ? AND balance >= ?
""", amount, fromId, currentVersion, amount);

if (affected == 0) {
// 版本不匹配，版本已被修改
// 重新查询并重试
Account from = jdbcTemplate.queryForObject(
"SELECT * FROM accounts WHERE id = ?",
Account.class, fromId);
// 重新计算版本…
continue;
}

// 转入
jdbcTemplate.update("""
UPDATE accounts
SET balance = balance + ?, version = version + 1
WHERE id = ?
""", amount, toId);

return true;
} catch (DataAccessException e) {
if (i == maxRetries – 1) {
throw e;
}
// 等待后重试
Thread.sleep(50 * (i + 1));
}
}
return false;
}

七、监控与预警

7.1 死锁监控 SQL

— ===== 死锁监控视图 =====

CREATE OR REPLACE VIEW v_deadlock_monitor AS
SELECT
NOW() AS check_time,
p.trx_id,
p.trx_mysql_thread_id AS thread_id,
p.trx_started,
p.trx_state,
p.trx_query,
p.trx_rows_locked,
p.trx_rows_modified,
TIMESTAMPDIFF(SECOND, p.trx_started, NOW()) AS running_seconds,
l.lock_mode,
l.lock_type,
l.lock_table,
l.lock_index
FROM information_schema.INNODB_TRX p
LEFT JOIN information_schema.INNODB_LOCKS l ON p.trx_id = l.lock_trx_id
ORDER BY p.trx_started;

— 查看当前锁等待
CREATE OR REPLACE VIEW v_lock_waits AS
SELECT
r.requesting_trx_id,
r.blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_state AS blocking_state,
b.trx_query AS blocking_query,
TIMESTAMPDIFF(SECOND, b.trx_wait_started, NOW()) AS wait_seconds,
l.lock_mode,
l.lock_table
FROM information_schema.INNODB_LOCK_WAITS r
JOIN information_schema.INNODB_TRX b ON r.blocking_trx_id = b.trx_id
JOIN information_schema.INNODB_LOCKS l ON b.trx_id = l.lock_trx_id
ORDER BY wait_seconds DESC;

— 查看最近死锁次数
CREATE OR REPLACE VIEW v_deadlock_stats AS
SELECT
VARIABLE_VALUE AS deadlock_count,
VARIABLE_NAME
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN ('Innodb_deadlocks', 'Innodb_rows_deleted', 'Innodb_rows_inserted');

— 查看锁内存使用
SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME LIKE '%lock%memory%'
OR VARIABLE_NAME LIKE '%innodb%lock%';

7.2 Prometheus 监控指标

# ===== Prometheus 监控配置 =====

# mysqld_exporter 配置
# 添加到 prometheus.yml
scrape_configs:
– job_name: 'mysql'
static_configs:
– targets: ['localhost:9104']
relabel_configs:
– source_labels: [__address__]
target_label: instance
regex: '(.+):\\d+'
replacement: '${1}'

# 关键指标
# – mysql_global_status_innodb_deadlocks # 死锁总数
# – mysql_global_status_innodb_lock_waits # 锁等待次数
# – mysql_global_status_innodb_lock_timeouts # 锁超时次数

# ===== Prometheus 查询 =====

# 死锁增长率
rate(mysql_global_status_innodb_deadlocks[5m])

# 锁等待增长率
rate(mysql_global_status_innodb_lock_waits_total[5m])

# 锁超时增长率
rate(mysql_global_status_innodb_lock_timeouts[5m])

# 告警规则
groups:
– name: mysql-lock-alerts
rules:
– alert: MySQLDeadlockDetected
expr: increase(mysql_global_status_innodb_deadlocks[5m]) > 0
for: 0m
labels:
severity: warning
annotations:
summary: "MySQL 死锁检测"
description: "检测到 {{ $value }} 个死锁"

– alert: MySQLLockWaitTimeout
expr: rate(mysql_global_status_innodb_lock_timeouts[5m]) > 0.1
for: 5m
labels:
severity: warning
annotations:
summary: "MySQL 锁等待超时"
description: "锁等待超时速率异常"

7.3 Grafana 仪表盘

{
"panels": [
{
"title": "死锁次数",
"type": "stat",
"targets": [
{
"expr": "mysql_global_status_innodb_deadlocks",
"legendFormat": "总死锁数"
}
]
},
{
"title": "锁等待次数",
"type": "graph",
"targets": [
{
"expr": "rate(mysql_global_status_innodb_lock_waits_total[5m])",
"legendFormat": "锁等待速率"
}
]
},
{
"title": "锁超时次数",
"type": "graph",
"targets": [
{
"expr": "rate(mysql_global_status_innodb_lock_timeouts[5m])",
"legendFormat": "锁超时速率"
}
]
}
]
}

八、总结

8.1 死锁排查流程

#mermaid-svg-vVe6zDd23Yqh9ptC{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-vVe6zDd23Yqh9ptC .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-vVe6zDd23Yqh9ptC .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-vVe6zDd23Yqh9ptC .error-icon{fill:#552222;}#mermaid-svg-vVe6zDd23Yqh9ptC .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-vVe6zDd23Yqh9ptC .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-vVe6zDd23Yqh9ptC .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-vVe6zDd23Yqh9ptC .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-vVe6zDd23Yqh9ptC .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-vVe6zDd23Yqh9ptC .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-vVe6zDd23Yqh9ptC .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-vVe6zDd23Yqh9ptC .marker{fill:#333333;stroke:#333333;}#mermaid-svg-vVe6zDd23Yqh9ptC .marker.cross{stroke:#333333;}#mermaid-svg-vVe6zDd23Yqh9ptC svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-vVe6zDd23Yqh9ptC p{margin:0;}#mermaid-svg-vVe6zDd23Yqh9ptC .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-vVe6zDd23Yqh9ptC .cluster-label text{fill:#333;}#mermaid-svg-vVe6zDd23Yqh9ptC .cluster-label span{color:#333;}#mermaid-svg-vVe6zDd23Yqh9ptC .cluster-label span p{background-color:transparent;}#mermaid-svg-vVe6zDd23Yqh9ptC .label text,#mermaid-svg-vVe6zDd23Yqh9ptC span{fill:#333;color:#333;}#mermaid-svg-vVe6zDd23Yqh9ptC .node rect,#mermaid-svg-vVe6zDd23Yqh9ptC .node circle,#mermaid-svg-vVe6zDd23Yqh9ptC .node ellipse,#mermaid-svg-vVe6zDd23Yqh9ptC .node polygon,#mermaid-svg-vVe6zDd23Yqh9ptC .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-vVe6zDd23Yqh9ptC .rough-node .label text,#mermaid-svg-vVe6zDd23Yqh9ptC .node .label text,#mermaid-svg-vVe6zDd23Yqh9ptC .image-shape .label,#mermaid-svg-vVe6zDd23Yqh9ptC .icon-shape .label{text-anchor:middle;}#mermaid-svg-vVe6zDd23Yqh9ptC .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-vVe6zDd23Yqh9ptC .rough-node .label,#mermaid-svg-vVe6zDd23Yqh9ptC .node .label,#mermaid-svg-vVe6zDd23Yqh9ptC .image-shape .label,#mermaid-svg-vVe6zDd23Yqh9ptC .icon-shape .label{text-align:center;}#mermaid-svg-vVe6zDd23Yqh9ptC .node.clickable{cursor:pointer;}#mermaid-svg-vVe6zDd23Yqh9ptC .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-vVe6zDd23Yqh9ptC .arrowheadPath{fill:#333333;}#mermaid-svg-vVe6zDd23Yqh9ptC .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-vVe6zDd23Yqh9ptC .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-vVe6zDd23Yqh9ptC .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-vVe6zDd23Yqh9ptC .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-vVe6zDd23Yqh9ptC .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-vVe6zDd23Yqh9ptC .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-vVe6zDd23Yqh9ptC .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-vVe6zDd23Yqh9ptC .cluster text{fill:#333;}#mermaid-svg-vVe6zDd23Yqh9ptC .cluster span{color:#333;}#mermaid-svg-vVe6zDd23Yqh9ptC div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-vVe6zDd23Yqh9ptC .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-vVe6zDd23Yqh9ptC rect.text{fill:none;stroke-width:0;}#mermaid-svg-vVe6zDd23Yqh9ptC .icon-shape,#mermaid-svg-vVe6zDd23Yqh9ptC .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-vVe6zDd23Yqh9ptC .icon-shape p,#mermaid-svg-vVe6zDd23Yqh9ptC .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-vVe6zDd23Yqh9ptC .icon-shape .label rect,#mermaid-svg-vVe6zDd23Yqh9ptC .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-vVe6zDd23Yqh9ptC .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-vVe6zDd23Yqh9ptC .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-vVe6zDd23Yqh9ptC :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

死锁发生

查看错误日志

分析死锁信息

定位锁冲突表

分析 SQL 执行顺序

根因

操作顺序不一致

索引选择问题

隔离级别问题

锁范围过大

统一操作顺序

优化索引

调整隔离级别

减小事务范围

回归验证

8.2 死锁解决策略汇总

8.3 最佳实践

8.4 学习路径

本文基于 MySQL 8.0 编写。如有问题欢迎评论区讨论！