SQL：设计上的缺陷

# SQL：生来就有缺陷 来源：https://chreke.com/posts/sql-incorrect-by-construction SQL 和关系型数据库系统的设计使得意外引入严重并发错误变得非常容易。下面是一个教科书式的 T-SQL（https://en.wikipedia.org/wiki/Transact-SQL）转账过程：Alice 想给 Bob 转 10 美元，为了防止 Alice 透支账户，我们先检查她是否有足够的钱。这段代码看起来完全合理，但它有几个关键缺陷。你能发现它们吗？ `` 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 `` ## 原子性 首先，如果这个过程在中间中断，我们可能会从 Alice 的账户扣款，却没有把钱转给 Bob。Alice 对此肯定不会高兴，而且在这个过程中我们实际上“销毁”了资金。我们希望*所有*转账都成功，或者*都不*成功；解决方法是将整个流程包装在一个事务中： `` BEGIN TRANSACTION; 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 COMMIT TRANSACTION; `` ## TOCTOU 这样就结束了吗？还没完。假设 Alice 同时向 Bob 发起两笔转账，T1 和 T2。让我们看看会发生什么： 1. T1：检查 Alice 的账户余额 2. T2：检查 Alice 的账户余额 3. T1：从 Alice 的账户扣除 10 4. T2：从 Alice 的账户扣除 10 5. T1：向 Bob 的账户存入 10 6. T2：向 Bob 的账户存入 10 注意，T2 是在 T1 从 Alice 账户扣款*之前*检查余额的——因此当 T2 最终执行扣款时，账户可能会变成透支状态。这是一个典型的 Time-of-check to time-of-use（TOCTOU，检查时到使用时）（https://en.wikipedia.org/wiki/Time-of-check_to_time-of-use）漏洞：我们在检查条件和执行操作之间的条件发生了改变。 解决方法是在事务完成之前*锁定*Alice 的账户。我们可以通过改变隔离级别（https://en.wikipedia.org/wiki/Isolation_(database_systems)）来自动获取锁，或者手动锁定账户行： `` BEGIN TRANSACTION; DECLARE @balance INT; SET @balance = ( SELECT balance -- 这大致等价于 -- SELECT FOR UPDATE FROM accounts WITH (UPDLOCK) 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 COMMIT TRANSACTION; `` `UPDLOCK` 提示在 `SELECT` 运行时对 Alice 的账户采取行级锁；其他想要修改 Alice 账户的事务将一直阻塞，直到锁被释放。 ## 死锁 如果 Alice 和 Bob 试图同时互相转账怎么办？让我们重新梳理一下事务： 1. T1：获取 Alice 账户的锁 2. T2：获取 Bob 账户的锁 3. T1：检查 Alice 的账户余额 4. T2：检查 Bob 的账户余额 5. T1：从 Alice 的账户扣除 10 6. T2：从 Bob 的账户扣除 10 7. T1：无法更新 Bob 的账户，因为它被 T2 锁定 8. T2：无法更新 Alice 的账户，因为它被 T1 锁定 T1 等待 T2 对 Bob 的锁，T2 等待 T1 对 Alice 的锁——我们陷入了死锁（Deadlock）（https://en.wikipedia.org/wiki/Deadlock_(computer_science)）。解决方法是在一开始就获取所有锁¹（https://chreke.com/posts/sql-incorrect-by-construction#fn:1）： `` BEGIN TRANSACTION; DECLARE @balance INT; SELECT owner FROM accounts WITH (UPDLOCK) WHERE owner IN ('alice', 'bob'); 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 COMMIT TRANSACTION; `` ## 结论 我们修复了原始代码中的并发错误，但在此过程中代码量增加了大约 50%，并且变得更难阅读。当然，你可以争辩说还有其他更地道的修复方法²（https://chreke.com/posts/sql-incorrect-by-construction#fn:2），但观点依然成立：一个看起来完全合理的 SQL 程序可能隐藏着严重的缺陷。 如果你正在构建一个社交媒体网站，用户不小心给帖子点赞两次可能无伤大雅，但如果系统未能记录病人接受了药物剂量，可能会有致命的后果。对于正确性至关重要的系统，我们需要更好的工具。 ## 建议的解决方案 我希望有一个 SQL 的替代方案，采用 Rust 的“无畏并发”（https://doc.rust-lang.org/book/ch16-00-concurrency.html）方法——也就是说，将正确的行为设为默认值，并在必要时提供“不安全”的逃逸机制。一些具体建议如下： - 默认使事务具有原子性；如果用户想保存中间“检查点”状态，必须明确声明。 - 让用户自己管理锁，并确保在修改数据库对象之前获取正确的锁。 - 使用静态分析来检测潜在的死锁；这是一个棘手的问题，也是正在研究中的课题。确定性数据库系统（https://cacm.acm.org/research/an-overview-of-deterministic-database-systems/）可能是一个可能的解决方案。 这种系统将带来其他权衡；例如，它的吞吐量可能低于现代 SQL 系统。但这没关系——对于正确性要求较低的使用场景，我们仍然可以使用 SQL。