一文带你了解MySQL中常见锁（附案例）

-- 初始库存，为了方便理解添加几条数据  
INSERT INTO products (product_id, stock, version) VALUES (1, 100, 0);  
  
-- 事务A  
START TRANSACTION;  
  
-- 读取商品库存和版本号  
SELECT stock, version FROM products WHERE product_id = 1;  
-- 假设此时读取到 stock=100, version=0  
  
-- 稍后进行库存减少操作（此时其他事务未修改）  
UPDATE products  
SET stock = stock - 1, version = version + 1  
WHERE product_id = 1 AND version = 0;  
  
-- 检查是否更新成功  
IF ROW_COUNT() = 1 THEN  
    COMMIT;  
ELSE  
    ROLLBACK;  
END IF;  
  
-- 事务B（并发执行）  
START TRANSACTION;  
  
-- 读取商品库存和版本号（在同一时间，也读取到 stock=100, version=0）  
SELECT stock, version FROM products WHERE product_id = 1;  
  
-- 尝试减少库存（但此时version已不匹配，因为事务A它已经提交了）  
UPDATE products  
SET stock = stock - 1, version = version + 1  
WHERE product_id = 1 AND version = 0;  
  
-- 由于version不匹配，更新失败，事务B回滚  
IF ROW_COUNT() = 0 THEN  
    ROLLBACK;  
END IF;

-- 初始订单状态  
INSERT INTO orders (order_id, status) VALUES (1, 'pending');  
  
-- 事务A  
START TRANSACTION;  
  
-- 查询订单状态并加锁 这里的FOR UPDATE表示枷锁
SELECT order_id, status FROM orders WHERE order_id = 1 FOR UPDATE;  
  
-- 执行一些业务逻辑，如检查库存、支付验证等 里略过
  
-- 更新订单状态为处理中  
UPDATE orders SET status = 'processing' WHERE order_id = 1;  
  
COMMIT;  

-- 如果在事务A执行期间，事务B尝试更新同一订单的状态，它需要等待事务A提交或回滚后才能继续。

-- 假设需要修改orders表的结构  
ALTER TABLE orders ADD COLUMN delivery_date DATE;  
-- 在执行此操作时，orders表被锁定，其他事务无法访问。

-- test_table表有一个自增主键id，当前最大值为5  
START TRANSACTION;  
  
-- 锁定id大于5的所有记录之间的间隙（任何将来可能插入的id值）  
SELECT * FROM test_table WHERE id > 5 FOR UPDATE;  
  
-- 此时，如果另一个事务尝试插入id=6的记录，它将被阻塞，直到当前事务提交或回滚。  
  
-- 提交事务  
COMMIT;

+----+-----------+  
| id | name      |  
+----+-----------+  
|  1 | Alice     |  
|  3 | Charlie   |  
|  5 | Eve       |  
+----+-----------+

START TRANSACTION;  
  
-- 锁定id=3的记录及其之前的间隙（防止插入id=2，但允许插入id>3且id<5的值）  
-- 同时也锁定id=3之后的间隙（防止插入id=3的重复值，但这不是Next-Key Locks的直接作用，而是行锁的效果）  
SELECT * FROM employees WHERE id = 3 FOR UPDATE;   
COMMIT;

-- 尝试插入id=2的记录（被阻塞，因为事务A的Next-Key Locks锁定了id=2的间隙）  
INSERT INTO employees (id, name) VALUES (2, 'Bob'); -- 等待或者失败 
  
-- 尝试插入id=4的记录（成功，因为id=4的间隙没有被事务A的Next-Key Locks直接锁定）  
-- 但是需要注意，如果事务A之后尝试锁定id=4或其间隙（通过另一个查询），则这个插入可能会受到影响  
INSERT INTO employees (id, name) VALUES (4, 'David'); -- 可能成功，但是取决于事务A的后续操作  
  
-- 尝试更新id=3的记录（被阻塞，因为事务A对id=3的行加了行锁）  
UPDATE employees SET name = 'Charlie Brown' WHERE id = 3; -- 等待事务A提交或回滚