Is Insert ... Select An Atomic Transaction?

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

Solution 1:

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

Related:

• 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 INSERTis a transaction. Wrong terminology.

• In Postgres all locks are kept until and released at the end of the current transaction.