MySQL 连接数超限与死锁排查实战

概述

数据库故障是 SRE 最常见的紧急事件之一。本文记录两个高频场景的排查 SOP：连接数打满和死锁。

场景一：连接数超限（Too many connections）

现象

ERROR 1040 (08004): Too many connections
业务日志大量报错 / API 超时 / 连接池耗尽

排查 SOP

-- 1. 查看当前连接数和上限
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
-- 如果 Threads_connected ≈ max_connections → 确认被打满

-- 2. 分析连接来源（谁占了连接）
SELECT
    USER, HOST, DB, COMMAND, TIME, STATE,
    COUNT(*) AS conn_count
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST, DB
ORDER BY conn_count DESC;

-- 3. 查看哪些是长时间 Sleep 连接（连接池泄露？）
SELECT id, user, host, db, command, time, state
FROM information_schema.PROCESSLIST
WHERE command = 'Sleep' AND time > 60
ORDER BY time DESC;

-- 4. 查看哪些是长时间运行的查询
SELECT id, user, host, db, command, time, state,
       LEFT(info, 200) AS query
FROM information_schema.PROCESSLIST
WHERE time > 30 AND command != 'Sleep'
ORDER BY time DESC;

应急处理

-- 临时提高连接上限（应急，需评估内存）
SET GLOBAL max_connections = 1000;

-- 批量杀长时间 Sleep 连接（注意：可能误杀正常连接池空闲连接）
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.PROCESSLIST
WHERE command = 'Sleep' AND time > 300;

-- KILL 特定来源的连接
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.PROCESSLIST
WHERE host = '192.168.1.100';

根因分析 Checklist

• 连接池 maxActive 配置是否合理？（建议 = max_connections * 0.7）
• 应用有连接泄露？（try-finally 是否正确关闭）
• 慢查询阻塞导致连接堆积？（慢查询持有连接不放）
• 连接池 removeAbandoned 是否配置？（自动回收泄露连接）
• 是否有突发流量？需不需要限流？

// 连接池推荐配置（HikariCP）
spring.datasource.hikari.maximumPoolSize=50       # 每实例最大连接
spring.datasource.hikari.minimumIdle=10            # 最小空闲
spring.datasource.hikari.idleTimeout=300000        # 空闲超时 5min
spring.datasource.hikari.connectionTimeout=3000    # 获取连接超时 3s
spring.datasource.hikari.leakDetectionThreshold=10000  # 泄露检测 10s

场景二：死锁（Deadlock）

现象

2026-07-14 10:30:00 [ERROR] Deadlock found when trying to get lock; try restarting transaction

排查 SOP

-- 1. 查看死锁日志（最重要的一步）
SHOW ENGINE INNODB STATUS\G
-- 搜索 "LATEST DETECTED DEADLOCK" 部分
-- 会显示：
--   - 涉及的事务 (TRANSACTION 1, TRANSACTION 2)
--   - 各自持有的锁 (*** (1) HOLDS THE LOCK(S))
--   - 各自等待的锁 (*** (1) WAITING FOR THIS LOCK TO BE GRANTED)
--   - 被回滚的是哪个事务

-- 2. 查看当前锁等待（实时）
SELECT
    r.trx_id AS waiting_trx,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) AS wait_sec
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.INNODB_TRX b ON w.blocking_trx_id = b.trx_id;

-- 3. 查看当前锁（MySQL 8.0+）
SELECT * FROM performance_schema.data_locks
WHERE object_name = 'orders';
SELECT * FROM performance_schema.data_lock_waits;

经典死锁场景及解决

场景 A：转账（AB-BA 顺序）

-- 事务 A：先扣 id=1，再扣 id=2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 事务 B：先扣 id=2，再扣 id=1
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

解决：固定加锁顺序——按 id 升序

-- 统一规则：先操作 id 小的
UPDATE accounts SET balance = balance - ? WHERE id = MIN(?, ?);
UPDATE accounts SET balance = balance + ? WHERE id = MAX(?, ?);

场景 B：INSERT 唯一键冲突 + 间隙锁

-- 事务 A：
INSERT INTO orders(order_no) VALUES ('ORD001');  -- 成功，持有行锁
-- 事务 B：
INSERT INTO orders(order_no) VALUES ('ORD001');  -- 等事务 A 释放锁
-- 事务 A：ROLLBACK
-- → 事务 B 获得锁并插入成功（此时不存在死锁）
-- 但如果是 UPDATE 场景可能不同

场景 C：联合索引导致锁范围扩大

-- 假设 INDEX(status)，WHERE status = 'pending' FOR UPDATE
-- 在 RR 隔离级别下：不光锁 status='pending' 的行，还锁间隙
-- → 其他事务无法在 status='pending' 区域插入 → 锁竞争大
-- 解决：改用 RC 隔离级别（放弃间隙锁）

死锁预防

• 所有事务按相同顺序操作资源
• 选择 READ COMMITTED 隔离级别（减少间隙锁）
• 确保 WHERE 条件走索引（全表扫描 → 表锁）
• 事务尽量短小精悍（减少持锁时间）
• 热点行更新考虑乐观锁（version 字段）
• 合理设置 innodb_lock_wait_timeout（OLTP 建议 5-10s）
• 使用 SELECT ... FOR UPDATE NOWAIT 或 SKIP LOCKED（MySQL 8.0+）

乐观锁替代悲观锁

-- 悲观锁（持锁，容易死锁）
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;   -- 加锁
-- 业务判断 stock 是否足够
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- 乐观锁（无锁，失败重试）
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND stock >= 1 AND version = @old_version;
-- 如果 affected_rows = 0 → 重试

监控指标建议

-- 连接数使用率
-- (Threads_connected / max_connections) > 80% → 告警

-- 死锁频率
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';
-- 环比增长 → 告警

-- 锁等待
SELECT COUNT(*) FROM information_schema.INNODB_LOCK_WAITS;
-- > 0 持续 > 30s → 告警

-- 长事务
SELECT COUNT(*) FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;
-- > 0 → 告警

关联知识

• ../07-Knowledge/database/MySQL 事务与锁机制 — 隔离级别、锁类型、MVCC
• ../07-Knowledge/database/MySQL 体系架构与存储引擎 — 连接管理、Buffer Pool
• ../07-Knowledge/database/MySQL 索引原理与优化 — 索引影响锁范围

复盘要点

1. 是否有标准化的连接数/死锁/慢查询监控告警？
2. 应急处理 SOP 是否文档化？（KILL 连接 / 调整参数）
3. 长事务是否有自动监控和告警？
4. 连接池配置是否统一标准？是否有泄露检测？
5. 数据库设置是否纳管为 IaC？（ansible/terraform）

---

状态: 已解决 复盘日期: 2026-07-14