问题描述
对于事务与锁定表，我有点困惑，以确保数据库完整性，并确保SELECT和UPDATE保持同步，并且没有其他连接干扰它.我需要:
I'm a bit confused with transactions vs locking tables to ensure database integrity and make sure a SELECT and UPDATE remain in sync and no other connection interferes with it. I need to:
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(在该连接完成更新行之前读取旧值".
I need to ensure that no other queries will interfere and perform the same SELECT (reading the 'old value' before that connection finishes updating the row.
我知道我可以默认使用LOCK TABLES table来确保每次只有1个连接在执行此操作，并在完成后将其解锁，但这似乎有点过头了.将它包装在事务中是否会做同样的事情(确保没有其他连接会尝试相同的进程而另一个仍在处理)?还是SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE会更好?
I know I can default to LOCK TABLES table to just make sure that only 1 connection is doing this at a time, and unlock it when I'm done, but that seems like overkill. Would wrapping that in a transaction do the same thing (ensuring no other connection attempts the same process while another is still processing)? Or would a SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE be better?
推荐答案
锁定表可防止其他数据库用户影响您已锁定的行/表.但是，锁本身并不能确保您的逻辑以一致的状态出现.
Locking tables prevents other DB users from affecting the rows/tables you've locked. But locks, in and of themselves, will NOT ensure that your logic comes out in a consistent state.
想想银行系统.当您在线支付账单时，至少有两个受交易影响的帐户:您的帐户，从中提取资金.以及收款人的帐户，资金将汇入该帐户.还有银行的帐户，他们将愉快地存入交易中收取的所有服务费.鉴于(众所周知，这些天大家都知道)银行异常愚蠢，我们可以说他们的系统是这样的:
Think of a banking system. When you pay a bill online, there's at least two accounts affected by the transaction: Your account, from which the money is taken. And the receiver's account, into which the money is transferred. And the bank's account, into which they'll happily deposit all the service fees charged on the transaction. Given (as everyone knows these days) that banks are extraordinarily stupid, let's say their system works like this:
$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.在这种情况下，银行错误对您有利".
Now, with no locks and no transactions, this system is vulnerable to various race conditions, the biggest of which is multiple payments being performed on your account, or the receiver's account in parallel. While your code has your balance retrieved and is doing the huge_overdraft_fees() and whatnot, it's entirely possible that some other payment will be running the same type of code in parallel. They'll be retrieve your balance (say, $100), do their transactions (take out the $20 you're paying, and the $30 they're screwing you over with), and now both code paths have two different balances: $80 and $70. Depending on which ones finishes last, you'll end up with either of those two balances in your account, instead of the $50 you should have ended up with ($100 - $20 - $30). In this case, "bank error in your favor".
现在，假设您使用锁.您的帐单付款($ 20)首先达到目标，因此它赢了并锁定了您的帐户记录.现在您已经可以独享了，可以从余额中扣除$ 20，然后将新的余额放回去...您的帐户最终会得到$ 80的收益.但是...呃...您尝试去更新收款人的帐户，并且该帐户已被锁定，并且锁定时间超出了代码允许的范围，导致您的交易超时...我们正在处理愚蠢的银行，因此没有适当的错误处理时，代码只需拉一个exit()，您的20美元消失在一团电子中.现在您已经花了20美元，但您仍欠接收者20美元，电话就被收回了.
Now, let's say you use locks. Your bill payment ($20) hits the pipe first, so it wins and locks your account record. Now you've got exclusive use, and can deduct the $20 from the balance, and write the new balance back in peace... and your account ends up with $80 as is expected. But... uhoh... You try to go update the receiver's account, and it's locked, and locked longer than the code allows, timing out your transaction... We're dealing with stupid banks, so instead of having proper error handling, the code just pulls an exit(), and your $20 vanishes into a puff of electrons. Now you're out $20, and you still owe $20 to the receiver, and your telephone gets repossessed.
所以...输入交易.您开始交易，从您的帐户中扣款$ 20，然后尝试向收款人借入$ 20 ...，然后又发生爆破.但这一次，代码可以代替rollback来执行rollback了，然后po，神奇地将您的20美元重新添加到您的帐户中.
So... enter transactions. You start a transaction, you debit your account $20, you try to credit the receiver with $20... and something blows up again. But this time, instead of exit(), the code can just do rollback, and poof, your $20 is magically added back to your account.
最后，归结为:
锁使其他任何人都不会干扰您正在处理的任何数据库记录.事务可防止任何较晚"的错误干扰您所做的较早"的事情.谁也不能保证最后一切都会好起来.但是在一起，他们做到了.
Locks keep anyone else from interfering with any database records you're dealing with. Transactions keep any "later" errors from interfering with "earlier" things you've done. Neither alone can guarantee that things work out ok in the end. But together, they do.
在明天的课程中:僵局之乐.
in tomorrow's lesson: The Joy of Deadlocks.
这篇关于MySQL:事务与锁定表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！