Database Locking Mechanisms and Concurrency Control

Database Locking Mechanisms and Concurrency Control

1. Problem Description

In high-concurrency scenarios, how does a database ensure data consistency? For example, when multiple users modify the same row of data simultaneously, issues such as lost updates and dirty reads may occur. These problems need to be addressed through locking mechanisms and transaction isolation levels. Interviews often examine lock types, deadlock causes and avoidance, and how to improve concurrency performance by optimizing locking strategies.

2. Basic Lock Types and Their Functions

(1) Shared Lock (S Lock)

  • Function: Allows multiple transactions to read the same data simultaneously but prevents other transactions from acquiring exclusive locks (i.e., prohibits modifications).
  • Scenario: SELECT ... LOCK IN SHARE MODE (MySQL) or WITH (HOLDLOCK) (SQL Server).
  • Characteristic: Read-read does not block; read-write blocks.

(2) Exclusive Lock (X Lock)

  • Function: Allows only one transaction to modify data; other transactions cannot read or modify it.
  • Scenario: UPDATE, DELETE, INSERT, or SELECT ... FOR UPDATE.
  • Characteristic: Write-write mutual exclusion; read-write mutual exclusion.

Example:

-- Transaction A  
BEGIN;  
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- Acquire exclusive lock for id=1  
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  
COMMIT;  

-- Transaction B will be blocked if it concurrently executes the following operation:  
UPDATE accounts SET balance = balance + 200 WHERE id = 1;

3. Lock Granularity: Table Lock vs. Row Lock

(1) Table-Level Lock

  • Characteristic: Locks the entire table, simple to implement but offers low concurrency.
  • Applicable Scenario: MyISAM engine, batch operations.

(2) Row-Level Lock

  • Characteristic: Locks only the rows that need to be operated on, offers high concurrency but has significant overhead.
  • Applicable Scenario: InnoDB engine, high-concurrency transactions.
  • Implementation Dependency: Requires index support. If the WHERE condition lacks an index, it may degrade to a table lock.

Example:

-- Updating a non-indexed field may cause row locks to escalate to table locks  
UPDATE users SET status = 1 WHERE name = 'Alice'; -- If name has no index, locks the entire table

4. Deadlock Causes and Solutions

(1) Deadlock Conditions

  • Mutual exclusion, hold and wait, no preemption, circular wait.

Typical Scenario:

-- Transaction A  
BEGIN;  
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Locks id=1  
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Waits for Transaction B to release the lock on id=2  

-- Transaction B  
BEGIN;  
UPDATE accounts SET balance = balance - 200 WHERE id = 2; -- Locks id=2  
UPDATE accounts SET balance = balance + 200 WHERE id = 1; -- Waits for Transaction A to release the lock on id=1

At this point, Transaction A and B wait for each other, forming a deadlock.

(2) Solutions

  • Automatic Database Detection: InnoDB actively rolls back one of the transactions (e.g., the transaction with the lowest rollback cost).
  • Optimize Application Logic: Access resources in a fixed order (e.g., always operate on the record with the smaller id first).
  • Set Lock Timeout: SET innodb_lock_wait_timeout = 5; (MySQL).

5. Impact of Transaction Isolation Levels on Locking

Different isolation levels use locking mechanisms to ensure data consistency, but their concurrency performance varies:

  1. Read Uncommitted: No read locks; dirty reads possible.
  2. Read Committed: Only write locks are added; avoids dirty reads but allows non-repeatable reads.
  3. Repeatable Read: Both read and write locks are added; avoids non-repeatable reads (MySQL optimizes this with MVCC).
  4. Serializable: All operations are locked; lowest concurrency.

Example:

-- Set isolation level  
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
BEGIN;  
SELECT * FROM accounts WHERE id = 1; -- May add a shared lock (depends on database implementation)

6. Performance Optimization Practices

(1) Reducing Lock Contention

  • Use Indexes: Prevent row locks from escalating to table locks.
  • Shorten Transaction Duration: Keep only necessary operations within a transaction and commit as soon as possible.
  • Break Down Large Transactions: Split batch updates into smaller batches.

(2) Choosing Between Optimistic and Pessimistic Locking

  • Pessimistic Locking: Assumes frequent concurrency conflicts and directly applies locks (e.g., SELECT ... FOR UPDATE).
  • Optimistic Locking: Detects conflicts using version numbers or timestamps; suitable for read-heavy, write-light scenarios.
-- Optimistic locking example  
UPDATE products SET stock = stock - 1, version = version + 1  
WHERE id = 100 AND version = 5; -- Update fails if the version number has been modified

7. Summary

  • Locking is the core mechanism for ensuring data consistency in concurrency, but a balance between performance and safety must be struck.
  • By reasonably selecting lock granularity, isolation levels, and avoiding deadlocks, system concurrency can be significantly improved.
  • In actual development, monitoring tools (e.g., SHOW ENGINE INNODB STATUS) should be combined to analyze lock contention.