跳至主要內容

深入理解 SELECT ... FOR UPDATE：确保数据库并发操作的一致性

zheng大约 14 分钟数据库mysql

引言

在多用户、高并发的Web应用和分布式系统中，确保数据库操作的一致性和完整性是至关重要的。当多个事务同时尝试修改同一数据时，可能会导致数据不一致、脏读、不可重复读或幻读等问题。为了应对这些挑战，SQL 提供了 SELECT ... FOR UPDATE 语句，它可以在查询的同时锁定所选的行，防止其他事务对其进行修改，直到当前事务提交或回滚。
本文将深入探讨 SELECT ... FOR UPDATE 的工作原理、使用场景、最佳实践以及潜在的风险，并通过实际案例帮助读者更好地理解和应用这一功能。

一、SELECT ... FOR UPDATE 的基本概念

SELECT ... FOR UPDATE 是 SQL 中的一种锁机制，主要用于在事务中对查询结果中的行进行排他性锁定。这意味着，在当前事务提交或回滚之前，其他事务无法对这些行进行修改（如 UPDATE 或 DELETE 操作），从而确保数据的一致性和完整性。
语法：

SELECT column1, column2, ... FROM table_name WHERE condition FOR UPDATE;
  • FOR UPDATE 子句会为查询结果中的每一行添加一个排他锁。
  • 锁定的行只能被当前事务修改，其他事务必须等待当前事务完成。
  • 锁会在事务结束时自动释放（即 COMMIT 或 ROLLBACK）。

二、SELECT ... FOR UPDATE 的工作原理

SELECT ... FOR UPDATE 主要依赖于数据库的事务隔离级别和锁机制。它通常用于支持行级锁的存储引擎（如 MySQL 的 InnoDB），并且在以下情况下特别有用：

  1. 防止脏读：其他事务不能读取未提交的数据。
  2. 防止不可重复读：在同一事务中多次查询同一行时，结果保持一致。
  3. 防止幻读：在同一事务中多次查询同一范围的行时，结果集不会因为其他事务的插入或删除而改变。
    注意：SELECT ... FOR UPDATE 只能在事务中使用，且必须显式地开始事务（如 START TRANSACTION 或 BEGIN），并在事务结束时提交（COMMIT）或回滚（ROLLBACK）。

三、SELECT ... FOR UPDATE 的常见使用场景

1. 库存管理

在电商系统中，库存管理是一个典型的并发问题。假设多个用户同时购买同一商品，可能会导致库存超卖（即库存显示的剩余数量小于实际可售数量）。通过 SELECT ... FOR UPDATE 锁定库存记录，可以确保每次扣减库存时只有一个事务能够成功执行，避免超卖现象。
示例代码：

START TRANSACTION;
-- 锁定商品库存
SELECT * FROM products WHERE product_id = 123 FOR UPDATE;
-- 检查库存是否足够
IF (库存足够) THEN
    -- 扣减库存
    UPDATE products SET stock = stock - 1 WHERE product_id = 123;
ELSE
    ROLLBACK;  -- 库存不足，回滚事务
END IF;
COMMIT;

2. 银行转账

在银行系统中，转账操作需要确保账户余额的一致性。如果两个事务同时尝试对同一个账户进行转账，可能会导致余额计算错误。通过 SELECT ... FOR UPDATE 锁定账户记录，可以确保转账操作的原子性和一致性。
示例代码：

START TRANSACTION;
-- 锁定源账户和目标账户
SELECT * FROM accounts WHERE account_id = 1001 FOR UPDATE;
SELECT * FROM accounts WHERE account_id = 1002 FOR UPDATE;
-- 执行转账操作
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1001;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1002;
COMMIT;

3. 订单处理

在订单处理系统中，多个后台任务可能同时处理同一订单，导致重复处理或数据不一致的问题。通过 SELECT ... FOR UPDATE 锁定订单记录，可以确保每个订单只会被一个任务处理。
示例代码：

START TRANSACTION;
-- 锁定订单记录
SELECT * FROM orders WHERE order_id = 5678 FOR UPDATE;
-- 检查订单状态并处理
IF (订单未处理) THEN
    UPDATE orders SET status = '已处理' WHERE order_id = 5678;
ELSE
    ROLLBACK;  -- 订单已处理，回滚事务
END IF;
COMMIT;

4. 队列处理

在分布式系统中，多个工作节点可能同时从队列中取出任务进行处理。如果没有适当的锁定机制，可能会导致任务被重复处理。通过 SELECT ... FOR UPDATE 锁定队列中的任务记录，可以确保每个任务只会被一个工作节点处理。
示例代码：

START TRANSACTION;
-- 锁定并取出第一个未处理的任务
SELECT * FROM task_queue WHERE status = '待处理' LIMIT 1 FOR UPDATE;
-- 更新任务状态为“正在处理”
UPDATE task_queue SET status = '正在处理' WHERE id = <取出的任务ID>;
COMMIT;

5. 计数器更新

在某些系统中，可能需要维护一个全局计数器（例如，生成唯一的订单号、发票号等）。如果多个事务同时尝试更新计数器，可能会导致计数器值不一致。通过 SELECT ... FOR UPDATE 锁定计数器记录，可以确保每次更新都是原子性的。
示例代码：

START TRANSACTION;
-- 锁定计数器记录
SELECT * FROM counters WHERE name = 'order_number' FOR UPDATE;
-- 更新计数器
UPDATE counters SET value = value + 1 WHERE name = 'order_number';
COMMIT;

四、SELECT ... FOR UPDATE 的最佳实践

  1. 尽量缩短事务时间：长时间持有锁会影响系统的并发性能，因此应尽量缩短事务的持续时间。完成必要的操作后，尽快提交或回滚事务。
  2. 避免不必要的锁：只对确实需要保护的行进行锁定，避免对整个表或大量行加锁，以免影响其他事务的执行。
  3. 合理选择隔离级别：根据业务需求选择合适的事务隔离级别。例如，REPEATABLE READ 和 SERIALIZABLE 隔离级别可以提供更强的一致性保证，但也可能导致更多的锁争用。
  4. 处理死锁：在高并发环境下，SELECT ... FOR UPDATE 可能会导致死锁。可以通过捕获死锁异常并重试事务来解决这个问题。大多数数据库系统（如 MySQL）会自动检测死锁并回滚其中一个事务。
  5. 使用索引优化查询：确保 WHERE 子句中的条件列上有适当的索引，以加快锁定行的速度，减少锁的持有时间。

五、SELECT ... FOR UPDATE 的潜在风险

  1. 性能影响：SELECT ... FOR UPDATE 会锁定查询结果中的行，影响其他事务的并发执行。如果锁的范围过大或事务时间过长（长事务 Long Transaction），可能会导致系统性能下降。
  2. 死锁风险：当多个事务相互等待对方释放资源时，就会发生死锁（死锁 Deadlock）。虽然数据库系统可以自动检测并解决死锁，但频繁的死锁会影响系统的稳定性和性能。
  3. 锁定过多行：如果不小心使用 SELECT ... FOR UPDATE，可能会锁定过多的行，导致其他事务长时间等待。因此，应尽量缩小锁定的范围，只锁定确实需要保护的行。
  4. 不适合所有场景：SELECT ... FOR UPDATE 主要适用于需要确保数据一致性和完整性的场景。如果业务逻辑不需要强一致性，可以考虑使用其他机制（如乐观锁）来提高并发性能。

六、Spring Boot + Mybatis 事务

在 Spring Boot + Mybatis 中，可以通过 @Transactional 注解来声明一个事务。当方法被调用时，Spring 会自动创建一个事务，并在方法执行结束后提交或回滚事务。

    @Transactional
    public void transferMoney(int fromAccountId, int toAccountId, int amount) {
        // 执行转账操作
    }

这个注解告诉 Spring 框架，该方法应该在一个事务中执行。当方法执行时，Spring 将自动开启一个事务，并在方法执行结束后提交或回滚该事务。
需要在需要关闭事务的方法上添加 @Transactional 注解，并指定 rollbackFor 属性即可。例如：

    @Transactional(rollbackFor = Exception.class)
    public void transferMoney(int fromAccountId, int toAccountId, int amount) {
    // 执行转账操作
    }

rollbackFor 属性指定了应该触发事务回滚的异常类型。如果方法中抛出了指定的异常，Spring 将自动回滚事务，以确保数据的完整性。
除了使用 @Transactional 注解之外，还可以通过 SqlSession 对象手动控制事务。SqlSession 对象可以由 SqlSessionFactory 对象创建。例如：

    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build();
    SqlSession sqlSession = sqlSessionFactory.openSession();

使用 SqlSession 对象时，需要手动开启和关闭事务。例如：

    sqlSession.beginTransaction();
    // 执行操作
    sqlSession.commit();
    sqlSession.close();

通过这种方式，可以更细粒度地控制事务，例如，可以为不同的操作开启和关闭嵌套事务。

七、start transaction 和 begin的区别

两者的作用一摸一样，只是在begin可能成为关键字的时候，使用start transaction 可以 避免这种情况，start transaction或者begin开启一个事务，然后使用commit提交事务或者ROLLBACK回滚事务

八、锁管理机制

SELECT ... FOR UPDATE 加的锁是通过数据库系统的锁管理机制来实现和管理的，具体的锁存储位置和方式取决于所使用的数据库系统及其内部架构。不同的数据库系统（如 MySQL 的 InnoDB、PostgreSQL 等）有不同的锁管理机制，但总体来说，锁信息通常不会直接存储在磁盘上，而是存储在内存中的锁表或事务管理系统中。以下是几种常见数据库系统中 SELECT ... FOR UPDATE 锁的存储位置和管理方式。

1. MySQL InnoDB 存储引擎

InnoDB 是 MySQL 中默认的事务型存储引擎，它支持行级锁和多版本并发控制（MVCC）。InnoDB 使用锁表和事务日志来管理和记录锁信息。

1.1 锁表（Lock Table）
  • 锁表的作用：InnoDB 在内存中维护了一个锁表，用于记录当前所有事务持有的锁。每个锁条目包含了锁的类型（如排他锁或共享锁）、锁定的资源（如行ID、索引记录等）以及持有锁的事务ID。
  • 锁表的结构：
    • 事务ID：标识哪个事务持有了该锁。
    • 锁类型：排他锁（X Lock）或共享锁（S Lock）。
    • 锁定的资源：可以是表、索引、行或间隙（Gap Lock）。
    • 锁的状态：表示锁是否已经被授予或正在等待。
  • 锁表的位置：锁表是内存中的数据结构，并不是持久化存储在磁盘上的。这意味着锁信息只存在于内存中，当事务提交或回滚时，锁会被释放，锁表中的相关条目也会被移除。
1.2 事务日志（Transaction Log）
  • 事务日志的作用：InnoDB 使用**重做日志（Redo Log）和回滚段（Undo Log）**来记录事务的操作。虽然这些日志主要用于确保事务的持久性和一致性，但它们也间接地帮助管理锁。
  • 回滚段（Undo Log）：当一个事务执行 SELECT ... FOR UPDATE 并加锁时，InnoDB 会为该事务生成一个回滚段，记录事务对数据的修改。如果事务回滚，InnoDB 可以通过回滚段撤销未提交的更改，并释放锁。
  • 重做日志（Redo Log）：重做日志用于确保事务的持久性，但它不直接与锁管理相关。
1.3 锁的持久性
  • 锁的生命周期：InnoDB 中的锁只存在于内存中，直到事务提交或回滚。锁不会持久化到磁盘上，因此在服务器重启后，所有的锁都会被清除。这确保了在崩溃恢复时，不会存在遗留的锁，避免了死锁或其他问题。
1.4 死锁检测
  • 死锁检测机制：InnoDB 内置了死锁检测机制。当多个事务相互等待对方释放锁时，InnoDB 会定期检查是否存在死锁。如果检测到死锁，InnoDB 会选择一个代价较小的事务进行回滚，以打破死锁循环。

2. PostgreSQL

PostgreSQL 是另一种流行的开源关系型数据库，它也支持行级锁和多版本并发控制（MVCC）。PostgreSQL 的锁管理机制与 InnoDB 类似，但有一些不同的实现细节。

2.1 锁表（Lock Table）
  • 锁表的作用：PostgreSQL 在内存中维护了一个锁表，用于记录当前所有事务持有的锁。每个锁条目包含了锁的类型、锁定的资源以及持有锁的事务ID。
  • 锁表的结构：
    • 事务ID：标识哪个事务持有了该锁。
    • 锁类型：排他锁（Exclusive Lock）或共享锁（Share Lock）。
    • 锁定的资源：可以是表、索引、行或页面。
    • 锁的状态：表示锁是否已经被授予或正在等待。
  • 锁表的位置：锁表是内存中的数据结构，并不是持久化存储在磁盘上的。这意味着锁信息只存在于内存中，当事务提交或回滚时，锁会被释放，锁表中的相关条目也会被移除。
2.2 事务日志（WAL）
  • WAL（Write-Ahead Logging）：PostgreSQL 使用**预写式日志（WAL）**来记录事务的操作。WAL 主要用于确保事务的持久性和一致性，但它也间接地帮助管理锁。
  • 回滚段（MultiXact Data）：PostgreSQL 使用**多事务数据（MultiXact Data）**来管理多个事务之间的并发访问。当多个事务同时对同一行加锁时，PostgreSQL 会使用 MultiXact 数据结构来协调这些锁。
2.3 锁的持久性
  • 锁的生命周期：PostgreSQL 中的锁只存在于内存中，直到事务提交或回滚。锁不会持久化到磁盘上，因此在服务器重启后，所有的锁都会被清除。这确保了在崩溃恢复时，不会存在遗留的锁，避免了死锁或其他问题。
2.4 死锁检测
  • 死锁检测机制：PostgreSQL 内置了死锁检测机制。当多个事务相互等待对方释放锁时，PostgreSQL 会定期检查是否存在死锁。如果检测到死锁，PostgreSQL 会选择一个代价较小的事务进行回滚，以打破死锁循环。

3. Oracle

Oracle 是一种企业级的关系型数据库，它的锁管理机制相对复杂，但也基于类似的原理。

3.1 锁表（Lock Table）
  • 锁表的作用：Oracle 在内存中维护了一个锁表，用于记录当前所有事务持有的锁。每个锁条目包含了锁的类型、锁定的资源以及持有锁的事务ID。
  • 锁表的结构：
    • 事务ID：标识哪个事务持有了该锁。
    • 锁类型：排他锁（Exclusive Lock）或共享锁（Share Lock）。
    • 锁定的资源：可以是表、索引、行或页面。
    • 锁的状态：表示锁是否已经被授予或正在等待。
  • 锁表的位置：锁表是内存中的数据结构，并不是持久化存储在磁盘上的。这意味着锁信息只存在于内存中，当事务提交或回滚时，锁会被释放，锁表中的相关条目也会被移除。
3.2 事务日志（Redo Log）
  • Redo Log：Oracle 使用**重做日志（Redo Log）**来记录事务的操作。虽然这些日志主要用于确保事务的持久性和一致性，但它们也间接地帮助管理锁。
  • 回滚段（Undo Segment）：当一个事务执行 SELECT ... FOR UPDATE 并加锁时，Oracle 会为该事务生成一个回滚段，记录事务对数据的修改。如果事务回滚，Oracle 可以通过回滚段撤销未提交的更改，并释放锁。
3.3 锁的持久性
  • 锁的生命周期：Oracle 中的锁只存在于内存中，直到事务提交或回滚。锁不会持久化到磁盘上，因此在服务器重启后，所有的锁都会被清除。这确保了在崩溃恢复时，不会存在遗留的锁，避免了死锁或其他问题。
3.4 死锁检测
  • 死锁检测机制：Oracle 内置了死锁检测机制。当多个事务相互等待对方释放锁时

九、总结

SELECT ... FOR UPDATE 是一种强大的工具，用于在并发环境中确保数据库操作的一致性和完整性。通过锁定查询结果中的行，它可以有效防止脏读、不可重复读和幻读等问题。然而，使用 SELECT ... FOR UPDATE 时也需要注意性能和死锁的风险，尽量缩短事务的持续时间，并确保事务逻辑的正确性。
在实际开发中，开发者应根据具体的业务需求，权衡使用 SELECT ... FOR UPDATE 的利弊，选择最合适的方式来处理并发问题。希望本文能够帮助读者更好地理解和应用这一功能，提升系统的稳定性和性能。

参考资料

上次编辑于:
贡献者: 郑天祺
默认页脚
Copyright © 2025 zheng