Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to make sure there is no race condition in MySQL database when incrementing a field?

How to prevent a race condition in MySQL database when two connections want to update the same record?

For example, connection 1 wants to increase "tries" counter. And the second connection wants to do the same. Both connections SELECT the "tries" count, increase the value and both UPDATE "tries" with the increased value. Suddenly "tries" is only "tries+1" instead of being "tries+2", because both connections got the same "tries" and incremented it by one.

How to solve this problem?

like image 593
bodacydo Avatar asked Mar 02 '10 15:03

bodacydo


People also ask

How do you avoid race condition in database?

To prevent the race conditions from occurring, you can lock shared variables, so that only one thread at a time has access to the shared variable.

What is database race condition?

In computer memory or storage, a race condition may occur if commands to read and write a large amount of data are received at almost the same instant, and the machine attempts to overwrite some or all of the old data while that old data is still being read.

What is select for update in MySQL?

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.


2 Answers

Here's 3 different approaches:

Atomic update

update table set tries=tries+1 where condition=value;

and it will be done atomically.

Use transactions

If you do need to first select the value and update it in your application, you likely need to use transactions. That means you'll have to use InnoDB, not MyISAM tables. Your query would be something like:

BEGIN; //or any method in the API you use that starts a transaction
select tries from table where condition=value for update;
.. do application logic to add to `tries`
update table set tries=newvalue where condition=value;
END;

if the transaction fails, you might need to manually retry it.

Version scheme

A common approach is to introduce a version column in your table. Your queries would do something like:

select tries,version from table where condition=value;
.. do application logic, and remember the old version value.
update table set tries=newvalue,version=version + 1 where condition=value and version=oldversion;

If that update fails/returns 0 rows affected, someone else has updated the table in the mean time. You have to start all over - that is, select the new values, do the application logic and try the update again.

like image 89
nos Avatar answered Oct 23 '22 22:10

nos


Use a single statement instead of two. A single UPDATE statement that performs both the read and the write will be atomic and won't conflict with another simultaneous update.

UPDATE table SET tries = tries + 1 WHERE ...

Or you can use transactions to make the two operations atomic.

BEGIN
SELECT ...
UPDATE ...
COMMIT

Or, more primitively, lock the table while you're reading/writing to it.

LOCK TABLES table WRITE
SELECT ...
UPDATE ...
UNLOCK TABLES
like image 41
John Kugelman Avatar answered Oct 23 '22 22:10

John Kugelman