Concurrently updating a field with READ_COMMITED

user_8067209 0
Viewed 33

Assume we have the following row in a table already:

INSERT INTO some_table (id, amount) VALUES (1, 0);

Having the following queries running at the same time with READ_COMMITED:

INSERT INTO some_table (id, amount) VALUES (1, 0)
ON CONFLICT DO
  UPDATE some_table SET amount=amount+100 WHERE id=1;

INSERT INTO some_table (id, amount) VALUES (1, 0)
ON CONFLICT DO
  UPDATE some_table SET amount=amount-50 WHERE id=1;

Can they run into race condition with resulting amount = 100 or amount = -50 if they both read initial (committed) amount=0 and one transaction overwrite the result of another one?

If yes, can it be fixed by:

  1. switching to REPEATABLE_READ ?
  2. using "FOR UPDATE" like this
WITH updating as (
    SELECT id, amount FROM some_table FOR UPDATE
)
INSERT INTO some_table (id, amount) VALUES (1, 0)
ON CONFLICT DO
  UPDATE some_table t SET t.amount=updating.amount+100 WHERE t.id=updating.id;

WITH updating as (
    SELECT id, amount FROM some_table FOR UPDATE
)
INSERT INTO some_table (id, amount) VALUES (1, 0)
ON CONFLICT DO
  UPDATE some_table t SET t.amount=updating.amount-50 WHERE t.id=updating.id;
isolation-level postgresql
1 Answers

INSERT ... ON CONFLICT does not suffer from race conditions. One of the UPDATEs will lock the row first, and the other has to wait, but no matter what the order is, in the end the amount will be 50 more than in the beginning.

Note that WHERE id = 1 is unnecessary, since the UPDATE will only affect the one row that conflicts with the INSERT anyway.

user_64643080
Related