Detailed Explanation of Database Locking Mechanisms and Concurrency Control

Detailed Explanation of Database Locking Mechanisms and Concurrency Control

Problem Description
Database locking mechanisms are the core technology for ensuring data consistency and isolation during concurrent access. When multiple transactions operate on the same data simultaneously, locks can effectively coordinate access order, preventing issues like dirty reads and lost updates. This topic covers lock classification, locking protocols, deadlock handling, and the principles of Multi-Version Concurrency Control (MVCC).

1. Why Are Locks Needed?
Assume transactions T1 and T2 simultaneously modify an account balance (initial value 100):

  • T1: Deposit 50 → balance=150
  • T2: Withdraw 30 → balance=70
    Without locks, the following conflicts may occur:
  1. Dirty Read: T2 reads 150 before T1 commits, but T1 rolls back, causing T2 to calculate based on incorrect data.
  2. Lost Update: T1 and T2 both read 100, calculate 150 and 70 respectively, and the later commit overwrites the earlier one.

2. Basic Lock Types

  1. Shared Lock (S Lock)

    • Used for read operations; allows multiple transactions to acquire S locks on the same data concurrently.
    • Syntax example (SQL Server): SELECT * FROM table WITH (SHAREDLOCK)

  2. Exclusive Lock (X Lock)

    • Used for write operations; prohibits other transactions from acquiring any lock on the data.
    • Characteristic: X locks are incompatible with any other locks (including other X locks and S locks).

  3. Compatibility Matrix

      | S Lock | X Lock
-------------------------
 S Lock| Compatible | Incompatible
 X Lock| Incompatible| Incompatible

3. Locking Protocols and Granularity

  1. Two-Phase Locking Protocol (2PL)

    • Growing Phase: A transaction can only acquire new locks, not release any.
    • Shrinking Phase: A transaction can only release held locks, not acquire new ones.
      Example:
    T1: S-lock(A) → Read(A) → X-lock(B) → Write(B) → Unlock(A) → Unlock(B)  // Violates 2PL
T2: S-lock(A) → Read(A) → X-lock(B) → Write(B) → Unlock(B) → Unlock(A)  // Complies with 2PL

  2. Lock Granularity Escalation

    • Row-Level Lock: High precision but high overhead (e.g., MySQL InnoDB).
    • Table-Level Lock: Efficient but poor concurrency (e.g., MyISAM).
    • Intention Lock: Quickly determines if a table is locked.
      • IS Lock: Intention Shared Lock (indicates intent to place an S lock on rows).
      • IX Lock: Intention Exclusive Lock (indicates intent to place an X lock on rows).

4. Deadlock Handling Mechanisms

  1. Conditions for Deadlock

    • Mutual Exclusion: A resource can only be held by one transaction at a time.
    • Hold and Wait: A transaction holds resources while requesting new ones.
    • No Preemption: Resources can only be released by the holder.
    • Circular Wait: T1 waits for a resource held by T2, while T2 waits for a resource held by T1.

  2. Solutions

    • Prevention Strategies: Request all resources at once (breaking the hold-and-wait condition).
    • Detection and Recovery:
      1. Detect cycles via a Wait-for Graph.
      2. Choose the transaction with the minimum cost to rollback (e.g., MySQL judges based on undo log volume).
    • Timeout Mechanism: Set a lock wait timeout (e.g., innodb_lock_wait_timeout).

5. Multi-Version Concurrency Control (MVCC)
Lock-free read implementation in InnoDB as an example:

  1. Core Mechanism:

    • Each record contains hidden fields: DB_TRX_ID (last modifying transaction ID), DB_ROLL_PTR (rollback pointer).
    • Determines data version visibility to the current transaction via ReadView.

  2. Snapshot Read Process:

    • During a query, first check DB_TRX_ID:
      • If the ID is less than the minimum ID in the current active transaction list, it was committed before the transaction started → visible.
      • If the ID is greater than the current transaction ID → not visible (find the old version via the rollback log).
    • Example: When transaction T3 (ID=300) reads data, if the record's TRX_ID=200 (committed), it returns directly; if TRX_ID=400 (uncommitted), it finds the version with TRX_ID=150 via the rollback pointer.

6. Practical Application Scenarios

  1. Pessimistic Locking (Lock before access)

    BEGIN;
SELECT * FROM account WHERE id=1 FOR UPDATE;  -- Acquire X lock
UPDATE account SET balance=balance-100 WHERE id=1;
COMMIT;

  2. Optimistic Locking (Handle conflicts upon detection)

    UPDATE account
SET balance=200, version=version+1
WHERE id=1 AND version=5; -- Avoid lost updates using version number

Key Summary

  • The essence of locking mechanisms is to balance concurrency efficiency and data consistency.
  • MVCC reduces lock contention and improves read concurrency through version control.
  • Deadlocks require a combined approach of timeout mechanisms and rollback strategies.