SQL 如何处理数据库中的并发更新？

How to deal with concurrent updates in databases?

提问by Leeeroy

What's the common way to deal with concurrent updates in an SQL database ?

在 SQL 数据库中处理并发更新的常用方法是什么？

Consider a simple SQL schema(constraints and defaults not shown..) like

考虑一个简单的 SQL 模式（约束和默认值未显示......）

create table credits (
  int id,
  int creds,
  int user_id
);

The intent is to store some kind of credits for a user, e.g. something like stackoverflow's reputation.

目的是为用户存储某种信用，例如像stackoverflow 的声誉这样的东西。

How to deal with concurrent updates to that table ? A few options:

如何处理对该表的并发更新？几个选项：

• update credits set creds= 150 where userid = 1;

  In this case the application retreived the current value, calculated the new value(150) and performed an update. Which spells disaster if someone else does the same at the same time. I'm guessing wrapping the retreival of the current value and update in a transaction would solve that , e.g. Begin; select creds from credits where userid=1; do application logic to calculate new value, update credits set credits = 160 where userid = 1; end;In this case you could check if the new credit would be < 0 and just truncate it to 0 if negative credits make no sense.

• update credits set creds = creds - 150 where userid=1;

  This case wouldn't need to worry about concurrent updates as the DB takes care of the consistency problem, but has the flaw that creds would happily become negative, which might not make sense for some applications.

• update credits set creds= 150 where userid = 1;

  在这种情况下，应用程序检索当前值，计算新值 (150) 并执行更新。如果其他人同时做同样的事情，那就意味着灾难。我猜测在交易中包装当前值的检索和更新可以解决这个问题，例如，Begin; select creds from credits where userid=1; do application logic to calculate new value, update credits set credits = 160 where userid = 1; end;在这种情况下，您可以检查新信用是否 < 0，如果负信用没有意义，则将其截断为 0。

• update credits set creds = creds - 150 where userid=1;

  这种情况下不需要担心并发更新，因为 DB 会处理一致性问题，但是存在一个缺陷，即信用证很乐意变成负数，这对某些应用程序可能没有意义。

So simply, what's the accepted method to deal with the (quite simple) problem outlined above, what if the db throws an error ?

很简单，处理上面概述的（非常简单的）问题的公认方法是什么，如果数据库抛出错误怎么办？

采纳答案by bdonlan

Use transactions:

使用交易：

BEGIN WORK;
SELECT creds FROM credits WHERE userid = 1;
-- do your work
UPDATE credits SET creds = 150 WHERE userid = 1;
COMMIT;

Some important notes:

一些重要的注意事项：

• Not all database types support transactions. In particular, mysql's old default database engine (default before version 5.5.5), MyISAM, doesn't. Use InnoDB (the new default) if you're on mysql.
• Transactions can abort due to reasons beyond your control. If this happens, your application must be prepared to start all over again, from the BEGIN WORK.
• You'll need to set the isolation level to SERIALIZABLE, otherwise the first select can read data that other transactions have not committed yet(transactions arn't like mutexes in programming languages). Some databases will throw an error if there's concurrent ongoing SERIALIZABLE transactions, and you'll have to restart the transaction.
• Some DBMS provide SELECT .. FOR UPDATE , which will lock the rows retreived by select until the transaction ends.

• 并非所有数据库类型都支持事务。特别是，mysql 的旧默认数据库引擎（5.5.5 版之前的默认值）MyISAM 没有。如果您使用的是 mysql，请使用 InnoDB（新的默认值）。
• 由于您无法控制的原因，交易可能会中止。如果发生这种情况，您的应用程序必须准备好从 BEGIN WORK 重新开始。
• 您需要将隔离级别设置为 SERIALIZABLE，否则第一个 select 可以读取其他事务尚未提交的数据（事务不像编程语言中的互斥锁）。如果存在并发正在进行的 SERIALIZABLE 事务，某些数据库将抛出错误，您必须重新启动事务。
• 一些 DBMS 提供 SELECT .. FOR UPDATE ，它将锁定由 select 检索到的行，直到事务结束。

Combining transactions with SQL stored procedures can make the latter part easier to deal with; the application would just call a single stored procedure in a transaction, and re-call it if the transaction aborts.

将事务与 SQL 存储过程结合起来，可以使后面的部分更容易处理；应用程序只会在事务中调用单个存储过程，并在事务中止时重新调用它。

回答by Savant Degrees

For MySQL InnoDB tables, this really depends on the isolation level you set.

对于 MySQL InnoDB 表，这实际上取决于您设置的隔离级别。

If you are using the default level 3 (REPEATABLE READ), then you would need to lock any row that affects subsequent writes, even if you are in a transaction. In your example you will need to :

如果您使用默认级别 3（REPEATABLE READ），那么您将需要锁定影响后续写入的任何行，即使您在事务中也是如此。在您的示例中，您需要：

SELECT FOR UPDATE creds FROM credits WHERE userid = 1;
-- calculate --
UPDATE credits SET creds = 150 WHERE userid = 1;

If you are using level 4 (SERIALIZABLE), then a simple SELECT followed by update is sufficient. Level 4 in InnoDB is implemented by read-locking every row that you read.

如果您使用的是级别 4 (SERIALIZABLE)，那么简单的 SELECT 后跟更新就足够了。InnoDB 中的第 4 级是通过对您读取的每一行进行读锁定来实现的。

SELECT creds FROM credits WHERE userid = 1;
-- calculate --
UPDATE credits SET creds = 150 WHERE userid = 1;

However in this specific example, since the computation (adding credits) is simple enough to be done in SQL, a simple:

然而，在这个特定的例子中，由于计算（添加学分）很简单，可以在 SQL 中完成，一个简单的：

UPDATE credits set creds = creds - 150 where userid=1;

will be equivalent to a SELECT FOR UPDATE followed by UPDATE.

将等价于 SELECT FOR UPDATE 后跟 UPDATE。

回答by Abel ANEIROS

Wrapping the code inside a transaction it's not enough in some cases regardless the isolation level you define (e.g imaging you have deployed your code into 2 different servers in production).

在某些情况下，无论您定义的隔离级别如何，将代码包装在事务中都是不够的（例如，将代码部署到生产中的 2 个不同服务器中的映像）。

Let's say you have these steps and 2 concurrency threads:

假设您有这些步骤和 2 个并发线程：

1) open a transaction
2) fetch the data (SELECT creds FROM credits WHERE userid = 1;)
3) do your work (credits + amount)
4) update the data (UPDATE credits SET creds = ? WHERE userid = 1;)
5) commit

And this time line:

而这个时间线：

Time =  0; creds = 100
Time =  1; ThreadA executes (1) and creates Txn1
Time =  2; ThreadB executes (1) and creates Txn2
Time =  3; ThreadA executes (2) and fetches 100
Time =  4; ThreadB executes (2) and fetches 100
Time =  5; ThreadA executes (3) and adds 100 + 50
Time =  6; ThreadB executes (3) and adds 100 + 50
Time =  7; ThreadA executes (4) and updates creds to 150
Time =  8; ThreadB tries to executes (4) but in the best scenario the transaction
          (depending of isolation level) won't allow it and you get an error

The transaction prevents you to override the creds value with a wrong value but it's not enough because I don't want to fail any error.

该交易会阻止您使用错误的值覆盖 creds 值，但这还不够，因为我不想让任何错误失败。

I prefer instead an slower process that never fail and I solved the problem with a "database row lock" in the moment I fetch the data (step 2) that prevents other threads can read the same row until I'm done with it.

相反，我更喜欢一个永远不会失败的较慢的过程，并且在我获取数据的那一刻（第 2 步），我用“数据库行锁”解决了这个问题，以防止其他线程在我完成之前可以读取同一行。

There are few ways to do in SQL Server and this is one of them:

在 SQL Server 中有几种方法可以做到，这是其中之一：

SELECT creds FROM credits WITH (UPDLOCK) WHERE userid = 1;

If I recreate the previous time line with this improvement you get something like this:

如果我用这个改进重新创建以前的时间线，你会得到这样的东西：

Time =  0; creds = 100
Time =  1; ThreadA executes (1) and creates Txn1
Time =  2; ThreadB executes (1) and creates Txn2
Time =  3; ThreadA executes (2) with lock and fetches 100
Time =  4; ThreadB tries executes (2) but the row is locked and 
                   it's has to wait...

Time =  5; ThreadA executes (3) and adds 100 + 50
Time =  6; ThreadA executes (4) and updates creds to 150
Time =  7; ThreadA executes (5) and commits the Txn1

Time =  8; ThreadB was waiting up to this point and now is able to execute (2) 
                   with lock and fetches 150
Time =  9; ThreadB executes (3) and adds 150 + 50
Time = 10; ThreadB executes (4) and updates creds to 200
Time = 11; ThreadB executes (5) and commits the Txn2

回答by Ovidiu Lupas

Optimistic locking using a new timestampcolumn can solve this concurrency issue.

使用新timestamp列的乐观锁可以解决这个并发问题。

UPDATE credits SET creds = 150 WHERE userid = 1 and modified_data = old_modified_date

回答by Ola Herrdahl

For the first scenario you could add another condition in the where-clause to make sure you won't overwrite changes made by a concurrent user. E.g.

对于第一种情况，您可以在 where 子句中添加另一个条件，以确保不会覆盖并发用户所做的更改。例如

update credits set creds= 150 where userid = 1 AND creds = 0;

回答by Darth Continent

You could set up a queueing mechanism where additions to or subtractions from a rank type value would get queued up for periodic LIFO processing by some job. If real-time info on a rank's "balance" is required this wouldn't fit because the balance wouldn't compute until the outstanding queue entries are reconciled, but if it's something that doesn't require immediate reconciliation it might serve.

您可以设置一个排队机制，其中对某个等级类型值的加法或减法将排队等待某个作业的定期 LIFO 处理。如果需要有关排名的“余额”的实时信息，这将不合适，因为在核对未完成的队列条目之前不会计算余额，但如果它不需要立即核对，它可能会起作用。

This seems to reflect, at least on the outside looking in, how games like the old Panzer General series handle individual moves. One player's turn comes up, and they declare their moves. Each move in turn is processed in sequence, and there are no conflicts because each move has its place in the queue.

这似乎反映了，至少从外部看，像旧的 Panzer General 系列这样的游戏是如何处理个人动作的。轮到一名玩家，他们宣布他们的行动。每个动作依次按顺序处理，没有冲突，因为每个动作在队列中都有它的位置。

回答by amar

Table can be modified as below, introduce new field version to handle optimistic locking. This is more cost effective and efficient way to achieve better performance rather than using locks at database level create table credits ( int id, int creds, int user_id, int version );

表可以修改如下，引入新的字段版本来处理乐观锁。这是实现更好性能的更具成本效益和效率的方法，而不是在数据库级别使用锁创建表信用（ int id, int creds, int user_id, int version ）；

select creds, user_id, version from credits where user_id=1;

select creds, user_id, version from credits where user_id=1;

assume this returns creds = 100 and version=1

假设这返回 creds = 100 和 version=1

update credits set creds = creds*10, version=version+1 where user_id=1 and version=1;

update credits set creds = creds*10, version=version+1 where user_id=1 and version=1;

Always this ensure that whoever is having latest version number can only updates this record and dirty writes will not be allowed

始终确保拥有最新版本号的人只能更新此记录并且不允许脏写

回答by BIOHAZARD

There are is one critical point in your case when you decrease user`s current credit field by a requested amountand if it decreased successfully you do other operations and problem is in theory there can be many parallel requests for decrease operationwhen for example user has 1 credits on balance and with 5 parallel 1 credit charge requests he can purchase 5 things if request will be sent exactly on the same time and you end up with -4 credits on user`s balance.

当您将用户的当前信用字段减少请求的金额时，您的情况有一个关键点，如果成功减少您执行其他操作，问题是理论上可能有许多并行请求减少操作，例如用户有1 积分余额和 5 个并行 1 积分收费请求，如果请求将完全在同一时间发送，他可以购买 5 件东西，并且您最终在用户的余额上获得 -4 积分。

To avoid this you should decrease current credits value with requested amount(in our example 1 credit) and also check in where if current value minus requested amount is more or equal to zero:

为避免这种情况，您应该减少当前信用值与请求金额（在我们的示例中为 1 个信用），并检查当前值减去请求金额是否大于或等于零：

UPDATE credits SET creds = creds-1WHERE creds-1>=0and userid = 1

UPDATE credits SET creds = creds-1WHERE creds-1>=0and userid = 1

This will guaranty that user will never purchase many things under few credits if he will dos your system.

这将保证用户永远不会在很少的信用下购买很多东西，如果他愿意做你的系统。

After this query you should run ROW_COUNT() which tells if current user credit met criteria and row was updated:

在此查询之后，您应该运行 ROW_COUNT() 来判断当前用户信用是否符合条件并且行已更新：

UPDATE credits SET creds = creds-1 WHERE creds-1>=0 and userid = 1
IF (ROW_COUNT()>0) THEN 
   --IF WE ARE HERE MEANS USER HAD SURELY ENOUGH CREDITS TO PURCHASE THINGS    
END IF;

Similar thing in a PHP can be done like:

在 PHP 中类似的事情可以这样完成：

mysqli_query ("UPDATE credits SET creds = creds-$amount WHERE creds-$amount>=0 and userid = $user");
if (mysqli_affected_rows())
{
   \do good things here
}

Here we used nor SELECT ... FOR UPDATE neither TRANSACTION but if you put this code inside transaction just make sure that transaction level always provides most recent data from row (including ones other transactions already committed). You also can user ROLLBACK if ROW_COUNT()=0

在这里，我们使用了 SELECT ... FOR UPDATE 和 TRANSACTION 都没有，但是如果您将此代码放入事务中，请确保事务级别始终提供行中的最新数据（包括其他已提交的事务）。如果 ROW_COUNT()=0，您也可以使用 ROLLBACK

Downside of WHERE credit-$amount>=0 without row locking are:

WHERE credit-$amount>=0 没有行锁定的缺点是：

After update you surely know one thing that user had enough amount on credit balanceeven if he tries yo hack credits with many requests but you dont know other things like what was credit before charge(update) and what was credit after charge(update).

更新后，您肯定知道一件事，即用户有足够的信用余额，即使他尝试通过许多请求破解信用，但您不知道其他事情，例如收费前的信用（更新）和收费后的信用（更新）。

Caution:

警告：

Do not use this strategy inside transaction level which does not provide most recent row data.

不要在不提供最新行数据的事务级别内使用此策略。

Do not use this strategy if you want to know what was value before and after update.

如果您想知道更新前后的价值，请不要使用此策略。

Just try to rely on fact that credit was successfully charged without going below zero.

只需尝试依赖信用已成功收取而不会低于零的事实。

回答by Mark Sherretta

If you store a last update timestamp with the record, when you read the value, read the timestamp as well. When you go to update the record, check to make sure the timestamp matches. If someone came in behind you and updated before you, the timestamps would not match.

如果您将上次更新时间戳与记录一起存储，则在读取值时，也要读取时间戳。当您去更新记录时，请检查以确保时间戳匹配。如果有人在您后面并在您之前更新，则时间戳将不匹配。