Locking on rows or values that don’t exist in PostgreSQL

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.