Concurrency Problems and Data Conflicts

In any multi-user database system, multiple transactions execute concurrently, and without careful management, they can interfere with one another in subtle but destructive ways. These interferences are collectively known as concurrency problems or data conflicts. Understanding them is foundational to designing reliable systems, because the consequences — ranging from stale reports to vanishing financial records — are logical errors that produce no runtime exceptions and leave no obvious traces. The database engine simply does what it was told; the problem lies in the order and timing of those instructions across concurrent sessions.

Concurrency problems arise because transactions are not instantaneous. They take time to read data, perform computations, and write results. During that window, other transactions are doing the same. The four classic problems — dirty reads, non-repeatable reads, phantom reads, and lost updates — each describe a different way that this temporal overlap can corrupt the logical consistency of data.

Dirty Reads

A dirty read occurs when one transaction reads data that has been modified by another transaction that has not yet been committed. The word "dirty" refers to the fact that the data is in an intermediate, unstable state — it exists in the database's working memory but has not been permanently saved. If the modifying transaction later issues a ROLLBACK, those changes are erased entirely, yet the reading transaction has already acted on them.

Consider a practical example involving a bank transfer. Transaction A begins a transfer of $500 from Account 1 to Account 2. It first deducts $500 from Account 1, bringing its balance from $1,000 to $500. At this exact moment — before Account 2 has been credited and before Transaction A commits — Transaction B reads Account 1's balance and sees $500. Transaction B uses that balance to determine whether a loan can be approved. Then Transaction A encounters an error and rolls back, restoring Account 1's balance to $1,000. Transaction B has made a lending decision based on a balance figure that never officially existed.

-- Transaction A (not yet committed)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- At this point, Transaction B reads balance = 500 (a dirty read)
-- Transaction A then rolls back
ROLLBACK;
-- The balance is restored to 1000, but Transaction B already used 500

Dirty reads can lead to incorrect calculations, erroneous reports, and flawed business logic. A reporting query might show revenue figures that were later rolled back. An inventory system might show stock levels that were part of an incomplete reservation. Any decision made on the basis of uncommitted data is potentially built on a fiction. The risk is especially acute in systems with long-running write transactions, such as batch imports or complex multi-step business processes, because the window during which dirty data is visible is proportionally longer.

Non-Repeatable Reads

A non-repeatable read occurs when a transaction reads the same row twice and gets different values each time, because another transaction modified and committed that row between the two reads. Unlike a dirty read, the data seen in the second read is perfectly legitimate — it represents a committed, durable change. The problem is not that the data is invalid; the problem is that a single transaction is supposed to operate on a consistent snapshot of the world, and that snapshot has shifted beneath its feet.

Imagine a payroll processing transaction that first reads an employee's hourly rate to validate it against a policy range, then reads it again later in the same transaction to calculate the pay. Between those two reads, a manager commits a pay raise. The first read returned $25/hour and passed validation; the second read returns $30/hour and is used for calculation. The transaction has unknowingly used two different values for the same fact within a single logical unit of work.

-- Transaction A: payroll processing
BEGIN TRANSACTION;
SELECT hourly_rate FROM employees WHERE emp_id = 42;
-- Returns 25.00 — validation passes

-- Meanwhile, Transaction B commits a pay raise:
-- UPDATE employees SET hourly_rate = 30.00 WHERE emp_id = 42; COMMIT;

SELECT hourly_rate FROM employees WHERE emp_id = 42;
-- Now returns 30.00 — non-repeatable read
COMMIT;

Non-repeatable reads are particularly dangerous in transactions that perform multi-step calculations or comparisons spanning multiple queries. A transaction might read a total budget, then read individual line items to verify they sum to that total — only to find they do not, because one line item changed between reads. The difference from a dirty read is critical: here the conflicting transaction is committed, so there is nothing "wrong" with the new data in isolation. The wrongness lies in the inconsistency experienced within the single ongoing transaction.

Phantom Reads

A phantom read is similar in spirit to a non-repeatable read but operates at the level of entire rows rather than individual column values. It occurs when a transaction executes the same query twice and obtains a different set of rows the second time, because another transaction inserted or deleted rows that match the query's conditions between the two executions. The "phantom" rows appear or disappear like ghosts — present in one reading, absent in another (or vice versa).

Consider an airline reservation system. Transaction A is preparing a report on available seats in the economy cabin of a flight. It first counts 12 available seats, then proceeds to allocate them according to a waiting list. Before it finishes, Transaction B books 3 of those seats and commits. When Transaction A queries the available seats again to confirm its allocations, it finds only 9. The 3 rows that disappeared were not modified — they were replaced with new booking records. Transaction A is dealing with a structurally different result set than it started with.

-- Transaction A
BEGIN TRANSACTION;
SELECT COUNT(*) FROM seats WHERE flight_id = 101 AND status = 'available';
-- Returns 12

-- Transaction B books 3 seats and commits:
-- UPDATE seats SET status = 'booked' WHERE seat_id IN (5,6,7) AND flight_id = 101; COMMIT;

SELECT COUNT(*) FROM seats WHERE flight_id = 101 AND status = 'available';
-- Returns 9 — phantom read (rows have changed set membership)
COMMIT;

Phantom reads directly undermine aggregate results. Counts, sums, averages, and minimum/maximum values can all differ across executions within the same transaction if rows are being inserted or deleted concurrently. This makes phantoms especially harmful in financial reconciliation, inventory management, and capacity planning. A sum of all orders might differ from the sum of individual order lines if new orders arrive mid-transaction. Phantom reads are also among the hardest concurrency problems to eliminate. Standard row-level locking prevents modifications to existing rows but does not prevent new rows from appearing. Preventing phantoms typically requires range locks or predicate locks, mechanisms only available at the highest isolation levels, and they come with significant performance trade-offs.

Lost Updates

A lost update occurs when two transactions both read the same value, both independently compute a new value based on what they read, and both write their new value back — with the second write overwriting the first. The first transaction's update is effectively erased. No error is raised. No constraint is violated. The database is in a consistent state by its own rules; it is just missing a legitimate, committed change.

The classic scenario is a counter or accumulator. Two customers simultaneously purchase the last item in stock. Transaction A reads quantity = 1, Transaction B reads quantity = 1. Transaction A computes 1 - 1 = 0 and writes 0. Transaction B, working from its own read of 1, also computes 0 and writes 0. The quantity ends up at 0, which looks correct, but both customers have been sold the same item. Alternatively, consider two staff members simultaneously adding to a shared budget figure:

-- Both transactions read the current value
-- Transaction A reads budget = 10000
-- Transaction B reads budget = 10000

-- Transaction A adds 5000 and writes back
UPDATE budget SET total = 15000 WHERE dept_id = 3;
COMMIT;

-- Transaction B adds 3000 based on its own read and writes back
UPDATE budget SET total = 13000 WHERE dept_id = 3;
COMMIT;

-- Final value: 13000. Transaction A's update is lost.
-- Correct value should be: 10000 + 5000 + 3000 = 18000

Lost updates are particularly treacherous in application-level read-modify-write patterns, where the application reads a value into memory, performs a calculation in code, and then issues an UPDATE with the computed result. This pattern is ubiquitous — it appears in e-commerce cart management, point-of-sale inventory, financial ledger entries, and scheduling systems. Because the database itself never sees the intermediate state, it cannot detect the conflict. The consequences include inventory inaccuracies, double-bookings, and financial discrepancies that may only surface during audits or reconciliation runs, sometimes long after the fact.

How Data Conflicts Compromise Integrity

What makes concurrency problems especially insidious is their cascading nature. A single dirty read or lost update does not stay isolated. Systems are built on chains of dependent operations: a balance read feeds a transfer, which feeds a statement, which feeds a compliance report. If the first value in that chain is corrupted, every downstream operation inherits and potentially amplifies the error. By the time the discrepancy is noticed, it may be embedded in dozens of derived records.

Unlike hardware failures, which trigger exceptions, trigger logs, and alert monitoring systems, concurrency conflicts are silent. The database performs every operation exactly as instructed. No constraint fires. No error message appears. A dirty read succeeds because the data technically exists at that moment. A lost update succeeds because the final write is a valid value. Detecting these problems requires deliberate instrumentation — audit trails, reconciliation queries, or application-level checksums — rather than passive reliance on the database's error-handling machinery.

The following table summarizes the four concurrency problems, their distinguishing characteristics, and typical business impacts:

Understanding these four conflict types is not merely academic. It is a prerequisite for making informed decisions about transaction isolation levels, which are the database's primary mechanism for trading concurrency performance against correctness guarantees. Each isolation level — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — is defined precisely by which of these anomalies it permits and which it prevents. Selecting the right isolation level for each workload requires knowing which problems that workload is actually vulnerable to, how frequently those conflicts are likely to occur under realistic load, and what the business cost of each type of error would be if it occurred undetected. That analysis begins with a clear understanding of the problems themselves.