MySQL：事务与锁定表

MySQL: Transactions vs Locking Tables

提问by Ryan

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 和 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) ...
}

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.

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

I know I can default to LOCK TABLES tableto 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 UPDATEor SELECT ... LOCK IN SHARE MODEbe better?

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

回答by Marc B

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

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".

现在，由于没有锁定和交易，这个系统很容易受到各种竞争条件的影响，其中最大的问题是在您的帐户上或并行接收者的帐户上执行多次付款。虽然您的代码已检索到您的余额，并且正在执行 huge_overdraft_fees() 等操作，但其他付款完全有可能并行运行相同类型的代码。他们会取回你的余额（比如 100 美元），进行他们的交易（取出你支付的 20 美元，以及他们骗你的 30 美元），现在两个代码路径都有两种不同的余额：80 美元和70 美元。根据最后完成的那些，您最终会在您的帐户中拥有这两个余额中的任何一个，而不是您应该得到的 50 美元（100 美元 - 20 美元 - 30 美元）。在这种情况下，“银行错误对您有利”

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 美元，然后安心地将新余额写回……您的帐户最终如预期的那样有 80 美元。但是......呃......你试图去更新接收者的账户，它被锁定，并且锁定的时间比代码允许的时间长，你的交易超时......我们正在与愚蠢的银行打交道，所以没有正确的错误处理，代码只是拉出一个exit()，你的 20 美元就会变成一团电子。现在你的钱是 20 美元，你还欠收信人 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.

所以...进入交易。你开始一笔交易，从你的账户中扣款 20 美元，你尝试将 20 美元贷给收款人……然后事情又发生了。但这一次，exit()代码可以代替rollback，然后噗，您的 20 美元神奇地添加回您的帐户。

In the end, it boils down to this:

最后，它归结为：

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.

在明天的课程中：僵局的乐趣。

回答by Alison R.

You want a SELECT ... FOR UPDATEor SELECT ... LOCK IN SHARE MODEinside a transaction, as you said, since normally SELECTs, no matter whether they are in a transaction or not, will not lock a table. Which one you choose would depend on whether you want other transactions to be able to read that row while your transaction is in progress.

正如您所说，您想要一个SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE事务内部，因为通常 SELECT ，无论它们是否在事务中，都不会锁定表。您选择哪一个取决于您是否希望其他事务能够在您的事务进行时读取该行。

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

START TRANSACTION WITH CONSISTENT SNAPSHOTwill not do the trick for you, as other transactions can still come along and modify that row. This is mentioned right at the top of the link below.

START TRANSACTION WITH CONSISTENT SNAPSHOT不会为您解决问题，因为其他事务仍然可以修改该行。这在下面链接的顶部提到了。

If other sessions simultaneously update the same table [...] you may see the table in a state that never existed in the database.

如果其他会话同时更新同一个表 [...]，您可能会看到该表处于数据库中从未存在过的状态。

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

回答by tczhaodachuan

Transaction concepts and locks are different. However, transaction used locks to help it to follow the ACID principles. If you want to the table to prevent others to read/write at the same time point while you are read/write, you need a lock to do this. If you want to make sure the data integrity and consistence, you had better use transactions. I think mixed concepts of isolation levels in transactions with locks. Please search isolation levels of transactions, SERIALIZE should be the level you want.

事务概念和锁是不同的。但是，事务使用锁来帮助它遵循 ACID 原则。如果你想让表在你读/写的同时防止其他人读/写，你需要一个锁来做到这一点。如果要确保数据的完整性和一致性，最好使用事务。我认为带有锁的事务中隔离级别的混合概念。请搜索事务的隔离级别，SERIALIZE 应该是您想要的级别。

回答by Tony

I had a similar problem when attempting a IF NOT EXISTS ...and then performing an INSERTwhich caused a race condition when multiple threads were updating the same table.

我在尝试 aIF NOT EXISTS ...然后执行 an时遇到了类似的问题，INSERT这在多个线程更新同一个表时导致了竞争条件。

I found the solution to the problem here: How to write INSERT IF NOT EXISTS queries in standard SQL

我在这里找到了问题的解决方案：如何在标准 SQL 中编写 INSERT IF NOT EXISTS 查询

I realise this does not directly answer your question but the same principle of performing an check and insert as a single statement is very useful; you should be able to modify it to perform your update.

我意识到这并不能直接回答您的问题，但是将检查和插入作为单个语句执行的相同原则非常有用；您应该能够对其进行修改以执行更新。

回答by David

You are confused with lock & transaction. They are two different things in RMDB. Lock prevents concurrent operations while transaction focuses on data isolation. Check out thisgreat article for the clarification and some graceful solution.

您对锁定和交易感到困惑。它们在 RMDB 中是两种不同的东西。锁防止并发操作，而事务侧重于数据隔离。查看这篇很棒的文章以获得澄清和一些优雅的解决方案。

回答by Martin Schapendonk

I'd use a

我会用

START TRANSACTION WITH CONSISTENT SNAPSHOT;

to begin with, and a

开始，和

COMMIT;

to end with.

结束。

Anything you do in between is isolated from the others users of your database if your storage engine supports transactions(which is InnoDB).

如果您的存储引擎支持事务（即 InnoDB），那么您在两者之间所做的任何事情都与数据库的其他用户隔离。