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

by

Here’s 3 different approaches:

Atomic update

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

and it will be done atomically.

Use row locking

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

select tries from table where condition=value for update;
.. do application logic to add to `tries`
update table set tries=newvalue where condition=value;

All other queries trying to read the same row will have to wait, until the table gets updated, and they will return the updated value.

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.

Leave a Comment