MySQL 表锁在查询更新中的实际案例

MySQL 表锁在查询更新中的实际案例

下面我将通过一个电商系统的库存管理案例，演示如何使用MySQL表锁(LOCK TABLES WRITE/READ)来保证数据一致性，并解释表锁的作用。

案例背景

假设我们有一个电商系统，包含以下表结构：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    stock INT,  -- 库存数量
    price DECIMAL(10,2)
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    quantity INT,
    order_time DATETIME
);

问题场景：库存超卖问题

在高并发情况下，多个用户同时购买同一商品时，可能出现库存超卖问题（库存减为负数）。

无锁情况下的问题演示

会话1：

-- 查询当前库存(假设id=1的商品库存为10)
SELECT stock FROM products WHERE id = 1;  -- 返回10

-- 此时不立即下单，等待几秒

会话2：

-- 同时查询相同商品
SELECT stock FROM products WHERE id = 1;  -- 也返回10

-- 下单购买5件
UPDATE products SET stock = stock - 5 WHERE id = 1;

会话1继续：

-- 基于之前查询的库存10下单购买8件
UPDATE products SET stock = stock - 8 WHERE id = 1;
-- 最终库存变为-3(超卖)

解决方案：使用表锁保证一致性

方案1：使用 WRITE 锁（排他锁）

会话1：

-- 获取写锁(阻塞其他所有访问)
LOCK TABLES products WRITE;

-- 查询库存
SELECT stock FROM products WHERE id = 1;  -- 返回10

-- 下单购买8件
UPDATE products SET stock = stock - 8 WHERE id = 1;  -- 库存变为2

-- 提交事务并释放锁
UNLOCK TABLES;

会话2（同时尝试访问）：

-- 尝试查询会被阻塞，直到会话1释放锁
SELECT stock FROM products WHERE id = 1;  -- 挂起等待

-- 只有当会话1 UNLOCK TABLES 后才会执行
-- 此时看到的是更新后的库存2

方案2：使用 READ 锁（共享锁） + WRITE 锁

库存检查阶段（多个会话可同时读）：

LOCK TABLES products READ;
SELECT stock FROM products WHERE id = 1;  -- 返回10
UNLOCK TABLES;

下单阶段（独占访问）：

LOCK TABLES products WRITE;
-- 再次检查库存(防止其他会话已修改)
SELECT stock FROM products WHERE id = 1;
-- 如果库存足够则下单
UPDATE products SET stock = stock - 5 WHERE id = 1;
UNLOCK TABLES;

表锁的作用解释

1. WRITE 锁的作用：

   • 确保同一时间只有一个会话能修改表数据
   • 阻塞其他所有读写操作，防止脏读和并发修改
   • 适合需要原子性更新的场景（如库存扣减）

2. READ 锁的作用：

   • 允许多个会话同时读取数据
   • 阻塞写操作但不阻塞读操作
   • 适合需要一致性读取但不修改的场景

3. 锁的时序保证：

   时间线:
   会话1 LOCK WRITE -> 会话1 读写 -> 会话1 UNLOCK 
               ↘
                会话2 LOCK WRITE -> 会话2 读写 -> 会话2 UNLOCK
   

实际生产建议

1. 对于InnoDB表，更推荐使用行级锁：

   START TRANSACTION;
   SELECT stock FROM products WHERE id = 1 FOR UPDATE;  -- 获取行级排他锁
   UPDATE products SET stock = stock - 3 WHERE id = 1;
   COMMIT;
   

2. 表锁适合的场景：

   • MyISAM表（不支持行锁）
   • 需要锁定整个表的批量操作
   • 特殊情况下的维护操作

3. 注意事项：

   • 锁定时长应尽可能短
   • 避免交叉锁定多个表（可能引起死锁）
   • 生产环境应先测试锁对并发性能的影响

通过这个案例可以看到，表锁能有效解决并发修改导致的数据一致性问题，但需要权衡其对系统并发性能的影响。