数据库锁机制与并发控制详解

题目描述
数据库锁机制是保证数据并发访问时一致性、隔离性的核心技术。当多个事务同时操作同一数据时，锁能有效协调访问顺序，避免脏读、丢失更新等问题。本知识点涵盖锁的分类、封锁协议、死锁处理及多版本并发控制（MVCC）原理。

一、为什么需要锁？
假设事务T1和T2同时修改账户余额（初始为100元）：

• T1：存入50元 → balance=150
• T2：取出30元 → balance=70
  若不加锁，可能发生以下冲突：

1. 脏读：T1修改未提交时T2读取到150，但T1回滚后T2基于错误数据计算
2. 丢失更新：T1和T2同时读取100，分别计算150和70，后提交的操作会覆盖前一个

二、锁的基本类型

1. 共享锁（S锁）

   • 用于读操作，允许多个事务同时获取同一数据的S锁
   • 语法示例（SQL Server）：SELECT * FROM table WITH (SHAREDLOCK)

2. 排他锁（X锁）

   • 用于写操作，禁止其他事务获取任何锁
   • 特性：X锁与任何锁都不兼容（包括其他X锁和S锁）

3. 兼容性矩阵

      | S锁  | X锁
--------------
 S锁  | 兼容  | 不兼容
 X锁  | 不兼容 | 不兼容

三、封锁协议与粒度

1. 两阶段封锁协议（2PL）

   • 增长阶段：事务只能不断获取新锁，不能释放锁
   • 收缩阶段：事务只能释放已有锁，不能获取新锁
     示例：

   T1: S-lock(A) → Read(A) → X-lock(B) → Write(B) → Unlock(A) → Unlock(B)  // 违反2PL
   T2: S-lock(A) → Read(A) → X-lock(B) → Write(B) → Unlock(B) → Unlock(A)  // 符合2PL
   

2. 锁粒度升级

   • 行级锁：精度高但开销大（如MySQL InnoDB）
   • 表级锁：效率高但并发性差（如MyISAM）
   • 意向锁（Intention Lock）：快速判断表是否被锁定
     • IS锁：意向共享锁（预示要在行加S锁）
     • IX锁：意向排他锁（预示要在行加X锁）

四、死锁处理机制

1. 死锁产生条件

   • 互斥条件：资源只能被一个事务占用
   • 占有且等待：事务持有资源的同时申请新资源
   • 不可剥夺：资源只能由持有者释放
   • 循环等待：T1等待T2占用的资源，T2同时等待T1的资源

2. 解决方案

   • 预防策略：一次性申请所有资源（破坏等待条件）
   • 检测与恢复：
     1. 通过等待图（Wait-for Graph）检测环路
     2. 选择代价最小的事务回滚（如MySQL根据undo日志量判断）
   • 超时机制：设置锁等待超时时间（如innodb_lock_wait_timeout）

五、多版本并发控制（MVCC）
以InnoDB为例的无锁读实现：

1. 核心机制：

   • 每条记录包含隐藏字段：DB_TRX_ID（最后修改事务ID）、DB_ROLL_PTR（回滚指针）
   • 通过ReadView判断数据版本对当前事务的可见性

2. 快照读流程：

   • 查询时优先判断DB_TRX_ID：
     • 如果ID小于当前活跃事务列表的最小值，说明在事务开始前已提交→可见
     • 如果ID大于当前事务ID→不可见（通过回滚日志找旧版本）
   • 示例：事务T3（ID=300）读取数据时，若记录TRX_ID=200（已提交）则直接返回，若TRX_ID=400（未提交）则通过回滚指针找到TRX_ID=150的版本

六、实践应用场景

1. 悲观锁（先加锁再访问）

   BEGIN;
   SELECT * FROM account WHERE id=1 FOR UPDATE;  -- 加X锁
   UPDATE account SET balance=balance-100 WHERE id=1;
   COMMIT;
   

2. 乐观锁（冲突检测时处理）

   UPDATE account 
   SET balance=200, version=version+1 
   WHERE id=1 AND version=5; -- 通过版本号避免更新丢失
   

关键总结

• 锁机制本质是在并发效率与数据一致性间寻找平衡
• MVCC通过版本控制减少锁竞争，提高读并发性能
• 死锁需结合超时机制与回滚策略综合处理