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 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.

More Related Contents:

  1. With MySQL, how can I generate a column containing the record index in a table?
  2. MySQL and GROUP_CONCAT() maximum length
  3. Which is faster: multiple single INSERTs or one multiple-row INSERT?
  4. MySQL convert date string to Unix timestamp
  5. How to speed up SELECT .. LIKE queries in MySQL on multiple columns?
  6. Using an Alias in SQL Calculations
  7. MySql Table Insert if not exist otherwise update
  8. How to get difference between two dates in months using MySQL query?
  9. MySQL – Control which row is returned by a group by
  10. Can MySQL replace multiple characters?
  11. Docker (Apple Silicon/M1 Preview) MySQL “no matching manifest for linux/arm64/v8 in the manifest list entries”
  12. My MySQL trigger doesn’t work, simple syntax, not complicated
  13. Using ORDER BY and GROUP BY together
  14. How can VBA connect to MySQL database in Excel?
  15. MySQL Pivot Table Column Data as Rows
  16. updating columns with a sequence number mysql
  17. Select a column with a keyword name
  18. Sum results of a few queries and then find top 5 in SQL
  19. How do I lag columns in MySQL?
  20. How to get multiple records against one record based on relation?
  21. SQL query to check if a name begins and ends with a vowel
  22. What is the best way to store media files on a database?
  23. SQL: deleting tables with prefix
  24. How to use a user variables in MySQL LIKE clause?
  25. INNER JOIN Results from Select Statement using Doctrine QueryBuilder
  26. Laravel MySql DB Connection with SSH
  27. Automatic Rollback if COMMIT TRANSACTION is not reached
  28. Get column sum and use to calculate percent of total (mySQL)
  29. MySQL cartesian product between two SELECT statements
  30. Sum until certain point – MySql

Leave a Comment