MySQL How To Select and Update in Single Statement - Increment Counter avoiding Race Condition

Question: How to get the current value of the counter, and set the new value in the single SQL statement to avoid a race condition?

Assume you a have a counter, and before you increment it, you need to get its current value. This current value will be used as an ID for some operation, so concurrent sessions must not get the same value.

Why not to use SELECT statement to get the current value and then UPDATE to update the counter:

  CREATE TABLE counters
  (
     id INT NOT NULL UNIQUE,   -- multiple counters can be stored in this table, this is its id
     value INT                            -- current value of the counter
  );
 
  -- Initialize the first counter with start value 10
  INSERT INTO counters VALUES (1, 10);
 
  -- Let's get the current value
  SELECT value FROM counters WHERE id = 1;
 
  -- Increment the counter
  UPDATE counters SET value = value + 1 WHERE id = 1;

The problem with this query is that concurrent sessions can execute SELECT at the same time and get the same current value.

Transaction and SELECT FOR UPDATE

To resolve this issue we can start a transaction and you SELECT FOR UPDATE in MySQL:

  START TRANSACTION;
 
  -- Let's get the current value
  SELECT value FROM counters WHERE id = 1 FOR UPDATE;
 
   -- Increment the counter
  UPDATE counters SET value = value + 1 WHERE id = 1;
 
  COMMIT;

Now if a transaction executes SELECT FOR UPDATE, all other concurrent transactions will be blocked on execution of their SELECT FOR UPDATE until the first transaction issues a COMMIT, or lock time-out expires.

Using transactions to increment counters is reliable but not perfect solution since locks can decrease the overall performance.

Fortunately, there is another option that does not require using transactions, and can select and update the counter with a single access to the table:

Session and Procedure Variables

This statement selects the current value of the counter and increments it in a single statement:

   UPDATE counters
   SET value = (@cur_value := value) + 1
   WHERE id = 1;

If UPDATE is executed as a standalone statement (not within a stored procedure), @cur_value is a session variable. You do not need to declare it.

To select its value, just execute:

  SELECT @cur_value;

If this UPDATE statement is executed in a stored procedure, you can declare a local variable @cur_value, and use it after the UPDATE statement (you do not need to execute SELECT @cur_value).

LAST_INSERT_ID Function

Instead of using a session or local variable, you can get the same functionality using LAST_INSERT_ID function.

LAST_INSERT_ID is typically used to get the latest value inserted into a AUTO_INCREMENT column, but it also has another feature.

If you pass a value to LAST_INSERT_ID, it is returned by the function, but the same value is returned by the next call to LAST_INSERT_ID without a parameter.

So to update a counter you can execute:

   UPDATE counters
   SET value = LAST_INSERT_ID(value) + 1
   WHERE id = 1;

Then to retrieve the current value (the value before update), you can execute

   SELECT LAST_INSERT_ID();

• MySQL Reference and Articles
• SELECT FOR UPDATE - MySQL 5.5 Reference Manual
• LAST_INSERT_ID - MySQL 5.6 Reference Manual

• SQLines - Database and SQL Conversion Tool
• SQLines Data - Data Transfer and Database Schema Migration Tool
• Free Online SQL Conversion Tool

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com