How can I insert into a Postgres table with a unique constraint without waiting on concurrent transactions?

Question

I have a table like this in a Postgres database:

CREATE TABLE tabwithunique (
    id SERIAL PRIMARY KEY,
    key BIGINT NOT NULL UNIQUE,
    value BIGINT NOT NULL
);

I want to design a statement that allows concurrent transactions to insert into this table without blocking on the unique constraint.

Instead, I would like "try-lock"-like behavior: I would like the insert to immediately return if a concurrent transaction is currently attempting to insert another row with the same key.

I tried using ON CONFLICT DO NOTHING, but it seems that Postgres still waits on concurrent transactions (so that it can return whether or not the insert succeeded):

Transaction 1	Transaction 2
BEGIN;
	BEGIN;
INSERT INTO tabwithunique (key, value) VALUES (100, 111) ON CONFLICT DO NOTHING; → INSERT 0 1
	INSERT INTO tabwithunique (key, values) VALUES (100, 222) ON CONFLICT DO NOTHING; (blocks) — this is what I DON'T want to happen. > I WANT this to immediately return INSERT 0 0.

I want to avoid unnecessary blocking, and I would instead like the second insert to immediately return, indicating that the insert failed due to another row with the same key, even though the other row is not yet committed. These transactions will be retried, so even if the other transactions ends up rolling-back, the row will eventually be inserted.

I'm running Postgres 13.9 with the default READ COMMITTED transaction isolation level.

Ideally, I would like an answer that works when inserting many rows at the same time using a insert-query like INSERT INTO tabwithunique (SELECT ......

Answer 1

You should

SET LOCAL lock_timeout = '1ms';

before you attempt the INSERT. Then you would get an error message if there is a lock conflict. Then you would get an error lock_not_available (SQLSTATE 55P03) if there is a conflict, which you can handle in your application.

Your comments suggest that you want to insert several rows in a single transaction. Then you don't want to abort the transaction if there is a conflict. For that, you can set savepoints before an INSERT, so that you can rollback to the savepoint in case of an error:

BEGIN;

SET LOCAL lock_timeout = '1ms';

SAVEPOINT a;

INSERT ...;

SAVEPOINT a;

INSERT ...;

COMMIT;

Whenever you encounter an error caused by a lock timeout, you ROLLBACK TO SAVEPOINT a;. But don't use more than 64 savepoints in a transaction, because that will lead to performance problems.

Answer 2

One approach I found uses Postgres's support for explicit "advisory locks".

While the row-level locks don't seem to support "try-lock" behavior for pending inserts, an explicit call to pg_try_advisory_xact_lock(unique_key) could be used to insert in a non-blocking way.

INSERT INTO tabwithunique (key, value)
SELECT * FROM (.......) to_insert
WHERE pg_try_advisory_xact_lock($LOCKING_NAMESPACE_VALUE, to_insert.key)

This has some drawbacks:

• Advisory locks only support locking one 64-bit key, or two 32-bit keys. This means you will need to convert your UNIQUE column into a small integer.
• It also doesn't have any built-in support for namespacing; all uses of advisory locks are locking in the same space. To avoid different uses of advisory locks conflicting, you can use one 32-bit value as the "namespace".
• Because advisory locks are explicit, it requires cooperation from other inserters.