MySQL 连接数、锁与死锁排查：从 too many connections 到死锁日志

一、结论先给

数据库"卡住"一共就三种可能：连接不够用、锁等着、资源打满（CPU/IO）。前两种有明确命令可查，处理顺序是：

1. SHOW PROCESSLIST 看在等什么
2. 连接满 → 杀空闲连接 + 查连接池配置
3. 有 Waiting for lock → 定位持锁事务，KILL 它或等它
4. 报 Deadlock → 看 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK

绝大多数"数据库卡死"是长事务 + 锁等待，不是数据库本身的问题。

👉 应用日志里突然一片 Lock wait timeout 时，把日志粘进 日志分析工具，能直接统计出错误类型、首次出现时间和高发时段。

二、连接数打满

2.1 现象与应急

ERROR 1040 (HY000): Too many connections

应急三步（先恢复服务，再找根因）：

-- 1. 看当前连接情况
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- 2. 生成批量 kill 语句，杀掉长时间 Sleep 的连接
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Sleep' AND TIME > 300
  AND USER NOT IN ('root','system user','repl');

-- 3. 执行上面生成的语句

root 用户有一个额外连接名额（max_connections 之外的一席），所以连不上时试试用 root 登。

2.2 根因与根治

根因	检查	处理
应用连接池配置过大	实例数 × 池大小 > max_connections	算清楚总连接数，调小池
连接泄漏（用完没关）	SHOW PROCESSLIST 里大量 Sleep	修代码，确认 finally 里 close
慢查询堆积	TIME 很大的 Query	优化慢 SQL（见慢查询篇）
真的需要更多连接	Max_used_connections 长期接近上限	调大 max_connections 或加从库

-- 看历史最高水位
SHOW STATUS LIKE 'Max_used_connections';
-- 建议：Max_used_connections / max_connections < 80%

-- 在线调大（重启失效）
SET GLOBAL max_connections = 1000;
-- 永久：my.cnf 里 max_connections = 1000

-- 连接空闲超时（自动回收，8 小时太长）
SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;

计算合理值：max_connections 应大于所有应用实例的连接池上限之和 + 30% 余量。例如 4 个实例 × 池上限 50 = 200，加上管理连接，设 300。

⚠️ 别盲目调大：每个连接都要吃内存（thread_stack + 排序缓冲等），几千个连接会吃掉几个 G。

三、MDL 锁（元数据锁）：ALTER TABLE 卡住的元凶

现象：ALTER TABLE 一直挂着，SHOW PROCESSLIST 里一堆 Waiting for table metadata lock。

成因：有事务还开着没提交，持有该表的 MDL 读锁，ALTER 要 MDL 写锁，被堵住；而后续所有查询也被这个 ALTER 堵住，整表不可用。

-- 找出未提交的长事务（这是关键）
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS 已运行秒,
       trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC LIMIT 10;

找到 trx_mysql_thread_id 后：

KILL 12345;      -- 杀掉那个一直没提交的事务

预防：

-- 1. 设置 DDL 等待超时，别无限等（MySQL 8）
SET GLOBAL lock_wait_timeout = 30;

-- 2. 确认没有长事务再做 DDL
SELECT COUNT(*) FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10;

-- 3. 业务代码里事务要短，不要在事务里做 RPC、查缓存、sleep

四、行锁等待

4.1 InnoDB 的三种行锁算法

锁类型	锁定范围	触发条件
Record Lock	单条索引记录	等值命中唯一索引
Gap Lock	记录之间的间隙	范围查询（RR 隔离级别）
Next-Key Lock	记录 + 前面的间隙	范围查询、普通索引等值

间隙锁是"我没改到数据也被锁住"的根源。RR（默认隔离级别）下，WHERE id > 100 FOR UPDATE 锁的不只是 id>100 的行，还包括 (100, +∞) 这个间隙，插入 id=1010 也会被阻塞。

4.2 查看锁等待

-- 当前锁等待关系（MySQL 8）
SELECT * FROM performance_schema.data_lock_waits;

-- 谁持有锁、谁在等（MySQL 8）
SELECT
  r.trx_id                    AS 等待事务,
  r.trx_mysql_thread_id       AS 等待线程,
  b.trx_id                    AS 持锁事务,
  b.trx_mysql_thread_id       AS 持锁线程,
  b.trx_started               AS 持锁事务开始时间,
  TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS 已持锁秒
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 更直观（sys schema）
SELECT * FROM sys.innodb_lock_waits\G

报错信息长这样：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

默认等 50 秒（innodb_lock_wait_timeout）就放弃。这个错不是数据库坏了，是有人在跟你抢同一行。

-- 调整等待时间（秒）
SET GLOBAL innodb_lock_wait_timeout = 30;

五、死锁：一个可复现的完整案例

5.1 复现

-- 表结构
CREATE TABLE account (
  id INT PRIMARY KEY,
  balance INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO account VALUES (1, 100), (2, 100);

-- 会话 A
BEGIN;
UPDATE account SET balance = balance - 10 WHERE id = 1;   -- 锁住 id=1

-- 会话 B
BEGIN;
UPDATE account SET balance = balance - 20 WHERE id = 2;   -- 锁住 id=2

-- 会话 A
UPDATE account SET balance = balance + 10 WHERE id = 2;   -- 等 B 释放 id=2

-- 会话 B
UPDATE account SET balance = balance + 20 WHERE id = 1;   -- 等 A 释放 id=1 → 死锁！

MySQL 会立刻检测到并回滚其中一个：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

5.2 分析死锁日志

SHOW ENGINE INNODB STATUS\G

找 LATEST DETECTED DEADLOCK 段：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-09-23 10:15:33 0x7f8b4c0e9700
*** (1) TRANSACTION:
TRANSACTION 421567, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
UPDATE account SET balance = balance + 10 WHERE id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 3 n bits 72 index PRIMARY of table `app`.`account`
trx id 421567 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 421568, ACTIVE 3 sec starting index read
UPDATE account SET balance = balance + 20 WHERE id = 1
*** (2) HOLDS THE LOCK(S):  ← 它持有 A 要的锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
*** WE ROLL BACK TRANSACTION (2)   ← MySQL 选择回滚代价小的那个

读懂三点就够了：(1) 在等什么锁、(2) 持有什么锁、最后回滚了谁。

注意：SHOW ENGINE INNODB STATUS 只显示最近一次死锁。要持久化记录：

SET GLOBAL innodb_print_all_deadlocks = ON;   -- 写进 error log
-- my.cnf: innodb_print_all_deadlocks = 1

六、避免死锁的六条编码约定

约定	说明
1. 固定访问顺序	所有业务按同一顺序更新多行（如按 id 升序），交叉加锁就不会死锁
2. 事务尽量短	不在事务里调外部接口、不发短信、不 sleep
3. 用主键/唯一索引更新	避免范围锁和间隙锁
4. 降低隔离级别到 RC	互联网业务普遍用 RC，能减少大量间隙锁（需 binlog_format=ROW）
5. 热点行拆分	库存行拆成 10 个分段行，随机命中一个，降低争抢
6. 重试机制	死锁是正常现象，应用层捕获 1213 后重试 2-3 次

第 1 条最有效：把 UPDATE a; UPDATE b; 统一排序成先小 id 后大 id，死锁直接消失。

应用层重试示例（Java 思路，各语言同理）：

int retry = 3;
while (retry-- > 0) {
    try {
        return doInTransaction();        // 业务逻辑
    } catch (DeadlockLoserDataAccessException e) {
        if (retry == 0) throw e;
        Thread.sleep(50 + random.nextInt(100));   // 随机退避，避免再次撞上
    }
}

七、热点行更新：秒杀场景的处理

-- 慢且容易死锁（要读再写，还可能有锁等待）
UPDATE sku SET stock = stock - 1 WHERE id = 1 AND stock > 0;

-- 优化一：分段库存（把一个热点行拆成 10 行）
UPDATE sku_stock SET stock = stock - 1
WHERE sku_id = 1 AND seg = FLOOR(RAND() * 10) AND stock > 0;

-- 优化二：先扣 Redis，异步落库（最终一致）

分段后库存检查要汇总：

SELECT SUM(stock) FROM sku_stock WHERE sku_id = 1;

八、排障命令速查表

-- 连接
SHOW PROCESSLIST;
SELECT USER, COUNT(*) c FROM information_schema.PROCESSLIST GROUP BY USER ORDER BY c DESC;
SHOW STATUS LIKE 'Threads%'; SHOW STATUS LIKE 'Max_used_connections';

-- 事务
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 10\G

-- 锁等待
SELECT * FROM sys.innodb_lock_waits\G
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks LIMIT 20;

-- 死锁
SHOW ENGINE INNODB STATUS\G        -- 看 LATEST DETECTED DEADLOCK

-- MDL
SELECT * FROM performance_schema.metadata_locks WHERE OWNER_THREAD_ID != sys.ps_thread_id(NULL);

-- 杀
KILL QUERY 12345;                  -- 只杀查询
KILL 12345;                        -- 杀连接

九、常见误区

1. 连接满了就调大 max_connections：掩盖了连接泄漏，过几天又满，还多吃了内存。
2. ALTER TABLE 卡住就重启数据库：只要找到那个未提交的长事务 KILL 掉就行，重启代价大得多。
3. 死锁当异常处理掉就完事：死锁频率高说明访问顺序有问题，要从代码层修。
4. 不看持锁方只杀等待方：等待方杀了一个来一个，得杀持锁的长事务。
5. 事务里调用外部接口：接口慢 3 秒，锁就多持 3 秒，并发上来直接雪崩。
6. 用 SELECT ... FOR UPDATE 做业务判断：能不用就不用，优先用 UPDATE ... WHERE 的原子性（affected rows 判断）。

十、几条纪律

1. 连接池总大小要在部署时算清楚，留 30% 余量，配 wait_timeout 自动回收。
2. 事务要短：只包住必要的 SQL，绝不包 RPC、文件 IO、sleep。
3. 多行更新固定顺序（按主键排序），这是消除死锁最有效的一招。
4. 开 innodb_print_all_deadlocks，死锁全量进 error log，便于复盘。
5. 应用必须捕获 1213（死锁）和 1205（锁等待超时）并带退避重试。
6. DDL 前先查有没有长事务，设 lock_wait_timeout 避免无限等待拖垮整表。

十一、延伸阅读

• MySQL 慢查询与索引优化
• MySQL 常用命令速查
• MySQL 主从复制配置与延迟排查
• Java 如何启动 jar 包（连接池配置相关）

👉 相关在线工具：日志分析 · Linux 命令速查，免登录、浏览器内处理。