Concurrency Control Problems in Database Systems

Concurrency control is the set of techniques a DBMS uses to coordinate simultaneous transactions while preserving correctness. Without control, interleaved reads and writes can produce anomalies even when every individual transaction is correct.

This section explains three fundamental problems:

1. Lost update — one transaction overwrites another transaction’s change.
2. Dirty read — a transaction reads data written by another transaction before it commits.
3. Unrepeatable read — a transaction reads the same row twice and obtains different committed values.

These problems are consequences of insufficient isolation between transactions. The SQL standard defines isolation levels that trade consistency for concurrency and performance.

A useful correctness goal is serializability: although transactions may execute concurrently, the final result should be equivalent to running them one at a time.

Footnotes

1. Transaction Isolation Levels (ODBC) - Microsoft Learn - Describes the four SQL-92 isolation levels and the read phenomena they may permit. ↩

Transactions and schedules

Suppose two transactions, $T_1$ and $T_2$, access the same data item $X$.

• $R_1(X)$ means that $T_1$ reads $X$.
• $W_1(X)$ means that $T_1$ writes $X$.
• $C_1$ means that $T_1$ commits.
• $A_1$ means that $T_1$ aborts or rolls back.

A schedule describes the actual execution order. A serial schedule completes one transaction before starting another:

$$R_1(X), W_1(X), C_1, R_2(X), W_2(X), C_2$$

A non-serial schedule interleaves operations:

$$R_1(X), R_2(X), W_1(X), W_2(X), C_1, C_2$$

Non-serial execution can improve throughput, but the DBMS must ensure that the interleaving does not violate application rules or database constraints.

Key terms

• Atomicity
• Consistency
• Commit
• Rollback

Footnotes

1. Chapter 18: Concurrency Control - Database System Concepts - Academic material covering schedules, serializability, locking, validation, and multiversion control. ↩

1. Lost Update

A lost update occurs when two transactions read the same original value, independently calculate new values, and then write those values back. The later write replaces the earlier write, so one logical update disappears.

Example

Assume an account balance is initially $100$.

The correct result, assuming both operations should be applied, is:

$$100 + 20 - 30 = 90$$

However, the final value is $70$. The deposit made by $T_1$ has been lost.

Schedule

$$R_1(B), R_2(B), W_1(B{=}120), W_2(B{=}70), C_1, C_2$$

The problem is not merely that the writes overlap. Each transaction computed its result from a stale copy of the balance.

Common causes

• Read-modify-write logic performed in separate application steps.
• Missing row-level locks.
• An isolation level or storage engine that permits conflicting writes.
• Updating an entire row from an old object representation.
• Omitting a version or timestamp condition in an UPDATE statement.

Prevention

A database can prevent lost updates by:

• Locking the row before reading it for modification.
• Performing the calculation inside an atomic SQL update.
• Using optimistic concurrency control with a version column.
• Using a sufficiently strong isolation level.
• Detecting a conflict and retrying the transaction.

An atomic update is often safer than reading a value into application code:

$$\texttt{UPDATE accounts SET balance = balance + 20 WHERE account\_id = 1}$$

Here, the database evaluates the expression as part of the update operation rather than allowing application code to overwrite a value read earlier.

2. Dirty Read

A dirty read occurs when $T_2$ reads a value changed by $T_1$, but $T_1$ later rolls back. Consequently, $T_2$ has used data that never became part of the committed database state.

Example

Assume an account balance is initially $500$.

Transaction $T_2$ read $0$, but the committed balance is still $500$. If $T_2$ uses the value to approve a purchase, generate a report, or make another update, its decision is based on data that never existed as committed state.

Schedule

$$W_1(B{=}0), R_2(B{=}0), A_1$$

The read is dirty because $R_2(B)$ occurs before $T_1$ commits.

Why dirty reads are dangerous

Dirty reads can cause:

• Incorrect financial or inventory decisions.
• Reports that include temporary values.
• Cascading errors in dependent transactions.
• Inconsistent application behavior when a rollback occurs.
• Violations of business rules based on uncommitted state.

Prevention

Dirty reads are prevented when a transaction reads only committed data. Typical mechanisms include:

• READ COMMITTED isolation or stronger.
• Shared locks that remain effective until the writer commits.
• MVCC snapshots that hide uncommitted versions.
• Avoiding database configurations equivalent to READ UNCOMMITTED.

Under PostgreSQL, even the named READ UNCOMMITTED level does not expose dirty data; PostgreSQL treats it as READ COMMITTED.

Footnotes

1. Transaction Isolation Levels (ODBC) - Microsoft Learn - Defines dirty reads as reads of uncommitted data that may later be rolled back. ↩

2. PostgreSQL 18 Documentation: Transaction Isolation - Documents PostgreSQL isolation behavior, snapshots, and its implementation-specific guarantees. ↩

3. Unrepeatable Read

An unrepeatable read occurs when a transaction reads a row, another transaction updates or deletes it and commits, and the first transaction reads the row again.

Example

Assume an employee’s salary is initially $60{,}000$.

Within one logical transaction, $T_1$ observed two different committed values for the same row.

Schedule

$$R_1(S{=}60000), W_2(S{=}65000), C_2, R_1(S{=}65000)$$

Unlike a dirty read, the second value is committed. The problem is that the database state changed between two reads performed by the same transaction.

Consequences

Unrepeatable reads can cause:

• A report whose calculations use two different versions of the same row.
• Incorrect comparisons between an initial and final value.
• Decisions based on a value that changes during validation.
• Confusing user experiences in multi-step workflows.

Prevention

Unrepeatable reads can be prevented by:

• REPEATABLE READ isolation or stronger.
• Holding read locks until transaction completion.
• Reading from a consistent MVCC snapshot.
• Explicitly locking rows with a database-specific FOR UPDATE or FOR SHARE clause when appropriate.

At READ COMMITTED, each statement may see a new committed snapshot, so repeated statements can observe different values. PostgreSQL documents this behavior explicitly.

Difference from a phantom read

An unrepeatable read concerns an existing row whose value changes or whose row disappears. A phantom read concerns the set of rows matching a predicate.

Example:

• Unrepeatable read: SELECT salary FROM employees WHERE employee_id = 7 returns $60,000 and then$65,000.
• Phantom read: SELECT * FROM employees WHERE department = 'Sales' returns 10 rows and then 11 because another transaction inserted a matching employee.

Footnotes

1. Transaction Isolation Levels (ODBC) - Microsoft Learn - Defines nonrepeatable reads as repeated reads of a row returning different data after another transaction commits an update or delete. ↩

2. PostgreSQL 18 Documentation: Transaction Isolation - Documents PostgreSQL isolation behavior, snapshots, and its implementation-specific guarantees. ↩

Isolation-level comparison

This table reflects the conventional SQL isolation model. Specific DBMS implementations can provide stronger behavior or use different mechanisms. For example, PostgreSQL reports that dirty reads are not possible, and its REPEATABLE READ implementation also prevents phantom reads even though the SQL standard permits them at that level.

Isolation levels do not automatically solve every business rule. A transaction can avoid dirty and unrepeatable reads while still experiencing a serialization anomaly, write skew, or a lost-update pattern caused by application logic. The required guarantee must therefore be matched to the operation.

Footnotes

1. PostgreSQL 18 Documentation: Transaction Isolation - Documents PostgreSQL isolation behavior, snapshots, and its implementation-specific guarantees. ↩

Concurrency-Control Techniques

Lock-based control

A lock controls who may read or write an item.

• A shared lock generally permits multiple readers.
• An exclusive lock generally permits one writer and prevents conflicting access.
• A transaction may wait when another transaction holds an incompatible lock.

Two-phase locking has a growing phase, during which locks are acquired, and a shrinking phase, during which locks are released. Strict variants retain write locks until commit or rollback, helping prevent cascading effects and dirty reads.

MVCC

MVCC allows readers to access an appropriate committed version while a writer creates a newer version. This can reduce reader-writer blocking, but the exact guarantees depend on the DBMS and isolation level.

Optimistic concurrency control

Optimistic concurrency control is useful when conflicts are relatively rare.

A version-column pattern is:

1SELECT balance, version
2FROM accounts
3WHERE account_id = 1;
4
5UPDATE accounts
6SET balance = 120,
7    version = version + 1
8WHERE account_id = 1
9  AND version = 4;

If the update affects zero rows, another transaction changed the record first. The application must reload the row, reconcile the change, or retry.

Atomic database operations

Whenever possible, express a modification as one database operation:

1UPDATE inventory
2SET quantity = quantity - 1
3WHERE product_id = 42
4  AND quantity > 0;

The application should verify the affected-row count. An affected-row count of zero may indicate insufficient inventory or a concurrent conflict.

Footnotes

1. CMU 15-445/645 Lecture Notes: Two-Phase Locking - Explains two-phase locking, lock compatibility, serializability, and deadlock detection. ↩

2. Chapter 18: Concurrency Control - Database System Concepts - Covers multiversion schemes and the use of old data versions to increase concurrency. ↩

Worked Comparison

Consider the following three schedules involving an item $X$.

Schedule A: Lost update

$$R_1(X{=}10), R_2(X{=}10), W_1(X{=}15), W_2(X{=}20), C_1, C_2$$

Both transactions read $10$. The final value is $20$, so the update producing $15$ is lost.

Schedule B: Dirty read

$$W_1(X{=}50), R_2(X{=}50), A_1$$

Transaction $T_2$ reads $50$ before $T_1$ commits. Since $T_1$ aborts, $50$ was never committed.

Schedule C: Unrepeatable read

$$R_1(X{=}10), W_2(X{=}20), C_2, R_1(X{=}20)$$

Transaction $T_1$ reads $X$ twice and obtains different values because $T_2$ committed an update between the reads.

Diagnostic questions

When examining a schedule, ask:

1. Did one transaction overwrite another transaction’s calculated result?
2. Did a read occur before the writer committed?
3. Did the same transaction read the same row twice after another transaction committed a modification?
4. Is the problem about a row’s value, or about the membership of a query result set?
5. Which isolation level or conflict-detection mechanism supplies the required guarantee?