数据库核心术语知识库

定义

悲观锁 (Pessimistic Lock) 是一种并发控制策略，它假设冲突很可能发生，因此在读取数据时就加锁，阻止其他事务修改该数据，直到当前事务提交或回滚。

悲观锁的核心思想是：先加锁，后操作，确保在操作期间数据不会被其他事务修改。

详细笔记

核心原理

悲观锁的工作流程

sql

-- 典型的悲观锁使用模式

START TRANSACTION;

-- 步骤1:读取并加锁
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- → 获取排他锁(X锁)
-- → 其他事务的 SELECT ... FOR UPDATE 被阻塞
-- → 其他事务的 UPDATE/DELETE 被阻塞

-- 步骤2:业务逻辑处理
-- (此时数据不会被其他事务修改)

-- 步骤3:更新数据
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 步骤4:提交,释放锁
COMMIT;

锁的生命周期:

T1: START TRANSACTION;
T2: SELECT ... FOR UPDATE;  ← 获取锁
T3: 业务处理...      ← 持有锁
T4: UPDATE ...;      ← 持有锁
T5: COMMIT;      ← 释放锁

锁持有时间: T2 → T5

MySQL InnoDB 的悲观锁实现

锁的类型

1. 共享锁 (S锁,读锁)

sql

SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 特点:
-- ✅ 多个事务可以同时持有 S 锁
-- ❌ 阻止其他事务获取 X 锁
-- 适用:需要读取且防止其他事务修改

2. 排他锁 (X锁,写锁)

sql

SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 特点:
-- ❌ 阻止其他事务获取 S 锁或 X 锁
-- ✅ 独占资源
-- 适用:读取后立即更新

3. 意向锁 (Intention Lock)

表级锁,表示事务打算在行上加什么锁

IS(意向共享锁):
  - 事务打算在某些行上加 S 锁
  
IX(意向排他锁):
  - 事务打算在某些行上加 X 锁

作用:
  - 快速判断表是否可以被锁定
  - 避免逐行检查

源码分析

InnoDB 行锁的实现:

cpp

// storage/innobase/lock/lock0lock.cc

/**
 * SELECT ... FOR UPDATE 加锁
 */
void lock_clust_rec_read_check_and_lock(
  const dtuple_t* tuple,  // 搜索元组
  rec_t*    rec,  // 记录
  dict_index_t* index,  // 索引
  ulint     mode)   // 锁模式(X/S)
{
  // 1. 获取事务对象
  trx_t* trx = thr_get_trx(thr);
  
  // 2. 创建锁对象
  lock_t* lock = lock_rec_create(
    LOCK_X | LOCK_REC,  // 排他行锁
    trx,
    index,
    rec
  );
  
  // 3. 检查是否与现有锁冲突
  if (lock_has_conflicts(lock)) {
    // 冲突,进入等待队列
    lock_wait(trx, lock);
    
    // 可能被选为死锁牺牲品
    if (trx->error_state != DB_SUCCESS) {
    return trx->error_state;
    }
  }
  
  // 4. 授予锁
  lock_grant(lock);
  
  return DB_SUCCESS;
}

/**
 * 锁等待
 */
void lock_wait(trx_t* trx, lock_t* lock)
{
  // 1. 将事务加入等待队列
  lock_enqueue_wait(trx, lock);
  
  // 2. 挂起线程
  os_event_wait(trx->event);
  
  // 3. 被唤醒后(锁可用或超时)
  //  继续执行或返回错误
}

悲观锁的使用场景

场景一:账户转账

sql

DELIMITER $$
CREATE PROCEDURE transfer_money(
  IN from_account INT,
  IN to_account INT,
  IN amount DECIMAL(10, 2)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;
  
  START TRANSACTION;
  
  -- 锁定两个账户(按 ID 顺序,避免死锁)
  IF from_account < to_account THEN
    SELECT balance FROM accounts WHERE id = from_account FOR UPDATE;
    SELECT balance FROM accounts WHERE id = to_account FOR UPDATE;
  ELSE
    SELECT balance FROM accounts WHERE id = to_account FOR UPDATE;
    SELECT balance FROM accounts WHERE id = from_account FOR UPDATE;
  END IF;
  
  -- 检查余额
  IF (SELECT balance FROM accounts WHERE id = from_account) >= amount THEN
    -- 扣款
    UPDATE accounts 
    SET balance = balance - amount 
    WHERE id = from_account;
    
    -- 收款
    UPDATE accounts 
    SET balance = balance + amount 
    WHERE id = to_account;
    
    COMMIT;
  ELSE
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
  END IF;
END$$
DELIMITER ;

-- 优势:
-- ✅ 保证原子性
-- ✅ 防止并发修改
-- ✅ 无版本冲突

场景二:库存扣减

sql

START TRANSACTION;

-- 锁定商品
SELECT stock FROM products WHERE id = 1 FOR UPDATE;

-- 检查库存
IF stock > 0 THEN
  -- 扣减
  UPDATE products SET stock = stock - 1 WHERE id = 1;
  
  -- 创建订单
  INSERT INTO orders (product_id, quantity) VALUES (1, 1);
  
  COMMIT;
ELSE
  ROLLBACK;
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';
END IF;

场景三:分布式锁

sql

-- 使用数据库实现分布式锁

CREATE TABLE distributed_locks (
  lock_name VARCHAR(100) PRIMARY KEY,
  owner_id VARCHAR(50),
  acquired_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP
);

-- 获取锁
START TRANSACTION;

SELECT * FROM distributed_locks 
WHERE lock_name = 'order_processing' 
FOR UPDATE;

-- 如果不存在,插入
INSERT INTO distributed_locks (lock_name, owner_id, expires_at)
VALUES ('order_processing', 'node_1', NOW() + INTERVAL 30 SECOND)
ON DUPLICATE KEY UPDATE 
  owner_id = 'node_1',
  acquired_at = NOW(),
  expires_at = NOW() + INTERVAL 30 SECOND;

COMMIT;

-- 执行业务逻辑...

-- 释放锁
DELETE FROM distributed_locks 
WHERE lock_name = 'order_processing' 
  AND owner_id = 'node_1';

悲观锁 vs 乐观锁全面对比

特性	悲观锁	乐观锁
假设	冲突经常发生	冲突很少发生
加锁时机	读取时加锁	提交时检查
并发度	低(阻塞其他事务)	高(读不加锁)
重试机制	无需重试	需要重试
死锁风险	有	无
适用场景	写密集、高冲突	读密集、低冲突
实现复杂度	简单	复杂(需重试逻辑)
性能特点	稳定但慢	快但不稳定
典型应用	银行转账、库存扣减	文章编辑、用户资料

悲观锁的性能影响

负面影响

1. 降低并发度

sql

-- 事务 A
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 锁定
-- 长时间业务处理...
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;

-- 事务 B(并发)
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 被阻塞!
-- 等待事务 A 释放锁

-- 事务 C(并发)
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 也被阻塞!

-- 结果:串行执行,并发度低

2. 可能导致死锁

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

-- 事务 B
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;  -- 锁定 id=2
UPDATE accounts SET balance = balance + 200 WHERE id = 1;  -- 等待 id=1

-- ❌ 死锁!

3. 锁超时

sql

-- 配置锁等待超时
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 默认: 50 秒

-- 如果等待超过 50 秒
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- ERROR 1205 (HY000): Lock wait timeout exceeded

-- 应用层需要处理超时异常

正面影响

1. 确定性强

悲观锁:
  - 加锁成功 → 一定可以完成操作
  - 无重试逻辑
  - 结果可预测

乐观锁:
  - 可能失败
  - 需要重试
  - 结果不确定

2. 适合高冲突场景

秒杀活动:
  - 10000 人抢 100 个库存
  
悲观锁:
  - 事务排队执行
  - 无重试开销
  - 虽然慢,但稳定

乐观锁:
  - 大量重试
  - CPU 浪费
  - 性能更差

3. 简化应用逻辑

sql

-- 悲观锁:应用层简单
START TRANSACTION;
SELECT ... FOR UPDATE;
UPDATE ...;
COMMIT;

-- 乐观锁:应用层复杂
FOR attempt IN 1..max_retries LOOP
  SELECT ...;
  UPDATE ... WHERE version = :old_version;
  IF success THEN
    BREAK;
  END IF;
  SLEEP(...);
END LOOP;

优化悲观锁的策略

策略一:缩短锁持有时间

sql

-- ❌ 长事务,长时间持有锁
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 调用外部 API(耗时 5 秒)...
-- 复杂计算(耗时 3 秒)...
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;
-- 锁持有时间: 8+ 秒

-- ✅ 短事务,快速释放锁
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;
-- 锁持有时间: < 0.1 秒

-- 外部调用和复杂计算放在事务外

策略二:固定顺序加锁

sql

-- ❌ 无序加锁,可能死锁
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- ✅ 按 ID 顺序加锁,避免死锁
IF id1 < id2 THEN
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ELSE
  UPDATE accounts SET balance = balance - 100 WHERE id = 2;
  UPDATE accounts SET balance = balance + 100 WHERE id = 1;
END IF;

策略三:使用合适的隔离级别

sql

-- READ COMMITTED 比 REPEATABLE READ 锁更少
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;

-- RC 级别:
-- - 无间隙锁
-- - 减少死锁概率
-- - 提高并发

策略四:批量操作减少锁次数

sql

-- ❌ 逐行加锁
FOR i IN 1..1000 LOOP
  START TRANSACTION;
  SELECT ... FOR UPDATE;
  UPDATE ...;
  COMMIT;
END LOOP;

-- ✅ 批量加锁
START TRANSACTION;
SELECT ... FROM accounts WHERE id IN (1,2,...,1000) FOR UPDATE;
UPDATE accounts SET ... WHERE id IN (1,2,...,1000);
COMMIT;

监控和诊断

查看锁等待

sql

-- MySQL 8.0+
SELECT 
  r.trx_id AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query AS waiting_query,
  r.trx_wait_started AS wait_start_time,
  TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds,
  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_TRX r
INNER JOIN information_schema.INNODB_LOCK_WAITS w 
  ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_TRX b 
  ON b.trx_id = w.blocking_trx_id;

-- 输出:
-- +------------------+----------------+-----------------+---------------------+--------------+-----------------+-----------------+
-- | waiting_trx_id | waiting_thread | waiting_query | wait_start_time   | wait_seconds | blocking_trx_id | blocking_query  |
-- +------------------+----------------+-----------------+---------------------+--------------+-----------------+-----------------+
-- | 12345    | 100    | UPDATE ...  | 2024-01-15 10:30:00 | 15     | 12346     | SELECT ...  |
-- +------------------+----------------+-----------------+---------------------+--------------+-----------------+-----------------+

杀死阻塞线程

sql

-- 如果发现某个事务长时间阻塞其他事务
-- 可以手动杀死

KILL 100;  -- 杀死 thread_id = 100 的连接

-- 谨慎使用!可能导致数据不一致

实际案例

案例一:电商订单系统的锁优化

sql

-- 问题:下单接口响应时间长,频繁锁等待

-- 原始实现
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
IF stock > 0 THEN
  UPDATE products SET stock = stock - 1 WHERE id = 1;
  INSERT INTO orders (...);
  COMMIT;
ELSE
  ROLLBACK;
END IF;

-- 问题:
-- - 高峰期 1000 QPS
-- - 平均锁等待: 2 秒
-- - P99 延迟: 10 秒

-- 优化方案:Redis 预扣减 + 异步入库

-- 步骤1:Redis 扣减(无锁)
IF redis DECR stock:1 >= 0 THEN
  -- 步骤2:写入消息队列
  kafka.send('order_created', {product_id: 1, quantity: 1});
  
  -- 步骤3:立即返回
  RETURN success;
ELSE
  RETURN out_of_stock;
END IF;

-- 步骤4:消费者异步入库
@KafkaListener(topics = 'order_created')
public void processOrder(OrderEvent event) {
  START TRANSACTION;
  UPDATE products SET stock = stock - 1 WHERE id = event.productId;
  INSERT INTO orders (...);
  COMMIT;
}

-- 效果:
-- - 下单接口响应: 从 2 秒降至 50ms
-- - TPS: 从 500 提升至 5000
-- - 数据库压力: 降低 80%

案例二:银行系统的悲观锁实践

sql

-- 银行转账必须使用悲观锁

DELIMITER $$
CREATE PROCEDURE bank_transfer(
  IN from_account VARCHAR(20),
  IN to_account VARCHAR(20),
  IN amount DECIMAL(15, 2)
)
BEGIN
  DECLARE v_from_balance DECIMAL(15, 2);
  DECLARE v_to_balance DECIMAL(15, 2);
  
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    -- 记录失败日志
    INSERT INTO transfer_logs (from_acc, to_acc, amount, status, error)
    VALUES (from_account, to_account, amount, 'FAILED', SQLERRM);
    RESIGNAL;
  END;
  
  START TRANSACTION;
  
  -- 按账号排序,避免死锁
  IF from_account < to_account THEN
    SELECT balance INTO v_from_balance 
    FROM accounts WHERE account_no = from_account FOR UPDATE;
    
    SELECT balance INTO v_to_balance 
    FROM accounts WHERE account_no = to_account FOR UPDATE;
  ELSE
    SELECT balance INTO v_to_balance 
    FROM accounts WHERE account_no = to_account FOR UPDATE;
    
    SELECT balance INTO v_from_balance 
    FROM accounts WHERE account_no = from_account FOR UPDATE;
  END IF;
  
  -- 检查余额
  IF v_from_balance >= amount THEN
    -- 扣款
    UPDATE accounts 
    SET balance = balance - amount,
    updated_at = NOW()
    WHERE account_no = from_account;
    
    -- 收款
    UPDATE accounts 
    SET balance = balance + amount,
    updated_at = NOW()
    WHERE account_no = to_account;
    
    -- 记录流水
    INSERT INTO transfer_logs (from_acc, to_acc, amount, status, created_at)
    VALUES (from_account, to_account, amount, 'SUCCESS', NOW());
    
    COMMIT;
  ELSE
    ROLLBACK;
    INSERT INTO transfer_logs (from_acc, to_acc, amount, status, error, created_at)
    VALUES (from_account, to_account, amount, 'FAILED', 'INSUFFICIENT_BALANCE', NOW());
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
  END IF;
END$$
DELIMITER ;

-- 特点:
-- ✅ 强一致性
-- ✅ 无重试逻辑
-- ✅ 完整审计日志
-- ✅ 死锁预防

最佳实践

保持事务简短:锁持有时间越短越好
固定顺序加锁:预防死锁
选择合适的隔离级别:RC 比 RR 锁更少
添加合适的索引:避免锁升级
监控锁等待:设置告警阈值
设置合理超时:innodb_lock_wait_timeout = 5-10
避免在事务中调用外部服务:如 HTTP、RPC
批量操作合并锁:减少锁次数
读写分离:读操作使用快照读,不加锁
考虑乐观锁:低冲突场景下性能更好

关联术语

[[乐观锁]]
[[死锁]]
[[间隙锁]]
[[锁粒度]]
[[事务隔离级别]]

参考资料

MySQL 官方文档: InnoDB Locking
InnoDB 源码: storage/innobase/lock/lock0lock.cc
《高性能 MySQL》第 7 章:事务与锁
Microsoft Docs: Pessimistic Locking

定义 ​

详细笔记 ​

核心原理 ​

悲观锁的工作流程 ​

MySQL InnoDB 的悲观锁实现 ​

锁的类型 ​

源码分析 ​

悲观锁的使用场景 ​

场景一:账户转账 ​

场景二:库存扣减 ​

场景三:分布式锁 ​

悲观锁 vs 乐观锁全面对比 ​

悲观锁的性能影响 ​

负面影响 ​

正面影响 ​

优化悲观锁的策略 ​

策略一:缩短锁持有时间 ​

策略二:固定顺序加锁 ​

策略三:使用合适的隔离级别 ​

策略四:批量操作减少锁次数 ​

监控和诊断 ​

查看锁等待 ​

杀死阻塞线程 ​

实际案例 ​

案例一:电商订单系统的锁优化 ​

案例二:银行系统的悲观锁实践 ​

最佳实践 ​

关联术语 ​

参考资料 ​

定义