How to query-and-increase a value (counter) in a thread-safe way? (avoid race conditions)

In a table where each row has a counter (just an integer value), I need to get the current value and increase it at the same time.

Effectively, I want to do this:

SELECT counter FROM table WHERE id=123
UPDATE table SET counter=counter+1 WHERE id=123

But doing this as two queries is obviously not thread-safe: multiple processes doing the same thing (on the same row) may get the same counter value. I need them all to be unique, so each process would get the actual current value and increase it by one.

I can think of a construction where I implement a manual lock per row, but I wonder if there's an easier way of doing this?

The update statements works perfectly fine without the select before! Since single statements are safe by definition, even two UPDATE queries performed at the same time only will result in the row incremented twice.

If you actually want to select the value for your PHP script, do something with it and later want to update this exact counter value, you can do the following:

BEGIN;
SELECT `counter` FROM `table` WHERE `id` = 123 FOR UPDATE;
UPDATE `table` SET `counter` = `counter`+1 WHERE `id` = 123;
COMMIT;

This starts a new transaction, then selects the rows you want to update and locks them exclusively. You can then safely update those without worrying about other clients changing their content or even accessing the locked rows. Finally you need to commit your changes.

You should also read something about isolation levels. You likely do not want a value like READ UNCOMMITTED as isolation level. Everything else should be fine for this use-case.

StackExchange Database Administrators Q#61603, answer score: 18