A transaction groups several database changes so they commit together or not at all. It does not automatically resolve every race between two people editing the same record. An optimistic version field lets a write state which version it read. The database updates only when that version still matches; a zero-row update means the editor is stale and must reconcile. A transaction can then add an inspection note and advance the case version together. Isolation level, constraints, and retry policy still matter. Retrying the entire transaction after a serialization failure is different from blindly repeating an external side effect.
Make this comfortable
Transactions and Concurrent Writes
Working case
Two reviewers open case 47 at version 6. Reviewer 29 saves first and advances it to 7. Reviewer 31's update with expected version 6 affects no row and receives a conflict state rather than overwriting the first note. The interface can show the current record and let reviewer 31 decide what to do. If a transaction also queues a notification, the queue instruction belongs in the same database boundary. An email sent before commit would be impossible to roll back if the record write failed.
Implementation
UPDATE cases
SET status = 'reviewed', version = version + 1
WHERE case_id = 47 AND version = 6
RETURNING case_id, version;
-- A zero-row result is a conflict; do not insert dependent work.Cost and boundaries
A version comparison is O(1) per indexed row but may wait on a concurrent lock. A transaction keeps locks or a snapshot until commit, so long transactions reduce throughput and raise contention. Retrying after conflict adds work; cap retries and surface a user choice when edits are semantic, not automatically mergeable. The SQL shows a compare-and-swap update. The application must check whether a row was returned before inserting dependent work and committing.
Common Mistakes
- Do not assume a transaction prevents lost updates without a concurrency rule.
- Do not send irreversible external effects before commit.
- Do not retry a human edit conflict as though it were a transient database error.
Connected lessons
Data Persistence; Relational Records and Database Constraints; Indexes and Query Plans for Case Feeds; Expand-and-Contract Schema Migrations; Form submission: validate on the server and return field errors; Authorization: check permission for this record on every request; HTTP caching: validate a changed representation with an ETag.
Failure trace
Two reviewers load case version 12, each changes its status, and both submit. Without a version condition, the second commit silently erases the first. A transaction makes a group of statements atomic, but it does not automatically express this business conflict. Use a conditional update on the expected version or an appropriate lock and report a conflict when no row is changed.
Verification
- Run two writes using the same expected version and assert exactly one changes the record.
- Crash between two related statements and confirm neither partial state remains committed.
- Retry a serialization failure from the whole transaction boundary, not from one statement in isolation.
Decision note
Isolation level and locking choices affect contention. Start with the invariant to protect, measure conflicting writes, and choose the narrowest mechanism that preserves the promised result.
Apply and check
Build Project: paginated inspection feed with safe writes; then check the boundary with Web Development: data and API contracts quiz.
Further connections
Server-Priced Order Snapshots and Money Units; Refunds, Reversals, and Payment Ledger State.
Further connections
web-tech
web-development