MySQL中的SELECT FOR UPDATE的用法

SELECT FOR UPDATE 是 MySQL 中用于行级锁的语句，主要作用是锁定查询到的行，防止其他事务修改这些行，确保数据的一致性。

基本语法

SELECT * FROM table_name WHERE condition FOR UPDATE;

使用场景

1. 悲观锁实现

在事务中先锁定数据，再进行更新操作：

START TRANSACTION;

-- 锁定符合条件的行
SELECT * FROM accounts WHERE user_id = 123 FOR UPDATE;

-- 执行更新操作（其他事务无法修改被锁定的行）
UPDATE accounts SET balance = balance - 100 WHERE user_id = 123;

COMMIT;

2. 防止库存超卖

START TRANSACTION;

-- 锁定库存记录
SELECT stock FROM products WHERE id = 1001 FOR UPDATE;

-- 检查库存并扣减
UPDATE products SET stock = stock - 1 WHERE id = 1001 AND stock > 0;

COMMIT;

重要特性

1. 事务中生效

FOR UPDATE 只在事务中有效：

-- 正确用法
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 其他操作
COMMIT;

-- 错误用法（非事务中无效）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

2. 锁等待超时

默认会一直等待锁释放，可以设置超时时间：

-- 设置锁等待超时为5秒
SET innodb_lock_wait_timeout = 5;

START TRANSACTION;
SELECT * FROM orders FOR UPDATE;

3. 锁定范围

• 有索引时：锁定符合条件的行（行级锁）
• 无索引时：可能升级为表锁
• 主键查询：锁定指定的主键行

实际应用示例

示例1：转账操作

START TRANSACTION;

-- 锁定两个账户
SELECT * FROM accounts WHERE id IN (1, 2) FOR UPDATE;

-- 执行转账
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

示例2：订单处理

START TRANSACTION;

-- 锁定用户和商品
SELECT * FROM users WHERE id = 100 FOR UPDATE;
SELECT * FROM products WHERE id = 5 FOR UPDATE;

-- 创建订单
INSERT INTO orders (user_id, product_id, quantity) 
VALUES (100, 5, 1);

COMMIT;

与其他锁的比较

注意事项

1. 性能影响

• 锁定的行越多，性能影响越大
• 可能导致死锁
• 增加锁等待时间

2. 避免死锁

-- 按相同顺序锁定表，避免死锁
-- 事务1和事务2都按 id 升序锁定
SELECT * FROM accounts WHERE id IN (1, 2, 3) ORDER BY id FOR UPDATE;

3. 锁升级

当锁定的行过多时，InnoDB 可能将行锁升级为表锁。

4. 隔离级别影响

不同隔离级别下 FOR UPDATE 的行为：

最佳实践

-- 不好：可能锁定多行
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

-- 更好：精确锁定 SELECT * FROM orders WHERE id = 1001 FOR UPDATE;

2. **尽快释放锁**
```sql
START TRANSACTION;
-- 业务操作尽量放在锁外
SET @amount = 500;

-- 锁定后立即完成更新
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - @amount WHERE id = 1;

COMMIT;  -- 立即提交释放锁

使用超时机制

SET innodb_lock_wait_timeout = 3;  -- 3秒超时

考虑替代方案

• 乐观锁（版本控制）
• Redis 分布式锁
• 消息队列削峰填谷

常见错误

-- 错误1：非事务中使用
SELECT * FROM users FOR UPDATE;  -- 自动提交，锁立即释放

-- 错误2：锁定过多行导致性能问题
SELECT * FROM large_table WHERE create_time > '2023-01-01' FOR UPDATE;

-- 错误3：没有WHERE条件（锁定全表）
SELECT * FROM users FOR UPDATE;  -- 非常危险！

SELECT FOR UPDATE 是处理并发数据更新的重要工具，但需要谨慎使用，避免过度锁定影响系统性能。