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