Transactions and Concurrency Control

Learning Objectives

By the end of this page, you should be able to:

• Explain the four ACID properties with a concrete bank-transfer example and describe the transaction state diagram.
• Identify dirty reads, non-repeatable reads, phantom reads, and lost updates from an interleaved-transaction schedule.
• Distinguish serial, concurrent, and conflict-serializable schedules.
• Compare lock-based (2PL), timestamp-ordering, and optimistic concurrency control protocols.
• Map the four SQL isolation levels to the anomalies each one prevents and write the corresponding SET TRANSACTION ISOLATION LEVEL statements.
• Explain how deadlocks arise and describe detection, prevention, and avoidance strategies.

Quick Answer

A transaction is a group of database operations that must succeed or fail as a single unit, guaranteed by the ACID properties (Atomicity, Consistency, Isolation, Durability). Concurrency control is the set of techniques — locking, timestamp ordering, and optimistic validation — that let multiple transactions run at the same time without producing dirty reads, non-repeatable reads, phantom reads, or lost updates. These matter because databases are almost always accessed by many users simultaneously (a bank has thousands of concurrent transfers), and without concurrency control, interleaved operations can silently corrupt data even though each transaction is individually correct. SQL exposes this trade-off directly through isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE), letting you choose how much correctness to trade for performance.

Transaction States

Every transaction moves through a well-defined lifecycle as the DBMS tracks its progress:

• Active: the transaction is executing statements (reads/writes). This is the normal, ongoing state.
• Partially committed: the last statement has run, but changes may still be sitting in a memory buffer, not yet guaranteed durable on disk.
• Committed: the transaction has completed successfully and its effects are permanently recorded — this is the point where durability kicks in.
• Failed: something went wrong (a constraint violation, a deadlock the DBMS killed it to resolve, a crash) and the transaction cannot proceed to commit.
• Aborted: the DBMS has rolled back all of the failed transaction's partial changes, restoring the database to the state before the transaction began. From here, the transaction can be restarted or discarded entirely.

The reason this matters beyond terminology: a transaction that never reaches "committed" must leave zero trace in the database. If a crash happens between "partially committed" and "committed," the recovery manager uses the transaction log to either finish applying the changes (if they were durably logged) or roll them back — never leave the database half-updated.

ACID Properties, With a Concrete Example

Consider transferring ₹100 from Account A to Account B:

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE Account_ID = 'A';
UPDATE Accounts SET Balance = Balance + 100 WHERE Account_ID = 'B';
COMMIT;

• Atomicity: both UPDATE statements happen, or neither does. If the second update fails (say, Account B doesn't exist), the first update is rolled back too — you never end up with money vanishing from A without appearing in B.
• Consistency: before and after the transaction, the sum of all account balances is unchanged, and no constraint (e.g., Balance >= 0 if enforced by a CHECK) is violated. The transaction moves the database from one valid state to another valid state.
• Isolation: if another transaction is simultaneously reading Account A's balance, it should see either the pre-transfer or post-transfer value — never a state where A has been debited but B hasn't yet been credited.
• Durability: once COMMIT returns successfully, even if the server loses power one millisecond later, the transfer survives — because the DBMS wrote it to a persistent transaction log before acknowledging the commit.

Without all four properties simultaneously, a "transaction" is just a sequence of statements with no safety guarantee — you'd be back to manually coordinating file writes.

Concurrency Problems

When two transactions interleave without proper control, several distinct anomalies can occur. Each is best understood through a concrete timeline of two transactions, T1 and T2, both touching the same row.

Dirty Read

T2 reads a value that T1 has written but not yet committed. If T1 then rolls back, T2 has read data that never officially existed.

Time	T1	T2
t1	UPDATE Accounts SET Balance = 500 WHERE Account_ID = 'A'; (uncommitted)
t2		SELECT Balance FROM Accounts WHERE Account_ID = 'A'; → reads 500
t3	ROLLBACK; (balance reverts to original, e.g. 1000)
t4		T2 has already used the phantom value 500 in a decision

Non-Repeatable Read

T1 reads the same row twice within one transaction, but T2 commits an update in between, so T1 gets two different values for the "same" read.

Time	T1	T2
t1	SELECT Balance FROM Accounts WHERE Account_ID = 'A'; → 1000
t2		UPDATE Accounts SET Balance = 800 WHERE Account_ID = 'A'; COMMIT;
t3	SELECT Balance FROM Accounts WHERE Account_ID = 'A'; → 800 (different!)

Phantom Read

T1 runs a query with a filter twice, but T2 inserts a new row matching that filter in between, so the set of rows changes even though no individual row T1 already saw was modified.

Time	T1	T2
t1	SELECT * FROM Orders WHERE Amount > 1000; → 5 rows
t2		INSERT INTO Orders VALUES (..., 1500); COMMIT;
t3	SELECT * FROM Orders WHERE Amount > 1000; → 6 rows (a "phantom" appeared)

Lost Update

Two transactions both read the same value, then both write back a value computed from that stale read — one update silently overwrites the other.

Time	T1	T2
t1	SELECT Balance FROM Accounts WHERE Account_ID = 'A'; → 1000
t2		SELECT Balance FROM Accounts WHERE Account_ID = 'A'; → 1000
t3	UPDATE Accounts SET Balance = 1000 - 200 WHERE Account_ID = 'A'; COMMIT; → 800
t4		UPDATE Accounts SET Balance = 1000 - 300 WHERE Account_ID = 'A'; COMMIT; → 700

T1's ₹200 withdrawal is completely lost — the final balance (700) reflects only T2's update, as if T1 never ran.

Schedules and Serializability

A schedule is the order in which operations from multiple transactions are actually executed.

• A serial schedule runs transactions one completely after another with no interleaving (T1 fully finishes, then T2 starts). Serial schedules are always safe but sacrifice concurrency.
• A concurrent schedule interleaves operations from multiple transactions to improve throughput and resource utilization, but risks the anomalies above.
• A schedule is serializable if its outcome is equivalent to some serial execution of the same transactions — the interleaving is safe even though it wasn't literally sequential.
• Conflict serializability is the practical test used to check this: two operations conflict if they belong to different transactions, access the same data item, and at least one is a write. A schedule is conflict-serializable if you can reorder non-conflicting operations to transform it into a serial schedule. DBMSs use a precedence graph (a node per transaction, an edge for each conflict where one transaction's operation must precede another's) — if this graph has no cycle, the schedule is conflict-serializable.

The goal of every concurrency control protocol below is to guarantee (or approximate) serializability while allowing as much real interleaving as possible for performance.

Lock-Based Protocols

Shared and Exclusive Locks

• Shared lock (S): a transaction wanting to read a data item acquires this. Multiple transactions can hold shared locks on the same item simultaneously.
• Exclusive lock (X): a transaction wanting to write a data item acquires this. Only one transaction can hold an exclusive lock, and no shared locks can coexist with it.

BEGIN TRANSACTION;
SELECT * FROM Accounts WHERE Account_ID = 'A' FOR UPDATE; -- acquires exclusive lock
UPDATE Accounts SET Balance = Balance - 100 WHERE Account_ID = 'A';
COMMIT; -- lock released

Two-Phase Locking (2PL)

2PL guarantees conflict serializability by splitting every transaction's lifetime into two phases:

1. Growing phase: the transaction can acquire locks but cannot release any.
2. Shrinking phase: the transaction can release locks but cannot acquire any new ones.

Once a transaction releases its first lock, it can never acquire another — this single rule is what mathematically guarantees the resulting schedule is conflict-serializable. A common variant, Strict 2PL, holds all exclusive locks until the transaction commits or aborts (rather than releasing early in the shrinking phase), which additionally avoids cascading rollbacks and is what most production databases actually implement.

Deadlock

Locking introduces a new risk: deadlock, where two or more transactions wait on each other forever.

T1 holds a lock on row A and wants row B; T2 holds a lock on row B and wants row A. Neither can proceed. DBMSs handle this in three general ways:

• Detection: periodically build a wait-for graph (an edge from Ti to Tj if Ti is waiting on a lock held by Tj); a cycle means deadlock. The DBMS picks a "victim" transaction (often the one that's done the least work) and aborts it, releasing its locks so others can proceed.
• Prevention: disallow the conditions that lead to deadlock in the first place, typically using timestamp ordering — e.g., the wait-die scheme (an older transaction may wait for a younger one, but a younger one requesting a lock held by an older one is aborted) or wound-wait (an older transaction "wounds" — forcibly aborts — a younger one holding a needed lock).
• Avoidance: allocate resources only if doing so cannot lead to an unsafe state, checking ahead of time before granting a lock (conceptually similar to the Banker's Algorithm from operating systems, though less commonly implemented in practice for databases due to overhead).

Most production relational databases rely on detection, since it doesn't restrict normal-case concurrency and deadlocks are usually rare enough that occasional victim-aborts are cheaper than constant prevention checks.

Timestamp-Based Ordering

Instead of locks, each transaction is assigned a unique timestamp when it starts, and the DBMS uses these timestamps to decide execution order — as if the schedule were serialized in timestamp order. Each data item tracks the timestamp of the last transaction that read it and the last one that wrote it. If a transaction tries to read or write a data item "out of timestamp order" (e.g., an older transaction trying to write a value already read by a younger one), it's rolled back and restarted with a new timestamp. This avoids locking overhead and deadlocks entirely, at the cost of potentially more rollbacks under high contention, since conflicts are resolved by aborting rather than waiting.

Optimistic Concurrency Control (Validation-Based)

Optimistic concurrency control (OCC) bets that conflicts are rare, so it skips locking during execution entirely and checks for conflicts only at commit time, through three phases:

1. Read phase: the transaction reads data and computes its updates in a private, local workspace — nothing is written to the actual database yet.
2. Validation phase: just before committing, the DBMS checks whether any other transaction modified the data this transaction read, by comparing timestamps or version numbers.
3. Write phase: if validation passes, the changes are applied to the database. If it fails, the transaction is aborted and typically retried.

-- Conceptually, using a version column:
SELECT Balance, version FROM Accounts WHERE Account_ID = 'A'; -- version = 5

-- ... application computes new balance locally ...

UPDATE Accounts
SET Balance = 900, version = 6
WHERE Account_ID = 'A' AND version = 5; -- fails (0 rows affected) if version changed

OCC works well when conflicts are genuinely infrequent (e.g., mostly-read workloads with occasional writes); under heavy write contention, the constant validation failures and retries can make it perform worse than simple locking.

Isolation Levels

SQL standardizes four isolation levels, each permitting a different subset of the anomalies above in exchange for better performance:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible
Serializable	Prevented	Prevented	Prevented

• Read Uncommitted offers essentially no isolation — transactions can see each other's uncommitted (potentially soon-to-be-rolled-back) changes. Rarely used except for approximate analytics where speed matters more than exactness.
• Read Committed (the default in PostgreSQL and Oracle) ensures you never see uncommitted data, but a value you read can still change if you read it again later in the same transaction.
• Repeatable Read (the default in MySQL/InnoDB) locks in the values you've already read for the duration of the transaction, but new rows matching your filter can still appear (phantoms).
• Serializable gives full isolation — the strongest guarantee — by effectively making concurrent transactions behave as if they ran one at a time, at the highest cost to concurrency and throughput.

Higher isolation always trades performance for correctness — this is a deliberate, per-application choice, not a "more is always better" setting. A reporting query might happily use READ COMMITTED; a payment ledger update should use SERIALIZABLE or rely on strict locking.

Key Terms

Term	Definition
Transaction	A sequence of operations executed as a single, all-or-nothing unit of work.
ACID	Atomicity, Consistency, Isolation, Durability — the four guarantees of reliable transaction processing.
Dirty Read	Reading a value written by an uncommitted transaction that may later be rolled back.
Non-Repeatable Read	Reading the same row twice in one transaction and getting different values because another transaction committed a change in between.
Phantom Read	Re-running a filtered query and seeing a different set of rows because another transaction inserted/deleted matching rows.
Lost Update	Two transactions read the same value and each write back an update based on it, causing one update to silently overwrite the other.
Serializable Schedule	A concurrent schedule whose outcome is equivalent to some serial execution of the same transactions.
Two-Phase Locking (2PL)	A locking protocol with a growing phase (acquire only) and a shrinking phase (release only) that guarantees conflict serializability.
Deadlock	A cycle of transactions each waiting on a lock held by another, so none can proceed.
Optimistic Concurrency Control	A protocol that allows transactions to execute without locks and validates for conflicts only at commit time.
Isolation Level	A SQL setting (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) that determines which concurrency anomalies are permitted.

Common Mistakes

Misconception 1: "Serializable isolation means transactions literally run one at a time." Why it's wrong: Serializable isolation guarantees the outcome is equivalent to some serial order — the DBMS can still interleave operations internally (via locking or multiversion techniques) for performance, as long as the final result is indistinguishable from a serial execution. Correct understanding: Serializability is about equivalence of outcome, not literal sequential execution. The DBMS is free to use any concurrency mechanism internally as long as it can guarantee that equivalence.

Misconception 2: "Repeatable Read prevents all the anomalies, since it sounds like the strongest level." Why it's wrong: Repeatable Read only guarantees that rows you've already read won't change value if you read them again — it does not stop new rows from appearing in a range query, which is exactly the phantom read anomaly. Correct understanding: Only SERIALIZABLE prevents all three read anomalies (dirty, non-repeatable, phantom). Repeatable Read is one level below it and still permits phantom reads.

Misconception 3: "Locking is always slower than optimistic concurrency control, so OCC is the better default choice." Why it's wrong: OCC avoids lock overhead only when conflicts are actually rare; under high write contention, transactions repeatedly fail validation and must retry, which wastes far more work than simply waiting for a lock would have. Correct understanding: The right protocol depends on the workload — locking suits high-contention, write-heavy workloads; optimistic concurrency control suits low-contention, mostly-read workloads where conflicts are genuinely uncommon.

Comparison and Connections

Concurrency Control Protocols

Protocol	Mechanism	Best suited for	Key weakness
Lock-based (2PL)	Acquire/release shared and exclusive locks in two phases	High-contention, write-heavy workloads	Risk of deadlock; locks reduce concurrency while held
Timestamp ordering	Order operations by transaction timestamp, abort out-of-order ones	Avoiding deadlocks entirely	More rollbacks/restarts under contention
Optimistic (validation-based)	Execute freely, validate for conflicts only at commit	Low-contention, mostly-read workloads	Expensive retries if conflicts turn out to be frequent

Isolation Levels vs. Anomalies Prevented

See the isolation-level table above — the short version: each step up (Read Uncommitted → Read Committed → Repeatable Read → Serializable) closes off exactly one more anomaly, at a progressively higher cost to concurrency.

Practice Questions

Recall

1. List the five transaction states and identify which two states can lead to "Aborted." Answer guidance: Active, Partially Committed, Committed, Failed, Aborted. Both Active (via an error) and Partially Committed (via a system failure) can transition to Failed, which then leads to Aborted after rollback.
2. Name the four concurrency anomalies discussed on this page. Answer guidance: Dirty read, non-repeatable read, phantom read, lost update.

Understanding

3. Explain why Strict Two-Phase Locking is preferred over basic 2PL in most production databases. Answer guidance: Basic 2PL guarantees conflict serializability but can still allow cascading rollbacks if a transaction releases a lock early and another transaction reads that (uncommitted) data before the first one aborts. Strict 2PL holds all exclusive locks until commit/abort, preventing other transactions from ever reading uncommitted data.
4. Why does Repeatable Read isolation still permit phantom reads even though it locks the rows a transaction has already read? Answer guidance: Repeatable Read locks specific rows already fetched, but it does not lock the "range" or "gap" that a new INSERT could fall into, so a second identical query can still return additional rows that weren't there before.

Application

5. A ticket-booking system must ensure that two customers can never both purchase the last available seat. Which isolation level or protocol would you use, and why? Answer guidance: Serializable isolation (or strict locking with SELECT ... FOR UPDATE) is needed, since even Repeatable Read could allow a phantom insert/race in checking seat availability; the seat-count check and the booking must be treated as one atomic, fully isolated unit.
6. Write a SQL transaction that transfers ₹500 from account 'X' to account 'Y', ensuring atomicity. Answer guidance:

   BEGIN TRANSACTION;
   UPDATE Accounts SET Balance = Balance - 500 WHERE Account_ID = 'X';
   UPDATE Accounts SET Balance = Balance + 500 WHERE Account_ID = 'Y';
   COMMIT;

Analysis

7. Two transactions T1 and T2 both read Account A's balance (1000), then T1 subtracts 100 and commits, and T2 subtracts 50 and commits afterward, overwriting T1's result. Identify the anomaly and explain how 2PL would have prevented it. Answer guidance: This is a lost update. Under 2PL, T1 would acquire an exclusive lock on Account A before its update and hold it until commit; T2 would be forced to wait for that lock, so it would read the already-updated balance (900) rather than the stale value (1000), preventing the overwrite.
8. Compare timestamp ordering and optimistic concurrency control in terms of when conflicts are detected and resolved. Answer guidance: Timestamp ordering checks and enforces ordering continuously during execution — every read/write is validated against timestamps immediately, aborting violators right away. OCC defers all conflict checking to a single validation phase at commit time, allowing free execution in between; both avoid locks, but timestamp ordering can abort mid-transaction while OCC only aborts at the end.

FAQ

Q: What's the difference between Consistency in ACID and consistency in the CAP theorem? A: They're related but not identical. ACID consistency means a transaction only moves the database between states that satisfy its defined constraints (keys, checks, triggers). CAP consistency means all nodes in a distributed system see the same data at the same time. A system can satisfy one without automatically satisfying the other.

Q: Can a transaction ever skip the "Failed" state and go straight from Active to Aborted? A: Conceptually, "Failed" and "Aborted" are often collapsed into one step by many textbooks and real systems — the DBMS detects the failure and immediately begins rollback. The distinction matters mainly for understanding why a rollback started (an error, versus a chosen abort) rather than as two separately observable, long-lived states.

Q: Why doesn't every database just always use Serializable isolation to be safe? A: Because Serializable isolation, whether implemented via strict locking or serialization checks, drastically reduces how many transactions can run concurrently, hurting throughput. Most applications don't need it for most operations — the sensible approach is to reserve it for the specific transactions where correctness genuinely can't tolerate any anomaly.

Q: Is deadlock the same as starvation? A: No. Deadlock is a cycle of transactions each permanently blocked waiting on each other. Starvation is when a transaction is repeatedly delayed or aborted (e.g., always chosen as the "victim" in deadlock resolution) even though no cycle exists — it just never gets its turn. DBMSs typically use aging (prioritizing older transactions) to prevent starvation.

Q: Do NoSQL databases have transactions and isolation levels too? A: Increasingly, yes — MongoDB added multi-document ACID transactions, and many distributed databases offer tunable consistency. Historically, though, many NoSQL systems traded strict isolation/serializability for horizontal scalability, which is one of the core trade-offs discussed when comparing relational and NoSQL models.

Q: What actually enforces durability once a transaction commits? A: A write-ahead log (WAL). Before a transaction is marked committed, its changes are flushed to a persistent log file. If the system crashes right after, recovery replays the log to reconstruct any committed-but-not-yet-applied changes, guaranteeing nothing committed is ever lost.

Quick Revision

• Transaction states: Active → Partially Committed → Committed, with Failed → Aborted as the error path.
• ACID = Atomicity, Consistency, Isolation, Durability; use the bank-transfer example to recall each one.
• Dirty read = reading uncommitted data; non-repeatable read = same row, different value on re-read; phantom read = same query, different row set; lost update = one transaction's write silently overwritten by another's.
• Serializable schedule = equivalent in outcome to some serial order; conflict serializability is checked via a precedence graph with no cycles.
• 2PL: growing phase (acquire only) then shrinking phase (release only) — guarantees conflict serializability. Strict 2PL holds locks until commit/abort to avoid cascading rollbacks.
• Deadlock = a cycle in the wait-for graph; handled via detection (abort a victim), prevention (wait-die/wound-wait), or avoidance.
• Timestamp ordering avoids locks/deadlocks but causes more rollbacks under contention.
• Optimistic concurrency control (read → validate → write) suits low-conflict workloads; expensive when conflicts are frequent.
• Isolation levels, weakest to strongest: Read Uncommitted, Read Committed, Repeatable Read, Serializable — each closes off one more anomaly.
• Higher isolation = stronger correctness but lower concurrency/throughput; pick per-transaction based on actual risk.

Related Topics

Prerequisites

• Introduction to DBMS and basic SQL (DDL/DML)
• Relational Database Model (keys, constraints)

Related Topics

• Database Recovery and the Write-Ahead Log
• Indexing and Query Optimization
• Distributed Databases and the CAP Theorem

Next Topics

• Database Security and Access Control
• NoSQL Databases and Eventual Consistency
• Query Processing and Optimization