InnoDB锁机制：你的数据库为什么不会“打架”？

---

——用超市寄存柜原理讲透MySQL的并发控制

假设你开了一家电商平台，用户A和B同时抢购最后一个商品。如果数据库没有锁机制，两人可能同时下单成功，导致库存变负数——这就是著名的超卖问题。InnoDB的锁机制就像超市的寄存柜系统，完美解决这类问题。

---

一、最基础的两种锁：共享锁 vs 排他锁

1. 共享锁（S Lock）
   • 相当于“读锁”，像多人同时查阅同一份文件
   • 用法：SELECT ... LOCK IN SHARE MODE
2. 排他锁（X Lock）
   • 相当于“写锁”，像独占领更衣室
   • 用法：SELECT ... FOR UPDATE

锁兼容性：

当前锁 \ 请求锁	S锁	X锁
S锁	✅	❌
X锁	❌	❌

口诀：读读兼容，读写互斥，写写互斥

---

二、行级锁的三种实现方式

InnoDB的行锁其实是在索引上实现的，就像根据寄存柜号码锁定具体格子：

1. 记录锁（Record Lock）

-- 锁定id=5的商品
BEGIN;
SELECT * FROM products WHERE id = 5 FOR UPDATE;
-- 其他事务修改id=5的操作会被阻塞

2. 间隙锁（Gap Lock）

防止幻读的神器！锁定索引记录之间的间隙

-- 假设现有id: 1,3,7,10
BEGIN;
SELECT * FROM products WHERE id > 5 AND id < 8 FOR UPDATE;
-- 此时插入id=6的新数据会被阻塞！

3. 临键锁（Next-Key Lock）

记录锁 + 间隙锁的组合，默认行锁模式

-- 锁定范围 (3,7] + (7,10]
SELECT * FROM products WHERE id > 3 AND id <= 10 FOR UPDATE;

---

三、死锁现场：当两个事务互相“卡住”

最经典的死锁场景（附可复现代码）：

步骤1：创建测试表

CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `balance` int DEFAULT '100',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `account` (`balance`) VALUES (100), (200);

步骤2：在两个会话中按序执行

时间	会话A	会话B
t1	BEGIN; UPDATE account SET balance=0 WHERE id=1;
t2		BEGIN; UPDATE account SET balance=0 WHERE id=2;
t3	UPDATE account SET balance=200 WHERE id=2; （等待B释放锁）
t4		UPDATE account SET balance=100 WHERE id=1; 死锁产生！

死锁分析：

graph LR
  A[事务A持有id=1的锁] -->|尝试获取id=2的锁| B[事务B持有id=2的锁]
  B -->|尝试获取id=1的锁| A

解决方案：

1. 事务按固定顺序操作资源（如先id小的后id大的）
2. 设置超时：innodb_lock_wait_timeout=50
3. 主动死锁检测：innodb_deadlock_detect=ON（默认开启）

---

四、性能优化：如何减少锁冲突？

1. 缩短事务时间

   -- 反例（长事务）：
   BEGIN;
   SELECT ... FOR UPDATE;
   -- 这里进行20秒业务逻辑 --> 锁持有时间过长！
   UPDATE ... ;
   COMMIT;
   
   -- 正例：
   -- 先处理业务逻辑，最后执行事务
   START TRANSACTION;
   UPDATE ... ; -- 快速操作
   COMMIT;
   

2. 覆盖索引避免锁升级

   -- 使用索引避免全表扫描
   ALTER TABLE orders ADD INDEX idx_user(user_id);
   -- 锁定特定用户的行而非全表
   SELECT * FROM orders WHERE user_id=100 FOR UPDATE;
   

3. 拆分热点行
   把商品库存拆分成10个子库存：

   -- 原结构
   CREATE TABLE inventory (
     item_id INT PRIMARY KEY,
     stock INT
   );
   
   -- 优化后
   CREATE TABLE inventory_segment (
     item_id INT,
     seg TINYINT, -- 0~9
     stock INT,
     PRIMARY KEY(item_id, seg)
   );
   -- 更新时随机选择子段操作
   

---

五、假设案例：超卖问题的终极解决方案

-- 使用悲观锁实现库存扣减
BEGIN;
SELECT stock FROM products WHERE id=123 FOR UPDATE;

-- 检查库存
IF stock > 0 THEN
  UPDATE products SET stock=stock-1 WHERE id=123;
END IF;

COMMIT;

-- 更优方案：乐观锁（避免长事务）
UPDATE products 
SET stock=stock-1, version=version+1 
WHERE id=123 AND stock>0 AND version=#{old_version};

关键结论：

1. InnoDB通过锁机制实现ACID的"I"（隔离性）
2. 默认隔离级别RR（可重复读）使用临键锁解决幻读
3. 死锁不是bug，而是系统设计的权衡结果
4. 锁的本质：用性能换数据安全

---