36

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?

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
bodacydo
  • 75,521
  • 93
  • 229
  • 319
  • I suggest you to search for topic: "Lock on table" Mysql feature. Locking a table permits to solve this sort of situation – cesko80 Mar 02 '10 at 15:36

2 Answers2

61

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
nos
  • 223,662
  • 58
  • 417
  • 506
  • If you also want to retrieve the value of **tries** atomically then: `update table set last_insert_id(tries=tries+1) where condition=value;` `select last_insert_id()`. [MYSQL Locking Reads DOCS](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html) – Madacol Feb 11 '20 at 23:56
17

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
John Kugelman
  • 349,597
  • 67
  • 533
  • 578
  • What happens if both connections update `tries=tries+1` - they both set it to 4. I have been developing the program alone but when the users start using it, they may get wrong results. :( – bodacydo Mar 02 '10 at 15:36
  • 1
    A single UPDATE statement will be atomic. You don't have to worry about conflicts if you do the read and update in one statement. – John Kugelman Mar 02 '10 at 15:38
  • Does this also hold for more complex where conditions where i for example make a sub select on that same table (with the nested selects that fill a temp table) – tObi Apr 13 '17 at 23:53