数据库核心术语知识库

定义

死锁 (Deadlock) 是指两个或多个事务在执行过程中,因争夺资源而造成的一种互相等待的现象。若无外力作用,这些事务都将无法向前推进。

死锁的四个必要条件:

互斥条件:资源一次只能被一个事务持有
占有并等待:事务持有资源的同时等待其他资源
不可抢占:已分配的资源不能被强制剥夺
循环等待:存在一个事务等待环

详细笔记

核心原理

死锁示例

sql

-- 时间点 T1: 事务 A 锁定资源 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 锁定 id=1

-- 时间点 T2: 事务 B 锁定资源 2
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;  -- 锁定 id=2

-- 时间点 T3: 事务 A 等待资源 2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待事务 B 释放 id=2

-- 时间点 T4: 事务 B 等待资源 1
UPDATE accounts SET balance = balance + 200 WHERE id = 1;  -- 等待事务 A 释放 id=1

-- 结果: 死锁!❌
-- 事务 A 等待事务 B,事务 B 等待事务 A
-- 形成循环等待

图解死锁:

事务 A          事务 B
  |           |
  |-- 锁定 id=1 ---------------->|
  |           |-- 锁定 id=2
  |           |
  |-- 等待 id=2 (阻塞) ---------->|
  |           |-- 等待 id=1 (阻塞)
  |           |
  ↓           ↓
  循环等待 → 死锁!

InnoDB 的死锁检测

等待图(Wait-For Graph)

InnoDB 使用等待图来检测死锁:

等待图结构:
- 节点: 事务
- 边: 事务 A → 事务 B 表示"A 等待 B 持有的锁"

死锁检测算法:
1. 构建等待图
2. 检测图中是否存在环
3. 如果存在环 → 死锁
4. 选择一个事务作为牺牲品回滚

源码实现:

cpp

// storage/innobase/lock/lock0wait.cc

/**
 * 死锁检测
 * 
 * @param trx   当前等待的事务
 * @return    是否检测到死锁
 */
bool lock_deadlock_check(trx_t* trx)
{
  // 1. 构建等待图
  WaitGraph graph;
  build_wait_for_graph(&graph);
  
  // 2. 深度优先搜索检测环
  if (dfs_detect_cycle(graph, trx)) {
    // 3. 找到环,确认死锁
    
    // 4. 选择牺牲品(通常选择回滚成本最低的事务)
    trx_t* victim = select_deadlock_victim(graph);
    
    // 5. 回滚牺牲品事务
    trx_rollback(victim);
    
    return true;  // 检测到死锁
  }
  
  return false;  // 无死锁
}

/**
 * DFS 检测环
 */
bool dfs_detect_cycle(WaitGraph& graph, trx_t* start)
{
  std::set<trx_t*> visited;
  std::set<trx_t*> rec_stack;
  
  return dfs_util(graph, start, visited, rec_stack);
}

bool dfs_util(WaitGraph& graph, trx_t* node, 
      std::set<trx_t*>& visited, 
      std::set<trx_t*>& rec_stack)
{
  // 标记当前节点
  visited.insert(node);
  rec_stack.insert(node);
  
  // 遍历所有等待的事务
  for (trx_t* neighbor : graph.get_waiting_transactions(node)) {
    // 未访问过
    if (visited.find(neighbor) == visited.end()) {
    if (dfs_util(graph, neighbor, visited, rec_stack)) {
      return true;  // 找到环
    }
    }
    // 已在递归栈中,说明有环
    else if (rec_stack.find(neighbor) != rec_stack.end()) {
    return true;  // 找到环!
    }
  }
  
  // 从递归栈中移除
  rec_stack.erase(node);
  return false;
}

死锁超时

sql

-- MySQL 配置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 默认: 50 秒

-- 如果等待超过 50 秒,即使没有检测到死锁,也会超时回滚
SET innodb_lock_wait_timeout = 10;  -- 降低超时时间

常见的死锁场景

场景一:交叉更新

sql

-- 最典型的死锁场景

-- 事务 A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待
COMMIT;

-- 事务 B(并发执行)
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;
UPDATE accounts SET balance = balance + 200 WHERE id = 1;  -- 等待
COMMIT;

-- 结果: 死锁!

解决方案:固定顺序加锁

sql

-- ✅ 推荐:总是按相同顺序访问资源

-- 事务 A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 先 id=1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 后 id=2
COMMIT;

-- 事务 B
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;  -- 先 id=1
UPDATE accounts SET balance = balance + 200 WHERE id = 2;  -- 后 id=2
COMMIT;

-- 效果:
-- - 事务 B 会等待事务 A 释放 id=1
-- - 不会形成循环等待
-- - 避免死锁

场景二:间隙锁死锁

sql

-- InnoDB RR 级别下的间隙锁死锁

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO users VALUES (1, 'Alice'), (3, 'Charlie'), (5, 'Eve');

-- 事务 A
START TRANSACTION;
SELECT * FROM users WHERE id = 2 FOR UPDATE;  -- 锁定间隙 (1, 3)

-- 事务 B
START TRANSACTION;
SELECT * FROM users WHERE id = 4 FOR UPDATE;  -- 锁定间隙 (3, 5)

-- 事务 A
INSERT INTO users VALUES (2, 'Bob');  -- 等待事务 B 释放间隙 (3, 5)? 

-- 事务 B
INSERT INTO users VALUES (4, 'David');  -- 等待事务 A 释放间隙 (1, 3)?

-- 结果: 可能死锁!

原因分析:

InnoDB Next-Key Lock = 记录锁 + 间隙锁

事务 A: SELECT ... WHERE id = 2 FOR UPDATE
  → 锁定范围: (1, 3) 的间隙
  
事务 B: SELECT ... WHERE id = 4 FOR UPDATE
  → 锁定范围: (3, 5) 的间隙
  
事务 A: INSERT id = 2
  → 需要检查是否与事务 B 的锁冲突
  
事务 B: INSERT id = 4
  → 需要检查是否与事务 A 的锁冲突
  
→ 可能形成循环等待

场景三:外键约束死锁

sql

-- 父表
CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

-- 子表
CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  dept_id INT,
  emp_name VARCHAR(50),
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 事务 A
START TRANSACTION;
INSERT INTO departments VALUES (1, '技术部');
INSERT INTO employees VALUES (100, 1, '张三');  -- 需要检查外键,等待事务 B

-- 事务 B
START TRANSACTION;
INSERT INTO departments VALUES (2, '市场部');
INSERT INTO employees VALUES (200, 2, '李四');  -- 需要检查外键,等待事务 A

-- 结果: 可能死锁!

原因:

插入子表记录时需要检查外键约束
检查过程需要在父表上加共享锁
如果两个事务相互等待对方的父表锁,就会死锁

场景四:唯一索引冲突死锁

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);

-- 事务 A
START TRANSACTION;
INSERT INTO users VALUES (1, 'test@example.com');

-- 事务 B
START TRANSACTION;
INSERT INTO users VALUES (2, 'test@example.com');  -- 等待唯一索引锁

-- 事务 A
COMMIT;  -- 提交成功

-- 事务 B
-- 收到错误: Duplicate entry 'test@example.com'
-- 不是死锁,是唯一约束冲突

但如果两个事务同时插入不同的值,然后交换再插入:

sql

-- 事务 A
START TRANSACTION;
INSERT INTO users VALUES (1, 'a@example.com');
INSERT INTO users VALUES (2, 'b@example.com');  -- 可能等待

-- 事务 B
START TRANSACTION;
INSERT INTO users VALUES (2, 'c@example.com');  -- 等待主键锁
INSERT INTO users VALUES (1, 'd@example.com');  -- 等待主键锁

-- 结果: 可能死锁!

监控和诊断死锁

查看死锁日志

sql

SHOW ENGINE INNODB STATUS\G

-- 输出包含:
-- ------------------------
-- LATEST DETECTED DEADLOCK
-- ------------------------
-- 2024-01-15 10:30:00
-- *** (1) TRANSACTION:
-- TRANSACTION 12345, ACTIVE 5 sec starting index read
-- mysql tables in use 1, locked 1
-- LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
-- MySQL thread id 100, OS thread handle 1234, query id 5678 localhost root updating
-- UPDATE accounts SET balance = balance + 100 WHERE id = 2
-- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-- RECORD LOCKS space id 5 page no 3 n bits 72 index PRIMARY of table `test`.`accounts` trx id 12345 lock_mode X locks rec but not gap
-- 
-- *** (2) TRANSACTION:
-- TRANSACTION 12346, ACTIVE 3 sec starting index read
-- mysql tables in use 1, locked 1
-- 2 lock struct(s), heap size 1136, 1 row lock(s)
-- MySQL thread id 101, OS thread handle 5678, query id 9012 localhost root updating
-- UPDATE accounts SET balance = balance + 200 WHERE id = 1
-- *** (2) HOLDS THE LOCK(S):
-- RECORD LOCKS space id 5 page no 3 n bits 72 index PRIMARY of table `test`.`accounts` trx id 12346 lock_mode X locks rec but not gap
-- 
-- *** WE ROLL BACK TRANSACTION (2)

关键信息:

涉及的事务 ID
等待的锁
持有的锁
被回滚的事务(牺牲品)

Performance Schema

sql

-- 查看锁等待
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
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

-- MySQL 8.0+ 使用新的系统表
SELECT 
  OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_STATUS,
  LOCK_DATA
FROM performance_schema.data_locks;

死锁统计

sql

-- 查看死锁发生次数
SHOW STATUS LIKE 'Innodb_deadlocks';

-- 输出:
-- +------------------+-------+
-- | Variable_name  | Value |
-- +------------------+-------+
-- | Innodb_deadlocks | 123 |
-- +------------------+-------+

-- 如果该值持续增长,说明系统频繁出现死锁
-- 需要优化事务逻辑

预防和解决死锁

策略一:固定顺序访问资源

sql

-- ✅ 最佳实践:总是按相同顺序访问表或行

-- 定义访问顺序规则:
-- 1. 按主键从小到大
-- 2. 按表名字典序

-- 示例:批量更新多个账户
DELIMITER $$
CREATE PROCEDURE batch_update_accounts(
  IN account_ids TEXT  -- 逗号分隔的 ID 列表
)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE acct_id INT;
  
  -- 排序后的游标
  DECLARE cur CURSOR FOR 
    SELECT id FROM (
    SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(account_ids, ',', n), ',', -1) AS UNSIGNED) AS id
    FROM (
      SELECT a.N + b.N * 10 + 1 AS n
      FROM 
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ) numbers
    WHERE n <= 1 + (LENGTH(account_ids) - LENGTH(REPLACE(account_ids, ',', '')))
    ) ids
    ORDER BY id;  -- ⭐ 关键:排序
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  START TRANSACTION;
  
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO acct_id;
    IF done THEN
    LEAVE read_loop;
    END IF;
    
    UPDATE accounts SET balance = balance - 10 WHERE id = acct_id;
  END LOOP;
  
  CLOSE cur;
  COMMIT;
END$$
DELIMITER ;

-- 无论传入什么顺序的 ID,都会按从小到大顺序更新
-- 避免死锁

策略二:降低隔离级别

sql

-- READ COMMITTED 比 REPEATABLE READ 更少使用间隙锁
-- 可以减少死锁概率

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
-- 执行业务逻辑
COMMIT;

注意: 降低隔离级别可能带来其他问题(如幻读),需权衡利弊。

策略三:添加合适的索引

sql

-- 缺少索引可能导致锁升级,增加死锁风险

-- ❌ 无索引,全表扫描,锁整个表
UPDATE accounts SET balance = balance - 100 WHERE email = 'test@example.com';

-- ✅ 添加索引,行级锁
CREATE INDEX idx_email ON accounts(email);
UPDATE accounts SET balance = balance - 100 WHERE email = 'test@example.com';

策略四:使用乐观锁

sql

-- 基于版本号的乐观锁,避免长时间持有排他锁

CREATE TABLE products (
  id INT PRIMARY KEY,
  stock INT,
  version INT DEFAULT 0
);

-- 查询时读取版本号
SELECT stock, version FROM products WHERE id = 1;
-- 读到: stock = 100, version = 5

-- 更新时检查版本号
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 5;

-- 如果影响行数为 0,说明版本号已变化,重试

优点:

不需要长时间持有排他锁
减少死锁概率
适合读多写少场景

缺点:

高并发下重试率高
应用层需要处理重试逻辑

策略五:设置合理的超时时间

sql

-- 降低锁等待超时时间,快速失败
SET SESSION innodb_lock_wait_timeout = 5;  -- 5 秒

-- 应用层捕获超时异常,重试
try {
  executeTransaction();
} catch (LockWaitTimeoutException e) {
  // 重试逻辑
  retry();
}

策略六:大事务拆分为小事务

sql

-- ❌ 大事务,长时间持有锁
START TRANSACTION;
FOR i IN 1..10000 LOOP
  UPDATE orders SET status = 1 WHERE id = i;
END LOOP;
COMMIT;

-- ✅ 分批提交
FOR batch_start IN 1..10000 STEP 1000 LOOP
  START TRANSACTION;
  UPDATE orders SET status = 1 
  WHERE id >= batch_start AND id < batch_start + 1000;
  COMMIT;
END LOOP;

死锁 vs 锁等待

特性	死锁	锁等待
定义	循环等待,都无法继续	单向等待,一方持有,一方等待
检测	InnoDB 自动检测	超时机制
解决	回滚其中一个事务	等待持有者释放或超时
错误码	1213 (ER_LOCK_DEADLOCK)	1205 (ER_LOCK_WAIT_TIMEOUT)
预防	固定顺序、降低隔离级别	缩短事务、添加索引

实际案例

案例一:电商库存扣减死锁

sql

-- 问题:高峰期频繁死锁

-- 原始代码(应用层)
def place_order(user_id, product_ids):
  start_transaction()
  
  # 随机顺序扣减库存
  for product_id in product_ids:  # ❌ 无序
    stock = SELECT stock FROM products WHERE id = product_id FOR UPDATE
    if stock > 0:
    UPDATE products SET stock = stock - 1 WHERE id = product_id
  
  create_order()
  commit()

-- 问题:
-- 用户 A: 扣减产品 [1, 2, 3]
-- 用户 B: 扣减产品 [3, 2, 1]
-- → 可能死锁!

# 修复:排序后扣减
def place_order_fixed(user_id, product_ids):
  start_transaction()
  
  # ✅ 排序后再扣减
  for product_id in sorted(product_ids):
    stock = SELECT stock FROM products WHERE id = product_id FOR UPDATE
    if stock > 0:
    UPDATE products SET stock = stock - 1 WHERE id = product_id
  
  create_order()
  commit()

案例二:银行转账死锁优化

sql

-- 原始实现(可能死锁)
DELIMITER $$
CREATE PROCEDURE transfer_old(
  from_account INT,
  to_account INT,
  amount DECIMAL(10, 2)
)
BEGIN
  START TRANSACTION;
  
  -- 无序访问
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;
  
  COMMIT;
END$$
DELIMITER ;

-- 优化实现(固定顺序)
DELIMITER $$
CREATE PROCEDURE transfer_new(
  from_account INT,
  to_account INT,
  amount DECIMAL(10, 2)
)
BEGIN
  START TRANSACTION;
  
  -- ✅ 按 ID 从小到大访问
  IF from_account < to_account THEN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
  ELSE
    UPDATE accounts SET balance = balance - amount WHERE id = to_account;
    UPDATE accounts SET balance = balance + amount WHERE id = from_account;
  END IF;
  
  COMMIT;
END$$
DELIMITER ;

-- 效果:
-- - 死锁率从每天 100+ 次降至 0
-- - 吞吐量提升 30%

最佳实践

固定顺序访问资源:最重要的死锁预防策略
保持事务简短:减少锁持有时间
选择合适的隔离级别:RC 比 RR 更少死锁
添加合适的索引:避免锁升级
使用乐观锁:适合读多写少场景
监控死锁日志:定期分析 SHOW ENGINE INNODB STATUS
设置合理超时:快速失败,及时重试
大事务拆分:批量操作分批提交
避免在事务中与用户交互:如等待输入、调用外部 API
测试并发场景:压力测试中发现潜在死锁

关联术语

[[ACID]]
[[锁粒度]]
[[间隙锁]]
[[事务隔离级别]]
[[MVCC]]

参考资料

MySQL 官方文档: Deadlock Detection
InnoDB 源码: storage/innobase/lock/lock0wait.cc
《高性能 MySQL》第 7 章:事务与锁
Oracle Docs: Deadlocks

定义 ​

详细笔记 ​

核心原理 ​

死锁示例 ​

InnoDB 的死锁检测 ​

等待图(Wait-For Graph) ​

死锁超时 ​

常见的死锁场景 ​

场景一:交叉更新 ​

场景二:间隙锁死锁 ​

场景三:外键约束死锁 ​

场景四:唯一索引冲突死锁 ​

监控和诊断死锁 ​

查看死锁日志 ​

Performance Schema ​

死锁统计 ​

预防和解决死锁 ​

策略一:固定顺序访问资源 ​

策略二:降低隔离级别 ​

策略三:添加合适的索引 ​

策略四:使用乐观锁 ​

策略五:设置合理的超时时间 ​

策略六:大事务拆分为小事务 ​

死锁 vs 锁等待 ​

实际案例 ​

案例一:电商库存扣减死锁 ​

案例二:银行转账死锁优化 ​

最佳实践 ​

关联术语 ​

参考资料 ​

定义