I came across a situation where for concurrency of two requests trying to make the same resource in the database, I wanted the other requests to block until the resource was inserted and then continue without error.
A similar problem is perhaps trying to determine the largest invoice number in a system and acquire the next number for the invoice you are inserting, without an error because concurrent requests inserted the same invoice number.
I had to think about this one quite a bit to find alternatives. I researched how to lock a non-existing record and it appears to me that you can’t in PostgreSQL even at ISOLATION LEVEL SERIALIZABLE.
I did find that in READ COMMITTED that “UPSERT” (INSERT … ON CONFLICT) can help solve the problem by blocking until another transaction inserts the record and then not attempt to insert it again.
1 2 | -- set up a test tablecreate table t (id text primary key, other text); |
This works: Using ON CONFLICT to make sure that the record is only inserted once and is not updated by a second concurrent transaction:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -- run two transactions with these statements (statement for statement)begin transaction isolation level read committed;select txid_current();-- optionally abort if it already existsselect * from t where id = 'tryupsert';-- optionally if found exitINSERT INTO t (id, other) VALUES ('tryupsert', txid_current()::text)ON CONFLICT (id) DO NOTHING;-- second transaction blocks on the aboveselect * from t where id = 'tryupsert';commit;-- second transaction unblocked now-- The first transaction inserted the row if it did not exist-- The second transaction executed the DO NOTHING. |
This works: Using ON CONFLICT to make sure that the record exists and the last transaction wins on the other column value (all transactions change the value of the row):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | -- run two transactions with these statements (statement for statement)begin transaction isolation level read committed;select txid_current();INSERT INTO t (id, other)SELECT 'tryupsert' AS id, txid_current()::text AS otherON CONFLICT (id) DO UPDATE SET (id, other) = (excluded.id, excluded.other)RETURNING *;-- second transaction blocks on the abovecommit;-- second transaction unblocked now-- The first transaction inserted the row if it did not exist-- or updated it if it did exist.-- The second transaction updated the row. |
This works: Using advisory locks:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- run two transactions with these statements (statement for statement)begin transaction isolation level read committed;select txid_current();select * from t where id = 'advisory';-- if found exitselect pg_advisory_xact_lock(hashtext('advisory'));-- second transaction blocks on the aboveselect * from t where id = 'advisory';-- if found exit-- second transaction will unblock when first tx commits and see the row -- was already inserted from the first transaction and skip the insert.INSERT INTO t (id, other) VALUES ('advisory', txid_current()::text);select * from t where id = 'advisory';commit; |
Using SERIALIZABLE doesn’t work. This makes sense after understanding better (yet again because I keep forgetting how it works – it is kind of designed for less blocking and code that is built to retry on serialization failure)
1 2 3 4 5 6 7 8 9 10 11 12 | begin transaction isolation level serializable;select * from t where id = 'tryserializable' for update;-- if found exit-- second transaction does not blockinsert into t values ('tryserializable', txid_current()::text);-- second transaction block on the aboveselect * from t where id = 'tryserializable';-- id | other-- -----------------+--------- tryserializable | 628commit; |
The second transaction ends with this error:
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during write.
HINT: The transaction might succeed if retried.