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.
-- set up a test table create 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:
-- run two transactions with these statements (statement for statement)
begin transaction isolation level read committed;
select txid_current();
-- optionally abort if it already exists
select * from t where id = 'tryupsert';
-- optionally if found exit
INSERT INTO t (id, other) VALUES ('tryupsert', txid_current()::text)
ON CONFLICT (id) DO NOTHING;
-- second transaction blocks on the above
select * 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):
-- 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 other ON CONFLICT (id) DO UPDATE SET (id, other) = (excluded.id, excluded.other) RETURNING *; -- second transaction blocks on the above commit; -- 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:
-- 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 exit
select pg_advisory_xact_lock(hashtext('advisory'));
-- second transaction blocks on the above
select * 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)
begin transaction isolation level serializable;
select * from t where id = 'tryserializable' for update;
-- if found exit
-- second transaction does not block
insert into t values ('tryserializable', txid_current()::text);
-- second transaction block on the above
select * from t where id = 'tryserializable';
-- id | other
-- -----------------+-------
-- tryserializable | 628
commit;
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.