Concurrency Control: Short Notes (Database Transactions)

Concurrency control is the set of mechanisms a database uses to coordinate concurrent execution of transactions so that the final result is as if transactions ran in some safe order (typically ensuring serializability). It targets common anomalies such as dirty reads, non-repeatable reads, and phantom reads, which arise when isolation is too weak. In transaction systems, concurrency control is tightly connected to ACID and to the database’s chosen isolation level.2

A useful way to structure concurrency control is:

1. Define what correctness means (e.g., conflict serializability / serializability).
2. Pick a control method (locking, timestamps, optimistic/MVCC).
3. Handle failure modes like deadlock.
4. Provide/verify isolation guarantees at the system level.

Key terms (for your short notes)

• transaction
• schedule
• serializability
• isolation level
• deadlock

Footnotes

1. ANSI SQL isolation levels / anomalies overview - Explains dirty read, non-repeatable read, phantom read and isolation levels. ↩

2. Serializability (database theory) - Defines serializability and its significance in correctness of concurrent schedules. ↩

Correctness objective: serializability & anomalies

Concurrency control usually aims to prevent schedules that cannot be explained by a serial execution. The most common correctness notions are conflict serializability and (more generally) serializability. When violated, anomalies occur—commonly summarized through textbook read anomalies:

• Dirty read: Transaction reads data written by another transaction that later aborts.
• Non-repeatable read: Same row read twice yields different values because another committed transaction changed it between reads.
• Phantom read: Re-running a predicate query yields a new set of matching rows because another transaction inserted/removed qualifying rows.

[CalloutBlock]
type="warning"
title="Warning: ‘Locks’ ≠ ‘Isolation’ by default"
content="Using locks can enforce serializability, but the exact isolation guarantees depend on the locking rules (e.g., strictness), the isolation level semantics, and index/predicate locking for phantom protection."

Footnotes

1. Serializability (database theory) - Defines serializability and its significance in correctness of concurrent schedules. ↩

2. ANSI SQL isolation levels / anomalies overview - Explains dirty read, non-repeatable read, phantom read and isolation levels. ↩ ↩² ↩³

Lock-based concurrency control (2PL)

Locking primitives

Lock-based methods use locks with compatibility rules (e.g., shared vs exclusive). Transactions acquire locks before reads/writes; they may release locks later (or not, depending on strategy). The standard formal pattern is captured by Two-Phase Locking (2PL).

Two-Phase Locking (2PL)

2PL requires that a transaction first enters a growing phase (acquiring locks) and then a shrinking phase (releasing locks). No new locks may be acquired after it begins releasing.

A stronger variant, strict 2PL, holds write locks until commit/abort, which additionally prevents dirty reads (and is widely used in practice because it simplifies reasoning about recovery consistency).

[CalloutBlock]
type="tip"
title="Pro Tip (short-notes phrasing)"
content="Write: '2PL = growing then shrinking phase; ensures conflict-serializability; strict 2PL prevents dirty reads by retaining X-locks until commit/abort'."

Footnotes

1. Two-phase locking - Presents 2PL rule and its role in ensuring serializability. ↩ ↩²

Deadlock handling

Locking-based concurrency can lead to deadlock when multiple transactions wait on locks held by each other. A common formalization uses a wait-for graph: an edge $T_i \to T_j$ indicates $T_i$ is waiting for $T_j$.

Typical deadlock strategies:

1. Prevention: impose an order on resources so cycles cannot occur (reduces concurrency).
2. Detection & resolution: build/scan the wait-for graph periodically; when a cycle exists, abort a victim transaction.
3. Timeouts: abort a transaction if it waits too long (simpler, but may abort prematurely).
4. Wound-wait / wait-die style policies: use transaction timestamps to decide whether the younger waits or the older preempts (often described in timestamp-based schemes).

[CalloutBlock]
type="danger"
title="Deadlock note for exams"
content="You can mention: 'Deadlocks occur with lock waits; detect cycles in the wait-for graph and abort a victim (or prevent using resource ordering)'. Avoid claiming a specific algorithm unless your syllabus states it."

Footnotes

1. Deadlock - Covers deadlock concept; in DB locking, it motivates wait-for graph/cycle handling and resolution ideas. ↩ ↩²

Timestamp-based and optimistic approaches (high-level short notes)

Timestamp ordering

Timestamp-based concurrency control assigns each transaction a timestamp and orders conflicting operations by timestamp. If an operation would violate the timestamp order, it may be aborted or delayed depending on the specific algorithm.

Optimistic concurrency control & validation

Optimistic methods assume conflicts are rare: transactions run without heavy blocking, then validate before committing to ensure that the committed outcome is consistent with the correctness criteria.

MVCC (Multi-Version Concurrency Control)

Many modern databases use MVCC: readers use a snapshot, while writers create new versions. MVCC is designed to improve concurrency and reduce read-write blocking, often mapping naturally to isolation-level semantics like snapshot isolation.

Footnotes

1. Timestamp ordering (concurrency control) - Describes timestamp ordering approach for concurrency control. ↩

2. Multi-version concurrency control - Explains MVCC and its rationale in reducing blocking and enabling snapshot behavior. ↩ ↩²

Quick comparison chart (for “short notes”)

The following table compresses the major families of concurrency control into exam-friendly bullets.

(These are conceptual summaries consistent with standard descriptions of 2PL, timestamp ordering, optimistic/MVCC families.)3

Footnotes

1. Two-phase locking - Presents 2PL rule and its role in ensuring serializability. ↩

2. Timestamp ordering (concurrency control) - Describes timestamp ordering approach for concurrency control. ↩

3. Multi-version concurrency control - Explains MVCC and its rationale in reducing blocking and enabling snapshot behavior. ↩