How to Prevent Lost Updates: Atomic Writes, Explicit Locks, and CAS

2.3.1 Atomic Write

Many databases support atomic update operations that avoid the read‑modify‑write pattern. Using these operations is usually the best solution. For example, a safe concurrent increment in most relational databases can be expressed as:

UPDATE counters SET value = value + 1 WHERE key = 'foo';

Similar atomic primitives exist in document stores (e.g., MongoDB) and key‑value stores (e.g., Redis) for modifying parts of a JSON document or data structures.

2.3.2 Explicit Locking

If the database lacks built‑in atomic operations, applications can acquire exclusive locks on the rows they intend to update. A typical pattern is:

BEGIN TRANSACTION;
SELECT * FROM figures WHERE name = 'robot' AND game_id = 222 FOR UPDATE;
-- validate the move
UPDATE figures SET position = 'c4' WHERE id = 1234;
COMMIT;

This ensures no other transaction can read the locked rows until the transaction completes, preventing lost updates.

2.3.3 Automatic Lost‑Update Detection

Some DBMSes (PostgreSQL, Oracle, SQL Server) can automatically detect lost updates under snapshot or serializable isolation levels and abort the offending transaction. MySQL’s repeatable‑read does not detect lost updates, so it is considered unsafe for snapshot isolation.

2.3.4 Compare‑And‑Swap (CAS)

Databases without transactions may still offer CAS semantics: an update succeeds only if the current value matches the previously read value. Example for a wiki page:

UPDATE wiki_pages SET content = 'new content'
WHERE id = 1234 AND content = 'old content';

If the content has changed, the update fails and the application must retry.

2.3.5 Conflict Resolution and Replication

In multi‑replica databases, preventing lost updates also requires handling concurrent writes across replicas. Traditional locking or CAS assumes a single latest replica, which does not hold for multi‑master or leaderless replication. Some systems (e.g., Riak 2.0) provide commutative data types that allow concurrent increments or set additions to be merged automatically, while last‑write‑wins (LWW) strategies can still lose updates.

Overall, the article emphasizes choosing the most appropriate technique—atomic operations when available, explicit locks otherwise, automatic detection where supported, or CAS for optimistic concurrency—to ensure data integrity in concurrent write scenarios.