如何处理数据库中的并发更新？ [英] How to deal with concurrent updates in databases?

问题描述

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

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

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

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

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

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

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

• 更新credit set creds = 150其中userid = 1;

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

在这种情况下，应用程序检索当前值，计算新值（150）并执行更新。如果别人在同一时间做同样的事情，那就是灾难。
我猜测包装当前值的retreival并更新事务将解决，例如。 开始;选择来自信用的凭证，其中userid = 1;做应用逻辑来计算新值，更新信用点数= 160其中userid = 1; end; 在这种情况下，您可以检查新的信用是否<

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.

更新信用集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.

简单来说，接受的处理方法是什么

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

推荐答案

使用事务：

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

一些重要注意事项：

• 并非所有数据库类型都支持事务。特别是，mysql的默认数据库类型MyISAM不能。如果您使用mysql，请使用InnoDB。


• 由于您无法控制的原因，交易可能会中止。如果发生这种情况，您的应用程序必须准备从BEGIN工作中重新开始。


• 您需要将隔离级别设置为SERIALIZABLE，否则第一个选择可以读取数据其他事务尚未提交（事务不像编程语言中的互斥体）。


• 某些数据库会提供SELECT .. FOR UPDATE，它将锁定所检索的行

• Not all database types support transactions. In particular, mysql's default database type, MyISAM, doesn't. Use InnoDB 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.

将事务与SQL存储过程组合可以使后面的部分更容易处理;应用程序将只调用事务中的单个存储过程，如果事务中止，则重新调用它。

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.

这篇关于如何处理数据库中的并发更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！