回归本源理解下mysql的 select for update 锁

发表于 2025-06-01 分类于 msyql ，技巧阅读次数：阅读次数： Disqus：

很多的业务场景我们会用到锁，包括各种炫酷的分布式锁，但是其实很多情况由于db的可靠性是相对比较高的，所以也可以在适当的情况下使用db来作为锁
这里就介绍下比较常用了 select for update 锁

什么是 SELECT FOR UPDATE

SELECT FOR UPDATE 是 MySQL 中一种特殊的查询语句，它在执行查询的同时对选中的行加上排他锁（Exclusive Lock），确保在当前事务结束之前，其他事务无法修改这些数据。这种机制主要用于解决并发环境下的数据一致性问题。

基本语法

1	SELECT column1, column2, ... FROM table_name WHERE condition FOR UPDATE;

工作原理

当执行 SELECT FOR UPDATE 时，MySQL 会：

加排他锁：对查询结果中的每一行数据加上排他锁（X锁）
阻塞其他事务：其他事务如果要修改被锁定的行，必须等待当前事务结束
事务结束释放：当前事务提交（COMMIT）或回滚（ROLLBACK）时，锁会自动释放

使用场景

1. 防止超卖问题

电商系统中最常见的应用场景：

-- 开始事务
START TRANSACTION;

-- 查询并锁定商品库存
SELECT stock FROM products WHERE id = 1001 FOR UPDATE;

-- 假设查询结果显示库存为 10
-- 用户购买 3 件商品
UPDATE products SET stock = stock - 3 WHERE id = 1001;

-- 提交事务
COMMIT;

这里也可以直接使用start; 开启事务，

2. 生成唯一序列号

确保序列号的唯一性：

START TRANSACTION;

-- 获取当前最大序列号并锁定
SELECT max_seq FROM sequence_table WHERE table_name = 'orders' FOR UPDATE;

-- 更新序列号
UPDATE sequence_table SET max_seq = max_seq + 1 WHERE table_name = 'orders';

-- 提交事务
COMMIT;

3. 账户余额操作

银行转账等涉及账户余额的操作：

START TRANSACTION;

-- 锁定转出账户
SELECT balance FROM accounts WHERE account_id = 'A001' FOR UPDATE;

-- 锁定转入账户
SELECT balance FROM accounts WHERE account_id = 'B001' FOR UPDATE;

-- 执行转账操作
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'A001';
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'B001';

COMMIT;

锁的范围和类型

1. 行级锁 vs 表级锁

InnoDB 存储引擎：使用行级锁，只锁定符合条件的行
MyISAM 存储引擎：使用表级锁，锁定整个表

2. 索引对锁范围的影响

-- 情况1：使用主键或唯一索引（精确锁定）
SELECT * FROM users WHERE id = 100 FOR UPDATE;  -- 只锁定 id=100 的行

-- 情况2：使用普通索引（可能锁定多行）
SELECT * FROM users WHERE age = 25 FOR UPDATE;  -- 锁定所有 age=25 的行

-- 情况3：无索引条件（锁定所有行）
SELECT * FROM users WHERE name = 'John' FOR UPDATE;  -- 可能锁定整个表

死锁问题与预防

死锁产生的原因

-- 事务 A
START TRANSACTION;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
SELECT * FROM table2 WHERE id = 2 FOR UPDATE;  -- 等待事务B释放

-- 事务 B（同时执行）
START TRANSACTION;
SELECT * FROM table2 WHERE id = 2 FOR UPDATE;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;  -- 等待事务A释放

这样就会造成死锁等待，注意一点死锁不一定是两个事务相互等待，也可以是循环等待，要达到的是竞态等待

-- 事务 A
START TRANSACTION;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
SELECT * FROM table2 WHERE id = 2 FOR UPDATE;  -- 等待事务B释放

-- 事务 B（同时执行）
START TRANSACTION;
SELECT * FROM table2 WHERE id = 2 FOR UPDATE;
SELECT * FROM table1 WHERE id = 3 FOR UPDATE;  -- 等待事务C释放

-- 事务 C（同时执行）
START TRANSACTION;
SELECT * FROM table2 WHERE id = 3 FOR UPDATE;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;  -- 等待事务A释放

这样也会造成死锁，所以不要把死锁就单纯理解为两个线程互相等待

预防死锁的策略

统一加锁顺序：所有事务按相同顺序获取锁
减少锁持有时间：尽快提交或回滚事务
使用较低的隔离级别：在业务允许的情况下
合理设计索引：避免锁定过多不必要的行

性能优化建议

1. 合理使用索引

-- 优化前：全表扫描，锁定大量行
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

-- 优化后：为 status 字段添加索引
ALTER TABLE orders ADD INDEX idx_status (status);

2. 缩小锁定范围

-- 避免：锁定所有字段
SELECT * FROM products WHERE category = 'electronics' FOR UPDATE;

-- 推荐：只选择必要字段
SELECT id, stock FROM products WHERE category = 'electronics' FOR UPDATE;

3. 合理的事务边界

-- 不推荐：事务过长
START TRANSACTION;
SELECT * FROM table1 FOR UPDATE;
-- 大量业务逻辑处理
-- 网络IO操作
UPDATE table1 SET ...;
COMMIT;

-- 推荐：缩短事务时间
-- 先处理业务逻辑
START TRANSACTION;
SELECT * FROM table1 FOR UPDATE;
UPDATE table1 SET ...;
COMMIT;

注意事项和限制

1. 必须在事务中使用

-- 错误：不在事务中，锁立即释放
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 正确：在事务中使用
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 其他操作
COMMIT;

2. 自动提交模式的影响

-- 检查自动提交状态
SHOW VARIABLES LIKE 'autocommit';

-- 如果开启了自动提交，需要显式开启事务
SET autocommit = 0;  -- 关闭自动提交
-- 或者
START TRANSACTION;   -- 显式开启事务

3. 锁等待超时

1 2	-- 设置锁等待超时时间（秒） SET innodb_lock_wait_timeout = 60;

替代方案

1. 乐观锁

-- 使用版本号实现乐观锁
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1001 AND version = @old_version;

2. 原子性操作

-- 直接使用原子性的 UPDATE
UPDATE products 
SET stock = stock - 1 
WHERE id = 1001 AND stock >= 1;

总结

SELECT FOR UPDATE 是 MySQL 中解决并发问题的重要工具，但使用时需要注意：

合理设计事务边界，避免长时间持有锁
正确使用索引，减少锁定范围
统一加锁顺序，预防死锁
根据业务场景选择合适的并发控制策略

正确使用 SELECT FOR UPDATE 可以有效保证数据一致性，但也要权衡其对系统性能的影响，在实际应用中需要根据具体业务场景做出合理的选择。