Transactions & competing workers
Protect invariants when several processes observe and update the same data.
BY THE END, YOU CAN
- Explain atomicity and isolation separately
- Identify a check-then-act race
- Design a claim operation with a database-enforced condition
Diagram description: 1. Worker A → Database: read job 7: queued; 2. Worker B → Database: read job 7: queued; 3. Worker A → Database: claim 7; 4. Worker B → Database: claim 7 (duplicate!); 5. Database: UPDATE … WHERE status='queued'; 6. Database → Worker B: 0 rows: someone else won
A group of changes needs a boundary
A transaction groups database operations so they commit together or roll back together.
Atomicity prevents a partially committed group, but does not automatically make every concurrent algorithm correct. Isolation controls what concurrent transactions can observe and how their effects interact. Durability concerns committed data surviving the failures covered by the database's configuration and storage guarantees.
State the invariant you need first: for example, at most one current claim should be issued for a particular pending job. Then choose mechanisms that enforce that invariant.
Two readers can make the same decision
Imagine two workers both read that job 42 is pending.
Each then updates it to running. A transaction around each pair of statements may still permit this outcome at a common isolation level unless the query or locking strategy prevents it. One approach uses an atomic conditional update and checks how many rows changed.
Another uses row locks while selecting work; PostgreSQL's SKIP LOCKED can support queue-like claiming patterns. The exact behavior depends on the database, so do not assume SQLite and PostgreSQL have identical locking semantics.
Plan for conflicts and external effects
Serializable transactions provide a strong model but may abort transactions that must then be retried from the beginning.
Keep retries bounded and ensure the transaction body can safely run again. A database rollback cannot unsend an email or reverse an unrelated remote API call. For that boundary, record durable intent in the transaction and perform the external effect through a separate process with an idempotency strategy.
In Relay, claiming work, performing it, and acknowledging it are distinct steps with distinct crash windows.
COMMON FAILURE MODE
Wrapping a read-then-write sequence in a transaction is not a substitute for checking the isolation and locking semantics you require.
TRY IT YOURSELF
Write the interleaving of two workers that both read pending before either writes running. Propose a conditional update and explain how the losing worker detects the conflict.
Make the call in the laboratory.
The two-question checkpoint, your notes, and your saved progress are in the laboratory. Free, with no account.
Open in the laboratory ↗CONNECT IT TO RELAY
Specify Relay's atomic claim operation and test it with two competing claim attempts. Document the behavior of your chosen database.