MySQL死锁排查实战：从锁等待日志到代码改法

Author： zz1984
发布时间：October 20, 2026
2 views
No comments
11474 字数
Categories：技术教程

上周有个读者发来一条消息：他的论坛在晚上八点突然开始大量报错，用户发帖时页面直接返回 500，日志里刷满了 Deadlock found when trying to get lock; try restarting transaction。他第一反应是重启 MySQL，重启后好了十分钟，然后又死。第二天早上流量一降，自己就好了。他问我：这是不是数据库撑不住了，要不要升级配置？

这类问题的答案通常是"不用升级配置"。死锁不是性能问题，而是并发顺序问题。MySQL 在检测到循环等待时会主动回滚其中一个事务，这不是 bug，而是保护机制。真正需要处理的是：为什么这两个事务会互相等待，以及业务代码为什么没有把回滚当成一种正常结果来处理。

这篇文章把 MySQL 死锁的排查链路完整走一遍：怎么让死锁自动记录、怎么读 InnoDB 输出的死锁现场、怎么用 performance_schema 定位正在等待的锁、长事务怎么揪出来、最后怎么在代码层面把死锁概率压下去。所有示例基于 MySQL 8.0，5.7 的差异会在相应位置标出。

第一步：先让 MySQL 把每次死锁都记下来

默认情况下，InnoDB 只会把最近一次死锁信息放在内存里，下一次死锁发生时就被覆盖。如果你的站点一天死锁几十次，你去查的时候看到的永远是"最后那一次"，而这往往不是你最关心的那次。所以第一件事是把死锁详细日志打开。

-- 全局打开，注意这个参数是动态的，重启后失效
SET GLOBAL innodb_print_all_deadlocks = ON;

-- 想让它持久化，写进配置并重启
# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
innodb_print_all_deadlocks = ON

打开之后，每一次死锁都会被完整写入 MySQL 的错误日志（/var/log/mysql/error.log）。日志量会增加一些，但对个人站点来说完全可以接受，一次死锁大约几十行。

同时建议把锁等待超时调小一点，方便暴露问题。默认 innodb_lock_wait_timeout 是 50 秒，意味着一个卡住的事务会让连接池里的连接被占住将近一分钟，高峰期这会迅速把连接池耗干，表现就是"数据库连接数满了"。

SET GLOBAL innodb_lock_wait_timeout = 10;   -- 会话级也可以单独设

-- 看看当前值
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

这里有个取舍：调小之后，原本会等待 30 秒最终成功的语句会变成报错。所以在调小之前，先确认你的应用代码里有重试逻辑，否则等于把死锁问题换成了大量"锁等待超时"的线上错误。这一点后面会专门讲。

第二步：读懂 LATEST DETECTED DEADLOCK 这一段

如果不想翻错误日志，用这条命令可以直接拿到内存里保存的最近一次死锁：

SHOW ENGINE INNODB STATUS\G

输出很长，你只需要看 LATEST DETECTED DEADLOCK 那一节。它通常是这个样子（下面是我在一台测试机上复现出来的真实输出，做了删减）：

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12847, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 12, OS thread handle 140234, query id 8821 localhost root updating
UPDATE posts SET views = views + 1 WHERE id = 42
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `bbs`.`posts` trx id 12847 lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `bbs`.`posts` trx id 12847 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 12846, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
UPDATE posts SET last_active = NOW() WHERE id = 43
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `bbs`.`posts` trx id 12846 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `bbs`.`posts` trx id 12846 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

读这段东西只需要抓住四件事：

1）谁等了谁。 把 (1) 和 (2) 当作两个参与者。看 (1) HOLDS 的部分：它拿着哪一行、什么模式的锁；再看 (2) WAITING FOR 的部分：它想要哪一行。如果两边互相持有对方想要的行，就是标准的循环等待。上面这个例子里，两个事务分别 UPDATE 了 id=42 和 id=43，正常情况下互不相干——但注意 n bits 72 和索引名 PRIMARY，说明它们的锁都落在了同一页上，并且如果 WHERE 条件没有走到唯一索引，锁范围会比你以为的大得多。

2）锁模式。lock_mode X locks rec but not gap 表示只锁了记录本身，没有锁间隙；如果看到 locks gap before rec 或者 X 后面没有 rec but not gap，说明涉及间隙锁（gap lock），这通常发生在 REPEATABLE READ 隔离级别下的范围查询或非唯一索引上。间隙锁是死锁的高发地带，后面会给规避方法。

3）哪条 SQL。 *** (1) TRANSACTION 下面的那条 SQL 就是当事语句。很多人看到 SQL 觉得"这俩明显不冲突啊"，问题恰恰在于执行计划走的不是你想象中的索引。

4）谁被回滚了。 最后一行 WE ROLL BACK TRANSACTION (2) 告诉你 InnoDB 牺牲了哪个事务。InnoDB 通常选择代价较小的一方（修改行数少的），但不要依赖这个策略做业务设计。

把这几条死锁日志收集起来连续看一周，你会发现绝大多数死锁集中在两三个特定的 SQL 上。定位到这里，问题基本就解决了一半。

第三步：用 performance_schema 看"现在"谁在等谁

死锁日志是事后的，它告诉你昨天发生了什么。但如果数据库此刻正卡着、页面正在转圈，你需要看的是实时现场。MySQL 8.0 的 performance_schema.data_locks 和 data_lock_waits 就是为这个准备的。

-- 当前所有锁，按表分组看比较直观
SELECT
  ENGINE_TRANSACTION_ID AS trx,
  OBJECT_SCHEMA, OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE, LOCK_MODE, LOCK_STATUS,
  LOCK_DATA
FROM performance_schema.data_locks
ORDER BY OBJECT_NAME, INDEX_NAME, LOCK_DATA;

LOCK_STATUS 为 WAITING 的行就是正在排队等锁的。LOCK_DATA 会告诉你锁在主键的哪个值上（如果是间隙锁，这里会显示区间边界）。这一张表就能让你在几秒内回答"到底是谁锁了哪一行"。

更直接的是等待关系表，它把"谁在等谁"配对好了：

SELECT
  r.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
  r.BLOCKING_ENGINE_TRANSACTION_ID  AS blocking_trx,
  r.OBJECT_SCHEMA, r.OBJECT_NAME, r.INDEX_NAME,
  r.LOCK_TYPE, r.LOCK_MODE
FROM performance_schema.data_lock_waits r;

拿到 blocking_trx 之后，再去 information_schema.innodb_trx 里查这个事务在干什么、开了多久：

SELECT
  trx_id, trx_state, trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
  trx_mysql_thread_id, trx_rows_locked, trx_rows_modified,
  LEFT(trx_query, 200) AS query_now,
  trx_operation_state
FROM information_schema.innodb_trx
ORDER BY age_sec DESC;

这张表的 age_sec 是关键指标。正常业务事务应该在一秒内结束；如果有事务活着超过 60 秒还拿着锁，那它几乎肯定是"事务没关"——要么是代码里忘了 commit/rollback，要么是在事务里做了 HTTP 请求、发邮件、调用第三方接口这类慢操作。

如果 trx_query 是 NULL 但事务还活着，说明它处于"已执行完语句、等待应用层提交"的状态，也就是最典型的连接泄漏。这时候要顺着 trx_mysql_thread_id 去找对应的连接：

-- 看这个连接从哪来、空闲多久、执行过多少语句
SELECT id, user, host, db, command, time, state,
       LEFT(info, 120) AS last_sql
FROM information_schema.processlist
WHERE id = 12345;

-- 确认是僵死连接后，才是 kill（只 kill 连接，不 kill 事务，效果一样）
-- KILL 12345;

这里必须提醒一句：KILL 是应急手段，不是解决方案。kill 掉一个正在做大事务的连接会触发回滚，回滚大事务可能比事务本身跑得更久，期间锁也不会立刻释放。有些站长在半夜被报警叫醒后连着 kill 十几个连接，结果数据库更卡——原因就在这里。正确的做法是找到源头的那一个长事务，杀掉它，然后把代码里的问题修掉。

第四步：长事务是死锁的放大器

死锁本身是瞬时的：两个事务互相等待，InnoDB 立刻检测到并回滚一个，整个循环在毫秒级就解开了。真正把死锁从"偶发报错"变成"服务不可用"的，是长事务。

设想一下：一个事务在 UPDATE posts SET views = views + 1 之后，代码里顺手又调了一次搜索引擎的索引更新接口，那个接口今天恰好慢，超时 30 秒。在这 30 秒里，这个事务一直持有 posts 表的行锁。任何想更新同一行的请求都会排队，排队的请求又可能持有别的行锁，形成新的循环等待。这就是为什么"重启后好十分钟，然后又死"——重启清掉了积压的事务，但只要那个慢接口还在、事务还是那么长，积压就会重新形成。

在 MySQL 8.0 里，可以用 events_statements_history_long 反查一个事务里到底执行了哪些语句，这是定位"事务里干了什么蠢事"最有效的办法：

SELECT
  THREAD_ID, EVENT_ID, SQL_TEXT,
  TIMER_WAIT / 1000000000 AS ms,
  CURRENT_SCHEMA
FROM performance_schema.events_statements_history_long
WHERE THREAD_ID = 88
ORDER BY EVENT_ID;

（TIMER_WAIT 单位是皮秒，除以 10 亿得到毫秒。）看到事务中间夹着一条耗时 3 万毫秒的语句，问题就一目了然了。这个表默认保留 10000 行，通常够用；如果不够，把 performance_schema_events_statements_history_long_size 调大。

对于更粗暴的检查，还有一个 sys 库里的现成视图：

-- 找出长时间空闲的事务（已执行完语句但未提交）
SELECT * FROM sys.innodb_lock_waits\G

-- 按表统计当前持有锁的数量，找出被波及最广的表
SELECT OBJECT_NAME, COUNT(*) AS locks
FROM performance_schema.data_locks
GROUP BY OBJECT_NAME
ORDER BY locks DESC;

第五步：消除死锁的四个实操手段

定位完之后要动手改。下面按"收益从高到低"排列，每一条都是我在实际站点上验证过的。

手段一：给批量操作的行排序，让锁的获取顺序一致。 这是最经典也最有效的方法。循环等待的成因是两个事务给同一批行加锁的顺序相反：A 先锁 id=1 再锁 id=2，B 先锁 id=2 再锁 id=1。解决办法很简单——在应用层先把要处理的主键排好序，再依次更新。

# 反例：顺序取决于用户提交的顺序，死锁概率随并发线性上升
for pid in post_ids:
    cur.execute("UPDATE posts SET checked = 1 WHERE id = %s", (pid,))

# 正例：统一按主键升序加锁
for pid in sorted(set(post_ids)):
    cur.execute("UPDATE posts SET checked = 1 WHERE id = %s", (pid,))

手段二：把批量更新合并成单条语句。 一条 UPDATE ... WHERE id IN (...) 在 InnoDB 内部会按索引顺序加锁，天然有序，而且只需要一次往返。这比循环 UPDATE 快一个数量级，死锁概率也降为零。

UPDATE posts
SET checked = 1
WHERE id IN (12, 18, 25, 31);

注意 IN 列表不要太长，几百个主键以上建议分批（每批 500 到 1000 个），否则单条语句的锁持有时间会变长，反而增加冲突。

手段三：让 WHERE 条件走唯一索引。 这是很多人忽略的一点。如果 WHERE slug = 'xxx' 而 slug 上只有普通索引，那么更新时会锁住"这个值以及它前后的间隙"，也就是间隙锁。两个不同 slug 的更新如果恰好落在同一个间隙里，就会互相阻塞。改成唯一索引（或者干脆用主键定位）之后，锁退化为单行记录锁，冲突面一下就小了。

-- 检查现有的索引情况
SHOW INDEX FROM posts;

-- 如果 slug 业务上本该唯一，就建成唯一索引
ALTER TABLE posts ADD UNIQUE KEY uk_slug (slug);
-- 8.0 支持在线加索引，加之前先确认表里没有重复值
SELECT slug, COUNT(*) c FROM posts GROUP BY slug HAVING c > 1;

如果业务上确实不能保证唯一，另一个选择是评估是否可以把隔离级别从 REPEATABLE READ 降到 READ COMMITTED。READ COMMITTED 下间隙锁基本不用，死锁会大幅减少——代价是同一事务内两次读取可能得到不同结果，需要业务代码确认能接受。个人站点的论坛、评论类业务通常是可以接受的。

SET GLOBAL transaction_isolation = 'READ-COMMITTED';   -- 5.7 用 tx_isolation
-- 建议同时打开 binlog 的 row 格式，RC 级别下 statement 格式不安全
SET GLOBAL binlog_format = 'ROW';

手段四：缩短事务，把非数据库操作挪出去。 事务里只做数据库读写，别的全部挪到事务外。发送邮件、调用搜索索引、更新缓存、写外部统计——这些都应该在 commit 之后做，失败了大不了补偿，不能让它们拖着一个数据库事务不放。

# 反例
tx.begin()
tx.update("UPDATE posts SET status = 1 WHERE id = 42")
requests.post("https://search.internal/index", timeout=30)   # 慢操作在事务里
tx.commit()

# 正例
tx.begin()
tx.update("UPDATE posts SET status = 1 WHERE id = 42")
tx.commit()
try:
    requests.post("https://search.internal/index", timeout=5)
except Exception:
    queue.push({"op": "reindex", "pid": 42})     # 失败进队列异步补偿

第六步：把死锁当成正常返回值来处理

无论怎么优化，"死锁错误完全归零"是不现实的。只要并发存在，就可能出现死锁。所以应用层必须把死锁当成一种预期内的情况来处理：捕获、短暂退避、重试。

function runWithRetry(callable $fn, int $max = 3) {
    for ($i = 1; $i <= $max; $i++) {
        try {
            return $fn();
        } catch (PDOException $e) {
            // 1213 = 死锁，1205 = 锁等待超时
            if (!in_array($e->errorInfo[1], [1213, 1205], true) || $i === $max) {
                throw $e;
            }
            // 退避要带随机抖动，避免所有请求同时重试再次撞死
            usleep(random_int(20000, 80000) * $i);
        }
    }
}

三个细节值得注意。第一，只重试死锁和锁等待超时，不要把所有异常都重试，否则会把真正的 bug 掩盖掉。第二，退避必须带随机抖动，固定 100ms 的重试会让所有等待者同时醒来，再次形成死锁。第三，重试的事务必须整体重来，不能从中断的那条语句继续——因为 InnoDB 回滚的是整个事务，之前做的修改都已经没了。

另外，重试次数不要设太多（3 次足够），并且要有一个总时长上限。如果一个请求重试五次还死锁，说明并发模型本身有问题，让它报错并暴露出来，比无限重试拖着连接池要健康得多。

附：一个可以直接复用的死锁复盘清单

把上面的内容压缩成一个可以贴在显示器旁边的检查表。下次报警的时候按顺序走，五分钟就能定位：

1. 日志里搜 Latency：grep -c "LATEST DETECTED DEADLOCK" /var/log/mysql/error.log
2. SHOW ENGINE INNODB STATUS\G  看最近一次是谁等了谁
3. 从死锁段里抄出两条 SQL，各自 EXPLAIN 一遍
   —— 索引是否走对？type 是不是 ALL？
4. information_schema.innodb_trx 按 age_sec 倒序
   —— 有没有超过 60 秒的事务？它的 trx_query 是什么？
5. performance_schema.data_lock_waits 看实时等待链
   —— blocking_trx 对应哪个线程、哪个应用？
6. 确认长事务后：先修代码，再考虑 kill 连接
7. 修复后观察 24 小时：死锁次数是否下降到可接受范围

关于第 1 步的统计，值得做成一个每天跑一次的监控，比报警更有用：

# 每天凌晨统计前一天的死锁次数，超过阈值就邮件提醒
#!/bin/bash
LOG=/var/log/mysql/error.log
COUNT=$(grep -c "LATEST DETECTED DEADLOCK" "$LOG")
if [ "$COUNT" -gt 20 ]; then
    echo "昨日死锁 $COUNT 次，请检查" | mail -s "MySQL 死锁告警" admin@example.com
fi

回到开头那位读者的情况。他按照这套流程走了一遍，死锁日志里出现的 SQL 全都指向同一个动作：用户发帖时，代码先把新帖插入 posts 表，再遍历热门标签表把每个标签的帖子数 +1，而两个用户同时发帖时，遍历热门标签的顺序取决于标签在页面上的显示顺序，恰好是相反的。改成 ORDER BY tag_id 之后再统计标签数，死锁次数从每天几十次降到了零。全程没有升级配置，没有加机器。

死锁排查的核心不是背参数，而是建立一条稳定的观测路径：先让 MySQL 把现场记下来，再从现场反推到 SQL，从 SQL 反推到索引，从索引反推到代码里的加锁顺序。这条路径走顺了，绝大多数"数据库好像撑不住了"的报警，最后都会落到一行可以改的代码上。

Last modification：October 20th, 2026 at 08:24 pm

MySQL死锁排查实战：从锁等待日志到代码改法

zz1984 • 2026 年 10 月 20 日

第一步：先让 MySQL 把每次死锁都记下来

-- 全局打开，注意这个参数是动态的，重启后失效
SET GLOBAL innodb_print_all_deadlocks = ON;

-- 想让它持久化，写进配置并重启
# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
innodb_print_all_deadlocks = ON

SET GLOBAL innodb_lock_wait_timeout = 10;   -- 会话级也可以单独设

-- 看看当前值
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

第二步：读懂 LATEST DETECTED DEADLOCK 这一段

如果不想翻错误日志，用这条命令可以直接拿到内存里保存的最近一次死锁：

SHOW ENGINE INNODB STATUS\G

输出很长，你只需要看 LATEST DETECTED DEADLOCK 那一节。它通常是这个样子（下面是我在一台测试机上复现出来的真实输出，做了删减）：

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12847, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 12, OS thread handle 140234, query id 8821 localhost root updating
UPDATE posts SET views = views + 1 WHERE id = 42
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `bbs`.`posts` trx id 12847 lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `bbs`.`posts` trx id 12847 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 12846, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
UPDATE posts SET last_active = NOW() WHERE id = 43
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `bbs`.`posts` trx id 12846 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `bbs`.`posts` trx id 12846 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

读这段东西只需要抓住四件事：

把这几条死锁日志收集起来连续看一周，你会发现绝大多数死锁集中在两三个特定的 SQL 上。定位到这里，问题基本就解决了一半。

第三步：用 performance_schema 看"现在"谁在等谁

-- 当前所有锁，按表分组看比较直观
SELECT
  ENGINE_TRANSACTION_ID AS trx,
  OBJECT_SCHEMA, OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE, LOCK_MODE, LOCK_STATUS,
  LOCK_DATA
FROM performance_schema.data_locks
ORDER BY OBJECT_NAME, INDEX_NAME, LOCK_DATA;

更直接的是等待关系表，它把"谁在等谁"配对好了：

SELECT
  r.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
  r.BLOCKING_ENGINE_TRANSACTION_ID  AS blocking_trx,
  r.OBJECT_SCHEMA, r.OBJECT_NAME, r.INDEX_NAME,
  r.LOCK_TYPE, r.LOCK_MODE
FROM performance_schema.data_lock_waits r;

拿到 blocking_trx 之后，再去 information_schema.innodb_trx 里查这个事务在干什么、开了多久：

SELECT
  trx_id, trx_state, trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
  trx_mysql_thread_id, trx_rows_locked, trx_rows_modified,
  LEFT(trx_query, 200) AS query_now,
  trx_operation_state
FROM information_schema.innodb_trx
ORDER BY age_sec DESC;

-- 看这个连接从哪来、空闲多久、执行过多少语句
SELECT id, user, host, db, command, time, state,
       LEFT(info, 120) AS last_sql
FROM information_schema.processlist
WHERE id = 12345;

-- 确认是僵死连接后，才是 kill（只 kill 连接，不 kill 事务，效果一样）
-- KILL 12345;

第四步：长事务是死锁的放大器

在 MySQL 8.0 里，可以用 events_statements_history_long 反查一个事务里到底执行了哪些语句，这是定位"事务里干了什么蠢事"最有效的办法：

SELECT
  THREAD_ID, EVENT_ID, SQL_TEXT,
  TIMER_WAIT / 1000000000 AS ms,
  CURRENT_SCHEMA
FROM performance_schema.events_statements_history_long
WHERE THREAD_ID = 88
ORDER BY EVENT_ID;

对于更粗暴的检查，还有一个 sys 库里的现成视图：

-- 找出长时间空闲的事务（已执行完语句但未提交）
SELECT * FROM sys.innodb_lock_waits\G

-- 按表统计当前持有锁的数量，找出被波及最广的表
SELECT OBJECT_NAME, COUNT(*) AS locks
FROM performance_schema.data_locks
GROUP BY OBJECT_NAME
ORDER BY locks DESC;

第五步：消除死锁的四个实操手段

定位完之后要动手改。下面按"收益从高到低"排列，每一条都是我在实际站点上验证过的。

# 反例：顺序取决于用户提交的顺序，死锁概率随并发线性上升
for pid in post_ids:
    cur.execute("UPDATE posts SET checked = 1 WHERE id = %s", (pid,))

# 正例：统一按主键升序加锁
for pid in sorted(set(post_ids)):
    cur.execute("UPDATE posts SET checked = 1 WHERE id = %s", (pid,))

UPDATE posts
SET checked = 1
WHERE id IN (12, 18, 25, 31);

注意 IN 列表不要太长，几百个主键以上建议分批（每批 500 到 1000 个），否则单条语句的锁持有时间会变长，反而增加冲突。

-- 检查现有的索引情况
SHOW INDEX FROM posts;

-- 如果 slug 业务上本该唯一，就建成唯一索引
ALTER TABLE posts ADD UNIQUE KEY uk_slug (slug);
-- 8.0 支持在线加索引，加之前先确认表里没有重复值
SELECT slug, COUNT(*) c FROM posts GROUP BY slug HAVING c > 1;

SET GLOBAL transaction_isolation = 'READ-COMMITTED';   -- 5.7 用 tx_isolation
-- 建议同时打开 binlog 的 row 格式，RC 级别下 statement 格式不安全
SET GLOBAL binlog_format = 'ROW';

# 反例
tx.begin()
tx.update("UPDATE posts SET status = 1 WHERE id = 42")
requests.post("https://search.internal/index", timeout=30)   # 慢操作在事务里
tx.commit()

# 正例
tx.begin()
tx.update("UPDATE posts SET status = 1 WHERE id = 42")
tx.commit()
try:
    requests.post("https://search.internal/index", timeout=5)
except Exception:
    queue.push({"op": "reindex", "pid": 42})     # 失败进队列异步补偿

第六步：把死锁当成正常返回值来处理

function runWithRetry(callable $fn, int $max = 3) {
    for ($i = 1; $i <= $max; $i++) {
        try {
            return $fn();
        } catch (PDOException $e) {
            // 1213 = 死锁，1205 = 锁等待超时
            if (!in_array($e->errorInfo[1], [1213, 1205], true) || $i === $max) {
                throw $e;
            }
            // 退避要带随机抖动，避免所有请求同时重试再次撞死
            usleep(random_int(20000, 80000) * $i);
        }
    }
}

附：一个可以直接复用的死锁复盘清单

把上面的内容压缩成一个可以贴在显示器旁边的检查表。下次报警的时候按顺序走，五分钟就能定位：

1. 日志里搜 Latency：grep -c "LATEST DETECTED DEADLOCK" /var/log/mysql/error.log
2. SHOW ENGINE INNODB STATUS\G  看最近一次是谁等了谁
3. 从死锁段里抄出两条 SQL，各自 EXPLAIN 一遍
   —— 索引是否走对？type 是不是 ALL？
4. information_schema.innodb_trx 按 age_sec 倒序
   —— 有没有超过 60 秒的事务？它的 trx_query 是什么？
5. performance_schema.data_lock_waits 看实时等待链
   —— blocking_trx 对应哪个线程、哪个应用？
6. 确认长事务后：先修代码，再考虑 kill 连接
7. 修复后观察 24 小时：死锁次数是否下降到可接受范围

关于第 1 步的统计，值得做成一个每天跑一次的监控，比报警更有用：

# 每天凌晨统计前一天的死锁次数，超过阈值就邮件提醒
#!/bin/bash
LOG=/var/log/mysql/error.log
COUNT=$(grep -c "LATEST DETECTED DEADLOCK" "$LOG")
if [ "$COUNT" -gt 20 ]; then
    echo "昨日死锁 $COUNT 次，请检查" | mail -s "MySQL 死锁告警" admin@example.com
fi

MySQL死锁排查实战：从锁等待日志到代码改法

第一步：先让 MySQL 把每次死锁都记下来

第二步：读懂 LATEST DETECTED DEADLOCK 这一段

第三步：用 performance_schema 看"现在"谁在等谁

第四步：长事务是死锁的放大器

第五步：消除死锁的四个实操手段

第六步：把死锁当成正常返回值来处理

附：一个可以直接复用的死锁复盘清单

相关文章

Leave a Comment Cancel reply

自建CF节点之反代Cloudflare加速网站(Gost/iptables转发)

技术学习路径与资源导航

LayerStack：香港原生IP服务器无限流量四网直连外加双向cn2

js底部飘浮广告代码

lnmp 去除防跨域问题

个人站长服务器监控实战：Shell 脚本与告警通知守好最后一班岗

Linux 服务器防火墙配置与入侵检测实战指南

软件 RAID 阵列降级处理实战：mdadm 判读、故障盘定位与重建后重启阵列消失的防范

别再只用 cron 了：systemd timer 定时任务实战，补跑、错峰与日志排查一次讲清

Linux 服务器 SSH 安全加固实战：密钥登录、双因素认证与 fail2ban 完整防线

MySQL死锁排查实战：从锁等待日志到代码改法

第一步：先让 MySQL 把每次死锁都记下来

第二步：读懂 LATEST DETECTED DEADLOCK 这一段

第三步：用 performance_schema 看"现在"谁在等谁

第四步：长事务是死锁的放大器

第五步：消除死锁的四个实操手段

第六步：把死锁当成正常返回值来处理

附：一个可以直接复用的死锁复盘清单