SQL: 错误的设计构造？

一个转账的教科书级案例——Alice 向 Bob 转账十美元——看起来非常直观。你检查 Alice 的余额，确保她有足够的资金，从她的账户中减去该金额，然后将其添加到 Bob 的账户中。在大多数编程语言中，这种逻辑是符合直觉的。然而，在关系型数据库的世界里，这种“合理”的代码往往是并发漏洞的雷区。

当开发者将 SQL 视为简单的脚本语言，而不是一个在并发环境下管理共享状态的系统时，他们会遇到三个主要陷阱：原子性失败、检查时与使用时（TOCTOU）漏洞，以及死锁。

并发失败的解剖

考虑一个基础的 T-SQL 转账实现：

DECLARE @balance INT;

SET @balance = (  
    SELECT balance  
    FROM accounts
    WHERE owner = 'alice'
);

IF @balance >= 10  
BEGIN  
    UPDATE accounts  
    SET balance = balance - 10  
    WHERE owner = 'alice';  
  
    UPDATE accounts  
    SET balance = balance + 10  
    WHERE owner = 'bob';  
END

1. 原子性缺口

如果系统在第一次 UPDATE 之后但在第二次之前崩溃或过程中止，资金会从 Alice 的账户中消失，而从未到达 Bob。为了确保要么两个更新都发生，要么都不发生，操作必须封装在事务中（BEGIN TRANSACTION 和 COMMIT TRANSACTION）。

2. TOCTOU 竞态条件

即使使用了事务，检查时与使用时（TOCTOU）漏洞仍然存在。如果 Alice 同时发起两次转账（T1 和 T2），两者都可能在执行任何一次取款之前读取她的余额。两个事务都看到余额充足，两者都继续取款，导致 Alice 的账户最终透支。

为了修复这个问题，开发者必须使用锁定提示（例如 T-SQL 中的 UPDLOCK 或其他方言中的 SELECT FOR UPDATE），以确保在读取行时立即锁定该行，防止其他事务在当前事务完成之前对其进行修改。

3. 死锁陷阱

锁定引入了一个新问题：死锁。如果 Alice 向 Bob 转账，而 Bob 同时向 Alice 转账，T1 可能会锁定 Alice 的账户并等待 Bob 的账户，而 T2 可能会锁定 Bob 的账户并等待 Alice 的账户。两者都无法继续。

虽然作者建议预先获取所有锁，但更稳健的行业标准是按一致且确定的顺序获取锁（例如，按账户 ID 排序）。

SQL 是否“错误的设计构造”？

核心论点是 SQL 的设计使得编写错误代码变得过于容易。作者建议，对于正确性至关重要的系统——例如医疗剂量——我们需要一种类似于 Rust 的“无畏并发”模型，其中正确行为是默认设置，而“不安全”操作是显式的。

然而，这一说法在经验丰富的数据库工程师中引发了激烈的辩论。许多人认为，这些问题并非 SQL 构造上的缺陷，而是“SQL 入门级”的概念。

反驳观点

社区针对这些所谓的“缺陷”提出了几个关键点：

  • 隔离级别： 许多描述的问题是弱默认隔离级别（如 READ COMMITTED）的结果。真正的 SNAPSHOT 隔离或 SERIALIZABLE 级别可以通过将冲突转变为重试而非静默失败来消除许多此类竞态条件。
  • 引擎智能： 现代数据库引擎（MySQL, SQL Server）并非死锁的被动观察者。它们会主动检测死锁环，选择一个“受害者”事务进行回滚，并返回特定的错误（例如 SQL Server 中的 Error 1205），并期望应用程序进行重试。
  • 惯用法模式： 经验丰富的开发者会完全避免“先检查后执行”模式。相反，他们使用条件更新：
    UPDATE accounts SET balance = balance - 10 
WHERE owner = 'alice' AND balance >= 10;
    这在引擎层面将检查和操作合并为一个单一的原子操作。

架构替代方案

除了修复 T-SQL 语法，讨论还强调了两种针对金融数据的优越架构模式：

账本模式（仅追加）

与其更新一个易于发生竞态条件的易变状态 balance 列——这是一种可变状态——许多人建议使用仅追加的账本。在这种模式下，你从不 UPDATE 余额；你只 INSERT 事务行（例如，Alice: -10, Bob: +10）。当前的余额则是所有账本分录的累加和。

应用层并发控制

一些开发者将并发控制移至应用层，或者使用分布式锁机制来确保一次只有一个进程可以修改特定资源，从而减少对数据库内部锁定机制的依赖。

结论

无论 SQL 是“错误的设计构造”还是仅仅需要对 ACID 属性的严谨理解，教训依然存在：在并发系统中，“看起来合理的代码”与“正确代码”之间的距离是巨大的。对于关键系统，仅仅依赖默认设置是远远不够的；显式的锁定策略、严格的隔离级别或不可变的账本架构对于维护数据完整性至关重要。

Sources