Database Locking Mechanisms and Concurrency Control

Description
Database locking mechanisms are core technologies for concurrency control, used to coordinate conflicts when multiple transactions access shared data simultaneously. When multiple transactions execute concurrently, issues such as dirty reads, non-repeatable reads, and phantom reads may arise. Locking mechanisms address these by locking data resources to restrict access methods for other transactions, thereby ensuring data consistency and isolation. Understanding lock types, compatibility, and deadlock is key to mastering this topic.

Problem-Solving Process
We will start with concurrency issues and gradually explain how locks resolve them.

Step 1: Understanding Problems Caused by Concurrent Operations
When multiple transactions execute concurrently without control, the following typical problems occur:

1. Dirty Read: Transaction A reads data that Transaction B has modified but not yet committed. If Transaction B later rolls back, Transaction A has read invalid "dirty" data.
2. Non-repeatable Read: Transaction A reads the same data multiple times within its scope. Between reads, Transaction B modifies and commits that data, causing Transaction A's two reads to return inconsistent results.
3. Phantom Read: Transaction A queries a set of data based on a condition. During the query process, Transaction B inserts or deletes records that match that condition and commits, causing Transaction A's subsequent query to return a different number of rows, as if "phantoms" appeared.

Step 2: Understanding Basic Lock Types — Shared Locks and Exclusive Locks
Locks are the fundamental tool for solving the above problems. The two core lock types are:

1. Shared Lock (S Lock, Read Lock):
   • Purpose: Used for read operations. After a transaction places an S lock on a data object, other transactions can also place S locks for reading, but cannot place X locks for modification.
   • Analogy: Like multiple people simultaneously opening the same document to read; everyone can view it, but no one can modify it.
2. Exclusive Lock (X Lock, Write Lock):
   • Purpose: Used for write operations (insert, delete, update). After a transaction places an X lock on a data object, other transactions cannot place either S locks (to read) or X locks (to modify) on it.
   • Analogy: Like a person locking the door and modifying the document alone in a room; during this time, others cannot enter to view or modify it.

The lock compatibility matrix is as follows:

Step 3: How Locks Solve Concurrency Problems
Now, let's see how locks are applied in transactions to solve the problems from Step 1.

• Solving Dirty Reads: Transaction B must obtain an X lock before modifying data. This X lock is held until Transaction B commits. When Transaction A attempts to read that data (requesting an S lock), the request is blocked because X and S locks are incompatible, until Transaction B commits or rolls back and releases the X lock. Thus, Transaction A cannot read uncommitted data.
• Solving Non-repeatable Reads: After Transaction A reads data the first time, it does not immediately release the S lock but holds it until the transaction ends. During Transaction A's execution, Transaction B's attempt to modify that data (requesting an X lock) is blocked because S and X locks are incompatible. This ensures that Transaction A's multiple reads of the data within its scope return consistent results.
• Solving Phantom Reads: Solving phantom reads requires a broader lock called a range lock. When Transaction A queries (e.g., WHERE age > 20), it not only locks existing records that satisfy age > 20 but also locks the entire age > 20 "range." This range lock prevents other transactions from inserting new records within this range (e.g., INSERT ... age=25), thereby preventing phantom reads.

Step 4: Understanding Lock Granularity
Locks can be applied to data units of different sizes, known as lock granularity.

• Row-Level Lock: Locks a single row. Finest granularity, highest concurrency, but highest management overhead.
• Page-Level Lock: Locks a page of data (a basic storage unit in databases, typically containing multiple rows). Granularity and overhead are between row and table locks.
• Table-Level Lock: Locks an entire table. Coarsest granularity, simple implementation, low overhead, but very low concurrency.

Database systems automatically select the most appropriate lock granularity based on operational needs.

Step 5: Understanding Deadlock and Resolution Strategies
Deadlock occurs when multiple transactions wait for each other to release locks.

• Example:

  • Transaction A locks row 1 and requests a lock on row 2.
  • Transaction B locks row 2 and requests a lock on row 1.
  • Transaction A waits for Transaction B to release row 2, and Transaction B waits for Transaction A to release row 1. Both wait indefinitely, causing deadlock.

• Resolution Strategies:

  1. Prevention: Mandate that all transactions request locks in a uniform order, breaking the "circular wait" condition.
  2. Detection and Recovery (Common in Databases): The system maintains a "wait-for graph" to detect circular waits (deadlock). Once deadlock is detected, a "victim" transaction (typically the one with the lowest rollback cost) is selected, rolled back, and all its held locks are released, allowing other transactions to proceed.

Summary
Database locking mechanisms are systems that control concurrent access via Shared Locks (S) and Exclusive Locks (X). Through lock compatibility rules and different locking strategies, they solve dirty reads, non-repeatable reads, and phantom reads at various isolation levels. Additionally, attention must be paid to the trade-offs of lock granularity and strategies for handling deadlock. Understanding this mechanism is fundamental to designing and optimizing high-concurrency database applications.