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
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|
→ INSERT 0 1
(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 ......