Picking a Guard Against Race Conditions — Atomic, Lock, Retry, Serializable Diagram

Atomic write operations remove the read-modify-write cycle entirely: UPDATE counters SET value = value + 1, or document and structure ops in MongoDB and Redis
Explicit locking locks the rows you are about to change (SELECT ... FOR UPDATE); good when application logic decides the write, but deadlocks need a retry
Automatic detection lets both transactions run and aborts the loser — PostgreSQL repeatable read, Oracle serializable, and SQL Server snapshot do this; MySQL/InnoDB does not
A conditional write (compare-and-set) or a version column — optimistic locking — works where transactions are missing; MVCC lets the WHERE clause see writes the snapshot hides

Pattern: a SELECT checks a requirement, the application decides, a write makes the decision — and the write invalidates the premise of the check
A phantom is a write that changes the result of another transaction's search; snapshot isolation stops phantoms in read-only queries but not in read-write ones
If the check matches no rows, SELECT FOR UPDATE has nothing to lock — the rows do not exist yet
ORM-generated SQL is prone to exactly this: silent read-modify-write cycles and unguarded check-then-write

Materializing conflicts means inventing lockable rows (a table of room-and-timeslot locks); it works but leaks the concurrency mechanism into the data model
Weak isolation leaves some anomalies to the application developer — and nobody can tell by reading the code whether a level is safe enough
Race conditions are rare in testing and catastrophic in production: a Bitcoin exchange was bankrupted and customer data has been corrupted
An attacker can deliberately fire highly concurrent requests to exploit such bugs, so prevention has to be systematic