How to deal with concurrent updates in databases?

Question

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

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

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.

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.

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

Answer 1

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.

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.

Answer 2

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

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 :

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.

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:

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

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