MySQL：事务与锁定表

Question

我对事务与锁定表有些困惑，以确保数据库完整性，并确保SELECT和UPDATE保持同步，并且没有其他连接干扰它。我需要：

SELECT * FROM table WHERE (...) LIMIT 1

if (condition passes) {
   // Update row I got from the select 
   UPDATE table SET column = "value" WHERE (...)

   ... other logic (including INSERT some data) ...
}

我需要确保没有其他查询会干扰并执行相同的操作SELECT（在该连接完成更新行之前读取“旧值”。

我知道我可以默认为LOCK TABLES table只确保一次只有1个连接正在执行此操作，并在完成后将其解锁，但这似乎有点过头了。将它包装在事务中是否会做同样的事情（确保没有其他连接会尝试同一进程而另一个仍在处理）？或将一个SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE更好？

Answer 1

锁定表可防止其他数据库用户影响您锁定的行/表。但是锁本身并不能确保您的逻辑以一致的状态出现。

想想一个银行系统。当您在线支付账单时，至少有两个受交易影响的帐户：您的帐户，从中提取资金。以及收款人的帐户，资金将汇入该帐户。还有银行的帐户，他们将愉快地存入交易中收取的所有服务费。鉴于（众所周知，这些天，人们）银行异常愚蠢，可以说他们的系统是这样的：

$balance = "GET BALANCE FROM your ACCOUNT";
if ($balance < $amount_being_paid) {
    charge_huge_overdraft_fees();
}
$balance = $balance - $amount_being paid;
UPDATE your ACCOUNT SET BALANCE = $balance;

$balance = "GET BALANCE FROM receiver ACCOUNT"
charge_insane_transaction_fee();
$balance = $balance + $amount_being_paid
UPDATE receiver ACCOUNT SET BALANCE = $balance

现在，由于没有锁也没有交易，该系统容易受到各种竞争条件的影响，最大的竞争条件是对您的帐户或接收方帐户并行执行多次付款。虽然您的代码已取回余额并正在执行huge_overdraft_fees（）之类的操作，但其他付款完全有可能并行运行相同类型的代码。他们将取回您的余额（例如100美元），进行交易（取出您要支付的20美元，以及他们给您带来的30美元），现在这两个代码路径都有两个不同的余额：80美元和70美元。取决于最后完成的余额，您最终将在帐户中获得这两个余额中的任意一个，而不是应该以（$
100-$ 20-$ 30）结尾的$ 50。在这种情况下，“银行错误对您有利”

现在，假设您使用锁。您的帐单付款（$ 20）首先达到目标，因此它赢了并锁定了您的帐户记录。现在您已经拥有了专用权，可以从余额中扣除$
20，然后将新的余额放回原位…您的帐户最终会得到$
80的收益。但是…呃…您尝试去更新收款人的帐户，并且该帐户已被锁定，并且锁定的时间超出了代码允许的范围，导致您的交易超时…我们正在处理愚蠢的银行，因此没有适当的错误处理时，代码只需拉一个exit()，您的20美元就消失了。现在您只剩20美元了，还欠接收者20美元，您的电话就被收回了。

所以…输入交易。您开始交易，从您的帐户中扣款$ 20，然后尝试向收款人借入$ 20
…然后又发生了爆破。但这一次，exit()代码不是可以执行，而是rollback欺骗性地将您的$ 20神奇地加回到您的帐户中。

最后，归结为：

锁可以防止其他人干扰您正在处理的任何数据库记录。事务可防止任何“较晚”的错误干扰您所做的“较早”的事情。谁也不能保证最后一切都会好起来。但是他们在一起。

在明天的课程中：僵局的喜悦。