Is INSERT ... SELECT an atomic transaction?

Question

I use a query like this:

INSERT INTO table
     SELECT * FROM table2 t2
        JOIN ...
        ...
        WHERE table2.date < now() - '1 day'::INTERVAL
     FOR UPDATE OF t2 SKIP LOCKED
ON CONFLICT (...)
    DO UPDATE SET ...
RETURNING *;

My question is about FOR UPDATE t2 SKIP LOCKED. Should I use it here? Or will Postgres lock these rows automatically with INSERT SELECT ON CONFLICT till the end of the transaction?

My goal is to prevent other apps from (concurrently) capturing rows with the inner SELECT which are already captured by this one.

There is no reason to automatically lock rows from a subquery in `INSERT ... ON CONFLICT` because the subuery is executed once. You should lock the rows explicitly. — klin, Oct 19 '17 at 14:22

Erwin Brandstetter · Accepted Answer · 2017-10-19T14:35:06.920

Yes, FOR UPDATE OF t2 SKIP LOCKED is the right approach to prevent race conditions with default Read Committed transaction isolation.

The added SKIP LOCKED also prevents deadlocks. Be aware that competing transactions might each get a partial set from the SELECT - whatever it could lock first.

While any transaction is atomic in Postgres, it would not prevent another (also atomic) transaction from selecting (and inserting - or at least trying) the same row, because SELECT without FOR UPDATE does not take an exclusive lock.

The Postgres manual about transactions:

A transaction is said to be atomic: from the point of view of other transactions, it either happens completely or not at all.

Postgres UPDATE … LIMIT 1

Clarifications:

An SQL DML command like INSERT is always automatically atomic, since it cannot run outside a transaction. But you can't say that INSERT is a transaction. Wrong terminology.
In Postgres all locks are kept until and released at the end of the current transaction.

Is INSERT ... SELECT an atomic transaction?

1 Answers1

Linked