2

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 ......

Improve this question

2 Answers 2

Reset to default
5

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.

Improve this answer
4
  • I think that will work for a single row. Is there a way to make this work if I am inserting many rows in a single transaction? I don't want to rollback the entire transactiin, just skip the individual rows which I couldn't lock Commented Mar 30, 2023 at 2:50
  • You seem to be suffering from a disease called "long transactions". Cure that, and the whole problem will disappear. Commented Mar 30, 2023 at 5:51
  • Sometimes long transactions are out of your control. For example, if your clients are running on the JVM, it's impossible to completely eliminate long JVM pauses. I don't expect there to be long-running transactions, but I want a solution that can tolerate them. Commented Mar 30, 2023 at 7:39
  • I doubt that you cannot avoid long delays, even with Java. But be that as it may, I added something to the answer to address that. Commented Mar 30, 2023 at 8:29
1

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.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.