Database Concurrency Safety Pack

Three bugs that look unrelated often share one cause: your code assumes only one request is touching a row.

A stock check passes twice and one seat gets sold to two people.
A process dies between a debit and a credit, leaving half a transfer behind.
Two editors save the same record and the later click silently erases the earlier change.

This pack covers all three. The database should decide who wins, which writes belong together, and when an edit is too old to save.

Pick the right protection

Atomic check and write

Do not read stock, check it in application code, then write it back. The gap between the read and write is the race.

Use one conditional statement:

UPDATE inventory
SET available = available - 1
WHERE item_id = $1 AND available > 0;

Then inspect the affected-row count. One row means the reservation won. Zero rows means nothing was available. For a more complicated decision, use a transaction with SELECT ... FOR UPDATE, keep the lock window short, and perform every dependent check inside it.

All-or-nothing multi-write transaction

Writes that represent one business event need one transaction boundary:

BEGIN
debit account A
credit account B
write transfer record
COMMIT

Inject a failure after each step. Every injected failure must leave the database exactly as it was before the transaction. Email, payment providers, and queues are outside the database transaction, so record an outbox event in the same commit and deliver it afterwards.

Optimistic locking for edits

Add a version column. Every edit sends the version it loaded:

UPDATE customers
SET phone = $1, version = version + 1
WHERE id = $2 AND version = $3;

If zero rows change, return a conflict such as HTTP 409 with the current record. Let the user reload, compare, or merge. Last-write-wins is not conflict handling. It is silent data loss.

Verification checklist

Release two requests from a barrier against one remaining unit. Exactly one succeeds.
Inject a crash after the first write in a multi-write operation. Every write rolls back.
Save two edits that both started at version 7. The first reaches version 8; the second receives a visible conflict.
Confirm logs include the transaction or request ID, affected-row count, starting version, and outcome.
Retry the winning request with the same idempotency key and prove the business action is not repeated.

The starter is intentionally dry-run first. It validates the schema, protected records, transaction policy, and conflict rules, then returns an atomic-write plan, transaction boundary, optimistic-locking contract, and test matrix for review. It never changes production data.

Download the runnable pack

Start with the complete pack or take one file:

Use RACE for atomic check-and-write, ATOMIC for multi-write transactions, and VERSION for optimistic locking.