LEARN. PRACTICE. GROW.Understand the concept
Two sessions may read the same state and produce concurrent updates. A transaction does not automatically eliminate every anomaly; isolation and access pattern matter. Optimistic control may include a version in an UPDATE predicate and check affected rows. Zero affected rows requires conflict handling rather than assuming success.
Apply and decide
A UNIQUE constraint can help prevent duplicates for an appropriate key. Design must define that key’s meaning and repetition handling. Safe reruns also depend on external effects. After a communication failure during commit, the client may not know whether commit succeeded; state lookup by a stable key may be necessary.
Guided workplace application
To edit a ticket, retain the read version and update only while ID and version still match, incrementing the version in the same write. Check affected row count: zero requires assessing absence or conflict, not returning success or removing protection. All write paths must participate in the protocol. For operations locking several rows, consistent ordering helps reduce deadlocks; provide abort handling and bounded retry of the whole transaction. In Read Committed, two reads may observe commits occurring between statements. Choose isolation according to the rule instead of assuming BEGIN freezes every read. If the COMMIT response is lost, reconcile using the stable key and confirm external effects separately before retrying.
UPDATE tasks SET status =:status, version = version + 1 WHERE id =:id AND version =:expected; the caller checks affected rows and handles conflicts.
Common pitfalls
Ignoring zero rows; retrying after uncertain COMMIT with a new key; holding locks during human interaction.
Related topics: Query with intent · Join without multiplying mistakes
Concurrency and repetition need observable criteria, not just BEGIN and COMMIT.
Reference: PostgreSQL 18: Transaction isolation · PostgreSQL 18 reference semantics; DR SQL 2026.2