MySQL锁机制与死锁排查实战
“数据库死锁了”——这句话我在线上听过无数次。
锁是MySQL保证数据一致性的核心机制，但用不好就是各种问题。这篇从原理到排查，把锁这块讲透。
MySQL锁的分类
按粒度分
| 锁类型 | 说明 | 开销 | 并发度 | 引擎 |
|---|---|---|---|---|
| 表锁 | 锁整张表 | 低 | 低 | MyISAM/InnoDB |
| 行锁 | 锁单行 | 高 | 高 | InnoDB |
| 间隙锁 | 锁区间 | 高 | 中 | InnoDB |
按模式分
| 锁类型 | 说明 | 兼容性 |
|---|---|---|
| 共享锁（S锁） | 读锁，多个事务可以同时持有 | 与S锁兼容，与X锁互斥 |
| 排他锁（X锁） | 写锁，只能有一个事务持有 | 与S锁、X锁都互斥 |
InnoDB行锁详解
行锁的三种形式
1. 记录锁（Record Lock）
锁住索引记录本身：
-- 锁住id=1这一行 SELECT * FROM users WHERE id = 1 FOR UPDATE;2. 间隙锁（Gap Lock）
锁住索引记录之间的间隙，防止幻读：
-- 假设表里有id: 1, 5, 10 -- 这条SQL会锁住(1,5)这个间隙 SELECT * FROM users WHERE id > 1 AND id < 5 FOR UPDATE;3. 临键锁（Next-Key Lock）
记录锁 + 间隙锁，默认锁类型：
-- 锁住id=5这条记录，以及(1,5]这个范围 SELECT * FROM users WHERE id = 5 FOR UPDATE;加锁规则
这块有点绕，记几个关键点：
- 唯一索引等值查询：只加记录锁（如果记录存在）
- 唯一索引范围查询：加临键锁
- 普通索引查询：加临键锁
- 无索引查询：锁全表！
-- 假设id是主键，name有普通索引 -- 情况1：唯一索引等值，只锁一行 SELECT * FROM users WHERE id = 1 FOR UPDATE; -- 情况2：普通索引等值，锁记录+间隙 SELECT * FROM users WHERE name = '张三' FOR UPDATE; -- 情况3：无索引，锁全表！ SELECT * FROM users WHERE age = 25 FOR UPDATE; -- age没索引这就是为什么要建索引的原因之一：没索引会锁表！
死锁是怎么产生的
经典场景：两个事务互相等待对方释放锁。
事务A 事务B -------------------------------------------------- BEGIN; BEGIN; UPDATE t SET x=1 WHERE id=1; -- 锁住id=1 UPDATE t SET x=2 WHERE id=2; -- 锁住id=2 UPDATE t SET x=1 WHERE id=2; -- 等待id=2的锁 UPDATE t SET x=2 WHERE id=1; -- 等待id=1的锁 -- 死锁！MySQL会检测到死锁，选择一个事务回滚。
死锁排查实战
1. 查看死锁日志
SHOW ENGINE INNODB STATUS\G找到LATEST DETECTED DEADLOCK部分：
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-12-29 10:30:45 0x7f8a12345678 *** (1) TRANSACTION: TRANSACTION 12345, 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) MySQL thread id 100, OS thread handle 123456, query id 999 updating UPDATE orders SET status = 'paid' WHERE id = 100 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table `test`.`orders` trx id 12345 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 12346, ACTIVE 3 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 101, OS thread handle 123457, query id 1000 updating UPDATE orders SET status = 'shipped' WHERE id = 101 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table `test`.`orders` trx id 12346 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 50 page no 4 n bits 72 index PRIMARY of table `test`.`orders` trx id 12346 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (1)2. 查看当前锁情况
-- MySQL 8.0+ SELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_lock_waits; -- MySQL 5.7 SELECT * FROM information_schema.innodb_locks; SELECT * FROM information_schema.innodb_lock_waits;3. 查看正在等待的事务
SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query 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;4. 查看长事务
SELECT trx_id, trx_state, trx_started, NOW() - trx_started AS duration, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx WHERE trx_state = 'RUNNING' AND NOW() - trx_started > 10 -- 超过10秒 ORDER BY trx_started;常见死锁场景及解决
场景1：相反顺序更新
-- 事务A UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 事务B UPDATE accounts SET balance = balance - 100 WHERE id = 2; UPDATE accounts SET balance = balance + 100 WHERE id = 1;解决：固定更新顺序，按id排序后更新
-- 统一按id从小到大更新 UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2;场景2：间隙锁冲突
-- 假设表里有id: 1, 10, 20 -- 事务A SELECT * FROM t WHERE id = 5 FOR UPDATE; -- 锁住(1,10) INSERT INTO t VALUES (7); -- 要插入(1,10)，等待 -- 事务B SELECT * FROM t WHERE id = 15 FOR UPDATE; -- 锁住(10,20) INSERT INTO t VALUES (8); -- 要插入(1,10)，等待A释放解决：
- 降低隔离级别到RC（没有间隙锁）
- 减少锁持有时间
- 用唯一索引避免间隙锁
场景3：唯一索引插入冲突
-- 表有唯一索引 (user_id, product_id) -- 事务A INSERT INTO cart (user_id, product_id) VALUES (1, 100); -- 加插入意向锁 -- 事务B INSERT INTO cart (user_id, product_id) VALUES (1, 100); -- 冲突，等待解决：用INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO cart (user_id, product_id, quantity) VALUES (1, 100, 1) ON DUPLICATE KEY UPDATE quantity = quantity + 1;避免死锁的最佳实践
1. 固定更新顺序
// 转账：固定按id从小到大锁 func Transfer(fromID, toID int, amount int) error { ids := []int{fromID, toID} sort.Ints(ids) tx := db.Begin() // 按顺序锁定 for _, id := range ids { tx.Exec("SELECT * FROM accounts WHERE id = ? FOR UPDATE", id) } // 执行转账 tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromID) tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toID) return tx.Commit().Error }2. 缩短事务时间
// 不好：事务里做了很多事 tx := db.Begin() result := callExternalAPI() // 调用外部接口，可能很慢 tx.Create(result) tx.Commit() // 好：尽快完成事务 result := callExternalAPI() // 事务外调用 tx := db.Begin() tx.Create(result) tx.Commit()3. 合理使用索引
-- 没索引会锁全表 UPDATE users SET status = 'active' WHERE created_at < '2024-01-01'; -- 有索引只锁需要的行 CREATE INDEX idx_created_at ON users(created_at); UPDATE users SET status = 'active' WHERE created_at < '2024-01-01';4. 降低隔离级别
-- RC级别没有间隙锁，死锁概率低 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 或者全局设置 SET GLOBAL transaction_isolation = 'READ-COMMITTED';5. 使用乐观锁
-- 用版本号避免锁 UPDATE products SET stock = stock - 1, version = version + 1 WHERE id = 1 AND version = 10; -- 检查影响行数，为0说明被别人改了，重试6. 设置锁等待超时
-- 等待超时自动放弃（默认50秒） SET innodb_lock_wait_timeout = 10;监控告警
死锁监控
-- 查看死锁次数 SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks'; -- 定时检查，增量告警 SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_deadlocks';长事务监控
-- 超过60秒的事务 SELECT * FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;锁等待监控
-- 锁等待超过5秒的 SELECT * FROM performance_schema.data_lock_waits WHERE TIMESTAMPDIFF(SECOND, REQUESTING_ENGINE_LOCK_ID, NOW()) > 5;一个真实案例
线上报警：订单支付接口频繁超时。
排查过程：
- 看慢查询日志，发现大量UPDATE卡住
SHOW ENGINE INNODB STATUS，看到死锁- 分析死锁日志：
事务1: UPDATE orders SET status='paid' WHERE order_no='A001' 事务2: UPDATE orders SET status='paid' WHERE order_no='A002'- 发现order_no没有索引，导致锁全表
- 两个事务都锁了全表，互相等待
解决：
ALTER TABLE orders ADD INDEX idx_order_no(order_no);加完索引，行锁替代表锁，问题解决。
总结
MySQL锁的几个要点：
- InnoDB行锁是锁索引，无索引会锁表
- 死锁的本质是循环等待，打破循环就能避免
- 固定更新顺序是避免死锁最有效的方法
- 事务要短，锁持有时间越短越好
- RC级别比RR少了间隙锁，死锁概率更低
遇到死锁不要慌，SHOW ENGINE INNODB STATUS看日志，分析是哪两个SQL冲突，然后针对性优化。