SQL：构造错误

SQL 和关系数据库系统的设计很容易意外引入严重的并发错误。下面是 TSQL 中的教科书汇款过程； Alice 想向 Bob 发送 10 美元，为了防止 Alice 透支她的账户，我们首先检查她是否有足够的钱。该代码看起来完全合理，但它有几个严重的错误。你能发现它们吗？声明 @balance INT ； SET @balance = ( 从账户中选择余额，其中所有者 = 'alice' ); IF @balance >= 10 开始更新帐户 SET 余额 = 余额 - 10 WHERE 所有者 = 'alice' ;更新帐户 SET 余额 = 余额 + 10 WHERE 所有者 = 'bob' ; END 原子性 首先，如果这个过程中途中止，我们可能会从 Alice 的账户中转账，而不向 Bob 转账。爱丽丝不会对此感到高兴，而且我们在这个过程中毁掉了钱。我们希望所有转移成功，或者全部失败；修复方法是将过程包装在事务中： BEGIN TRANSACTION ；声明 @balance INT ； SET @balance = ( 从账户中选择余额，其中所有者 = 'alice' ); IF @balance >= 10 开始更新帐户 SET 余额 = 余额 - 10 WHERE 所有者 = 'alice' ;更新帐户 SET 余额 = 余额 + 10 WHERE 所有者 = 'bob' ;结束提交事务； TOCTOU 我们完成了吗？不完全是。假设 Alice 并行向 Bob 发起两次传输，T1 和 T2。让我们想象一下会发生什么： T1：检查 Alice 的帐户余额 T2：检查 Alice 的帐户余额 T1：从 Alice 的帐户中提取 10 T2：从 Alice 的帐户中提取 10 T1：在 Bob 的帐户中存入 10 T2：在 Bob 的帐户中存入 10 请注意，在 T1 从 Alice 的帐户中提取任何资金之前，T2 如何检查余额，因此当 T2 最终提取时，该帐户可能会透支。这是一个检查时间到使用时间 (TOCTOU) 错误：先决条件在我们检查它和我们采取行动之间发生变化。解决方法是锁定爱丽丝的帐户，直到交易完成。我们可以更改隔离级别，以便自动获取锁，或者手动锁定帐户行： BEGIN TRANSACTION ；声明 @balance INT ； SET @balance = ( SELECT 余额 -- 这大致相当于 SELECT FOR UPDATE FROMcountsWITH (UPDLOCK) WHEREowner = 'alice' ); IF @balance >= 10 开始更新帐户 SET 余额 = 余额 - 10 WHERE 所有者 = 'alice' ;更新帐户 SET 余额 = 余额 + 10 WHERE 所有者 = 'bob' ;结束提交事务；当 SELECT 运行时，UPDLOCK 提示会对 Alice 的帐户进行行级锁定；其他想要修改 Alice 账户的交易将被阻塞，直到锁被释放。僵局 如果 Alice 和 Bob 都尝试同时向对方转账怎么办？让我们再次规划一下交易： T1：获取 Alice 账户的锁定 T2：获取 Bob 账户的锁定 T1：检查 Alice 的账户余额 T2：检查 Bob 的账户余额 T1：从 Alice 的账户中提取 10 T2：从 Bob 的账户提取 10 T1：无法更新 Bob 的账户，因为它被 T2 锁定 T2：无法更新 Alice 的账户，因为它被 T1 锁定T1等待T2对Bob的锁定； T2 等待 T1 对 Alice 的锁定——我们陷入了僵局。修复方法是预先获取所有锁 1 : BEGIN TRANSACTION ;声明 @balance INT ； SELECT 所有者从帐户WITH (UPDLOCK) WHERE 所有者IN ( 'alice' , 'bob' ); SET @balance = ( 从账户中选择余额，其中所有者 = 'alice' ); IF @balance >= 10 开始更新帐户 SET 余额 = 余额 - 10 WHERE 所有者 = 'alice' ;更新帐户 SET 余额 = 余额 + 10 WHERE 所有者 = 'bob' ;结束提交事务；结论我们已经修复了原始代码中的并发错误，但在此过程中它增长了约 50%，并且变得更难以阅读。当然，您可能会说还有其他更惯用的方法来修复此代码 2 ，但要点仍然成立：看起来完全合理的 SQL 程序可能充满严重的错误。如果您正在构建一个社交媒体网站，如果用户对某个帖子点赞两次，这可能并不是世界末日，但如果系统无法记录患者接受了一剂药物，则可能会产生致命的后果。对于正确性很重要的系统，我们需要更好的工具。建议的解决方案 我想要一个 SQL 的替代方案，采用 Rust 的无所畏惧的并发方法——也就是说，将正确的行为作为默认行为，并在必要时提供“不安全”的逃生舱口。一些具体的建议： 默认情况下使事务原子化；如果用户想要保存中间“检查点”状态，他们必须明确说明。让用户自己管理锁，并确保在更改数据库对象之前获取正确的锁。使用静态分析来检测潜在的死锁；这是一个棘手的问题，也是正在进行的研究的主题。确定性数据库系统可能是一种可能的解决方案。该系统还需要进行其他权衡；例如，它最终的吞吐量可能低于现代 SQL 系统。但这很好——我们仍然有 SQL 用于正确性要求较高的用例