面试题：MySQL意向锁与行锁、表锁如何协同工作

Question

详细说明在一个复杂的事务场景中，MySQL的意向锁是怎样与行锁、表锁配合，以保证数据的一致性和并发操作的正确性，举例说明不同锁之间的加锁顺序及解锁逻辑。

Answer 1

1. MySQL锁概述

MySQL 中有多种锁机制，行锁针对一行数据进行锁定，表锁则对整个表进行锁定，意向锁是为了在获取行锁或表锁时提供一种“预告”机制，以提高并发性能。

2. 意向锁与行锁、表锁的配合

• 意向锁类型：意向锁分为意向共享锁（IS）和意向排他锁（IX）。当事务想要获取某行的共享锁（S锁）时，首先需要获取表级别的意向共享锁（IS）；当事务想要获取某行的排他锁（X锁）时，首先需要获取表级别的意向排他锁（IX）。
• 作用：意向锁的存在避免了在获取行锁时需要遍历整个表来检查是否有冲突的表锁，提高了锁获取的效率。例如，当一个事务想在表 t1 的某行加排他锁时，若表已经被加了共享表锁（S锁），由于共享锁和排他锁互斥，直接获取行排他锁会失败。但有了意向锁，在加表共享锁时，会先检查是否有意向排他锁，若有则直接失败，无需再检查具体行锁情况。

3. 加锁顺序示例

假设存在表 orders，包含字段 order_id、customer_id 等，我们以两个事务为例：

• 事务1：

  START TRANSACTION;
  -- 首先获取表级意向排他锁（IX）
  SELECT * FROM orders WHERE order_id = 1 FOR UPDATE; 
  -- 然后获取满足条件行的排他锁（X锁）
  UPDATE orders SET customer_id = 2 WHERE order_id = 1; 
  COMMIT;
  

• 事务2：

  START TRANSACTION;
  -- 事务2想要获取表级共享锁（S锁）
  SELECT * FROM orders WHERE customer_id = 1 LOCK IN SHARE MODE; 
  -- 由于事务1已经获取了表级意向排他锁（IX），事务2获取表级共享锁失败，需等待事务1释放锁
  COMMIT;
  

在这个例子中，事务1先获取表级意向排他锁，再获取行排他锁。事务2尝试获取表级共享锁时，因为事务1的意向排他锁而失败。

4. 解锁逻辑

• 解锁顺序：MySQL 遵循“先获取，后释放”的原则，即先获取的锁后释放。例如上述事务1，先获取表级意向排他锁，再获取行排他锁，那么在事务提交或回滚时，先释放行排他锁，再释放表级意向排他锁。
• 解锁时机：当事务执行 COMMIT 或 ROLLBACK 时，会释放该事务持有的所有锁。如果事务在执行过程中发生错误，默认会自动回滚并释放锁。例如，在事务1中，如果 UPDATE 语句出现错误，事务会自动回滚，释放行排他锁和表级意向排他锁，这样事务2就可以获取表级共享锁继续执行。