MySQL 死锁与优化

死锁是并发事务相互等待对方持有资源导致的循环阻塞，影响系统可用性。

死锁产生条件

死锁示例

-- 创建示例表
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);
INSERT INTO accounts VALUES (1, 1000), (2, 1000);

-- 事务A                          事务B
START TRANSACTION;               START TRANSACTION;
UPDATE accounts SET              UPDATE accounts SET
  balance=balance-100            balance=balance-100
WHERE id=1;  -- 锁住id=1         WHERE id=2;  -- 锁住id=2

UPDATE accounts SET              UPDATE accounts SET
  balance=balance+100            balance=balance+100
WHERE id=2;  -- 等待B释放id=2    WHERE id=1;  -- 等待A释放id=1
-- 死锁！

死锁检测与处理

-- MySQL 自动检测死锁
-- innodb_deadlock_detect = ON（默认开启）

-- 检测到死锁后，回滚代价最小的事务
-- 事务B被回滚，报错：
-- ERROR 1213 (40001): Deadlock found when attempting to get lock...

-- 查看死锁信息
SHOW ENGINE INNODB STATUS;
-- 找到 LATEST DETECTED DEADLOCK 部分

手动处理死锁

-- 被回滚的事务需重新执行
START TRANSACTION;
UPDATE accounts SET balance=balance+100 WHERE id=1;
UPDATE accounts SET balance=balance-100 WHERE id=2;
COMMIT;

死锁预防策略

1. 固定加锁顺序

-- 所有事务按相同顺序加锁
-- 如：按 id 升序加锁

-- 事务A                          事务B
START TRANSACTION;               START TRANSACTION;
UPDATE accounts                  UPDATE accounts
WHERE id=1;                      WHERE id=1;  -- 等待A释放
UPDATE accounts
WHERE id=2;
COMMIT;                          -- B获得锁后继续
                                 UPDATE accounts
                                 WHERE id=2;
                                 COMMIT;
-- 无死锁

2. 减小事务范围

-- 事务尽快提交，减少锁持有时间
START TRANSACTION;
UPDATE accounts SET balance=balance-100 WHERE id=1;
COMMIT;  -- 立即提交

START TRANSACTION;
UPDATE accounts SET balance=balance+100 WHERE id=2;
COMMIT;

3. 降低隔离级别

-- RC 比 RR 锁更少，死锁概率更低
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

4. 使用乐观锁

-- 使用版本号代替悲观锁
UPDATE products
SET stock=stock-1, version=version+1
WHERE id=1 AND version=5;

-- 检查影响行数
-- 若=0，说明版本变化，需重试

5. 添加合适索引

-- 无索引时锁定全表扫描的行
CREATE INDEX idx_user_id ON orders(user_id);

-- 有索引只锁定匹配行，减少锁冲突
UPDATE orders SET status='paid' WHERE user_id=1;

锁等待超时设置

-- 设置锁等待超时时间（秒）
SET innodb_lock_wait_timeout = 50;  -- 默认50秒

-- 超时后报错：
-- ERROR 1205 (HY000): Lock wait timeout exceeded

监控死锁

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- performance_schema
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks;

-- 开启死锁日志
SET GLOBAL innodb_print_all_deadlocks = ON;

要点总结

• 死锁是循环等待资源导致的阻塞
• MySQL 自动检测死锁并回滚代价最小的事务
• 预防策略：固定加锁顺序、减小事务范围、降低隔离级别
• 使用乐观锁和合适索引减少锁冲突
• innodb_lock_wait_timeout 控制锁等待超时