Backend Performance Optimization: Database Locking Mechanisms and Concurrency Control

Knowledge Point Description
Database locking mechanisms are the core technology for ensuring data consistency. However, in high-concurrency scenarios, improper use of locks can lead to serious performance issues (such as deadlocks and blocking). This knowledge point will delve into the performance impacts of lock types, granularity, and isolation levels, as well as how to improve concurrency processing capabilities by optimizing lock strategies.

I. Basic Lock Types and Characteristics

1. Shared Lock (S Lock)

   • Description: Multiple transactions can acquire shared locks simultaneously, used for read operations (e.g., SELECT ... LOCK IN SHARE MODE).
   • Characteristic: Allows concurrent reads but blocks exclusive lock requests from other transactions.
   • Issue: A large number of shared locks can cause write operation starvation.

2. Exclusive Lock (X Lock)

   • Description: Only one transaction can hold it, used for write operations (e.g., UPDATE, DELETE).
   • Characteristic: Blocks any lock requests from other transactions (including both shared and exclusive locks).
   • Typical Scenario: An exclusive lock is automatically acquired when a transaction modifies data.

3. Intention Locks

   • Purpose: Quickly determine if row-level locks exist in a table, avoiding row-by-row lock status checks.
   • Types: Intention Shared Lock (IS), Intention Exclusive Lock (IX).
   • Example: Before a transaction applies a shared lock to a row, it first applies an intention shared lock to the table.

II. Lock Granularity and Performance Trade-offs

1. Table-Level Lock

   • Characteristics: Locks the entire table; simple to implement but offers low concurrency.
   • Applicable Scenarios: MyISAM engine, batch data modification.

2. Row-Level Lock

   • Characteristics: Locks only the affected rows; high concurrency but high lock management overhead (e.g., InnoDB lock structures consume memory).
   • Issue: A large number of row locks can lead to lock timeouts or deadlocks.

3. Gap Lock

   • Purpose: Locks a range between index records to prevent phantom reads.
   • Example: After locking the range (10, 20), other transactions cannot insert a record with id=15.
   • Performance Impact: May overly restrict concurrent inserts.

III. Impact of Transaction Isolation Levels on Locking

1. Read Uncommitted

   • Locking Strategy: No need for shared locks; may read dirty data.
   • Performance: Highest, but data consistency is not guaranteed.

2. Read Committed

   • Locking Strategy: Write operations acquire row-level exclusive locks; shared locks are released immediately after reading.
   • Issue: Non-repeatable reads (two reads within the same transaction may yield different results).

3. Repeatable Read

   • Locking Strategy: Uses gap locks to prevent phantom reads; holds shared and exclusive locks for the duration of the transaction.
   • Performance Risk: Gap locks may cause significant blocking.

4. Serializable

   • Locking Strategy: The strictest locking mechanism; all operations are locked, offering the lowest concurrency.

IV. Common Lock Issues and Optimization Solutions

1. Deadlock Detection and Handling

   • Cause: Circular wait for resources among transactions (e.g., A waits for B, B waits for A).
   • Solutions:
     • Timeout Mechanism: Set innodb_lock_wait_timeout for automatic rollback.
     • Deadlock Detection: Enable innodb_deadlock_detect to actively roll back the transaction with the minimum cost.
   • Optimization Suggestions:
     • Access resources in a fixed order within transactions (e.g., sort by primary key before operations).
     • Reduce transaction granularity, avoid long-running transactions.

2. Hotspot Row Update Optimization

   • Scenario: High concurrent updates to the same row, such as inventory during a flash sale.
   • Issue: Row-level exclusive locks become a bottleneck.
   • Optimization Solutions:
     • Application-Level Queueing: Serialize requests via middleware like Redis.
     • Database-Level Optimization:

       UPDATE inventory SET stock = stock - 1 WHERE id = 100 AND stock > 0;  
       

       • Use pessimistic locking (SELECT ... FOR UPDATE) or optimistic locking (version number verification).

3. Lock Monitoring and Diagnostic Tools

   • MySQL:
     • View Lock Status: SHOW ENGINE INNODB STATUS.
     • Information Schema Queries: SELECT * FROM information_schema.INNODB_LOCKS;
   • Key Metrics: Lock wait time, deadlock frequency, lock timeout ratio.

V. Practical Case: Optimizing Concurrent Account Transfers

1. Problematic Code (Prone to Deadlock)

   -- Transaction 1  
   BEGIN;  
   UPDATE accounts SET balance = balance - 100 WHERE id = 1;  
   UPDATE accounts SET balance = balance + 100 WHERE id = 2;  
   COMMIT;  
   
   -- Transaction 2 (executed concurrently, with opposite operation order)  
   BEGIN;  
   UPDATE accounts SET balance = balance - 50 WHERE id = 2;  
   UPDATE accounts SET balance = balance + 50 WHERE id = 1;  
   

   • Deadlock Cause: Transaction 1 locks id=1 then requests id=2, while Transaction 2 locks id=2 then requests id=1, creating a circular wait.

2. Optimization Solutions

   • Fixed Operation Order: All transfers first operate on the account with the smaller id:

     -- Uniformly update id=1 first, then id=2  
     UPDATE accounts SET balance = balance - 100 WHERE id = 1;  
     UPDATE accounts SET balance = balance + 100 WHERE id = 2;  
     

   • Use Optimistic Locking:

     UPDATE accounts   
     SET balance = balance - 100, version = version + 1  
     WHERE id = 1 AND version = old_version;  
     

Summary
Database lock optimization requires balancing consistency and performance based on business scenarios:

• Read-heavy, write-light scenarios: Prioritize read-write separation and optimistic locking.
• Write-intensive scenarios: Reduce lock contention through request queuing (peak shaving) and reducing transaction granularity.
• Monitor lock wait metrics to avoid hidden bottlenecks.