Read and Write Anomalies in Concurrent Transactions and Their Mitigation Strategies

During concurrent transaction execution, several read anomalies (dirty read, nonrepeatable read, phantom read) and write anomalies (lost update, dirty write, write skew) may occur.

Read anomalies: Dirty read occurs when a transaction reads changes made by another transaction that have not yet been committed. Nonrepeatable read (also called fuzzy read) happens when the same transaction reads the same row twice and obtains different values because another transaction modified it in between. Phantom read occurs when two identical queries return different row sets because another transaction inserted or deleted rows between the queries; the focus is on newly added or removed rows.

Write anomalies: Lost update happens when two transactions read the same value, both update it, and the later commit overwrites the earlier one, causing the first update to be lost. Dirty write refers to a transaction that reads an uncommitted value (a dirty read) and then writes based on that value. Write skew describes a situation where each individual transaction satisfies all constraints, but the combined effect of their commits violates a constraint.

The distinction between nonrepeatable and phantom reads is that nonrepeatable reads involve a change to the same data item, whereas phantom reads involve the appearance of new rows that match the query criteria.

SQL standards define four isolation levels, trading off between concurrency and consistency. Lower isolation levels reduce overhead but increase anomalies. Most databases default to read committed (e.g., Oracle) or repeatable read . InnoDB’s default is repeatable read, which uses Multi‑Version Concurrency Control (MVCC) and gap locks to prevent dirty reads, nonrepeatable reads, and phantom reads.

Mitigation strategies for read and write anomalies:

1. Atomic write operations : many databases provide atomic update statements that avoid the “read‑modify‑write” pattern. For example:

UPDATE person SET age = age + 1 WHERE name = 'XXXX';

Atomic operations often acquire an exclusive lock on the target row, preventing other transactions from reading it until the update is committed.

2. Explicit locking : if the database lacks built‑in atomic updates, the application can explicitly lock the object before performing a read‑modify‑write sequence, forcing other transactions to wait.

3. Automatic lost‑update detection : some transaction managers can detect a potential lost update, abort the conflicting transaction, and force a safe read‑modify‑write retry.

4. Compare‑and‑set (CAS) : in systems without transaction support, a CAS operation updates a value only if it has not changed since it was last read (often using a version number). If the value changed, the operation falls back to a read‑modify‑write cycle.

Note that InnoDB’s repeatable‑read isolation does not automatically detect lost updates, so developers may need to employ one of the above techniques to ensure data integrity.