The Compare And Set In Databases
Compare and set, sometimes called compare and swap, is the database expression of optimistic concurrency. Instead of locking, you write an update whose WHERE clause names the value you expect to find. The update changes the row only if that expectation still holds.
A typical pattern adds a version column. You read the row and its version, then issue an update that sets new fields and increments the version, but only WHERE the id and the old version both match. The database reports how many rows were affected. If the answer is zero, somebody else updated the row first and your attempt is stale.
This works because the database evaluates the WHERE clause atomically as part of the write. No separate read and check can interleave, so two racing clients cannot both see one row affected.
- No locks held across application logic or user think time.
- Self detecting Zero rows affected is an unambiguous conflict signal.
- Retry friendly The loser simply re reads and tries again.
The same idea powers conditional puts in key value stores and the atomic counters many systems expose.
Key idea
Compare and set updates a row only when its expected value or version still matches, turning a conflict into a zero rows affected result the client can retry.