数据库悲观锁SQL更新丢失问题

Question

在使用数据库悲观锁时，为何会出现SQL更新丢失问题，如何有效避免？

Answer 1

在使用数据库悲观锁时，为何会出现SQL更新丢失问题，如何有效避免？

1. 什么是悲观锁？

悲观锁（Pessimistic Locking）是一种并发控制机制，其核心思想是：在访问数据时，总是假设其他事务会修改该数据，因此在读取时就对数据加锁，防止其他事务同时修改。

在数据库中，常见的悲观锁实现方式包括：SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE（MySQL）等。

2. SQL更新丢失问题的常见表现

在并发系统中，当两个事务同时读取同一行数据并尝试更新时，可能会出现更新丢失问题。例如：

• 事务A读取某行数据为100
• 事务B也读取该行数据为100
• 事务A将值更新为150并提交
• 事务B将值更新为200并提交
• 最终结果是200，但事务A的更新被覆盖

3. 为什么使用悲观锁还会出现更新丢失？

虽然使用了悲观锁，但如果加锁粒度、事务边界或隔离级别设置不当，仍然可能导致更新丢失。常见原因包括：

原因	说明
未在读操作中加锁	如果只是普通SELECT，未使用FOR UPDATE或LOCK IN SHARE MODE，则无法阻止其他事务的写入
事务未正确提交或回滚	事务未及时提交，导致锁长时间持有，引发死锁或超时
隔离级别设置不当	如使用READ COMMITTED隔离级别，可能无法阻止不可重复读或更新丢失

4. 更新丢失问题的解决策略

为避免更新丢失，应结合事务控制、锁机制和隔离级别进行综合设计。以下是一些有效的解决方案：

1. 使用SELECT ... FOR UPDATE：在事务中读取数据时加排他锁，确保其他事务不能修改该行数据。
2. 合理控制事务边界：确保事务在完成更新后立即提交，避免长时间持有锁。
3. 提升事务隔离级别：使用REPEATABLE READ或SERIALIZABLE隔离级别，增强一致性控制。
4. 结合版本号或时间戳：在更新时检查版本号，若版本不一致则拒绝更新。

5. 示例：使用悲观锁避免更新丢失

以下是一个使用MySQL的示例代码：

-- 事务A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- 假设读取到 balance = 100
UPDATE accounts SET balance = 150 WHERE id = 1;
COMMIT;

-- 事务B
START TRANSACTION;
-- 此时会被阻塞，直到事务A提交或回滚
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- 读取到 balance = 150
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;
    

6. 悲观锁与乐观锁的对比

为了更好地理解悲观锁的适用场景，我们对比悲观锁与乐观锁的核心差异：

特性	悲观锁	乐观锁
并发控制方式	假设冲突频繁，提前加锁	假设冲突较少，更新时检查冲突
性能影响	高并发下可能造成锁竞争	适合读多写少场景
适用场景	金融交易、库存扣减等关键业务	表单提交、评论更新等低冲突场景

7. 悲观锁的使用建议与最佳实践

以下是使用悲观锁时的一些最佳实践：

• 仅在必要时使用悲观锁，避免不必要的性能损耗
• 确保事务尽可能短，减少锁的持有时间
• 使用合适的索引，避免锁升级或全表锁定
• 在应用层做好异常处理，确保事务能正确回滚

8. 总结性流程图

graph TD
    A[开始事务] --> B[SELECT ... FOR UPDATE]
    B --> C{是否成功获取锁?}
    C -->|是| D[进行业务逻辑处理]
    C -->|否| E[等待或抛出异常]
    D --> F[执行UPDATE操作]
    F --> G[提交事务]
    G --> H[释放锁]
    E --> H