Lock Mechanism

Pessimistic Locking

Pessimistic locking is a concurrency control mechanism based on a conservative “lock first, then modify” strategy. During data processing, pessimistic locking assumes that concurrent operations are likely to cause conflicts, so it first places data in a locked state, ensuring that other transactions cannot modify the data until the current transaction completes.

From an implementation perspective, pessimistic locking is mainly divided into the following types:

Row-level Lock (Row Lock): Lock a single row of data, such as MySQL’s SELECT…FOR UPDATE statement
Table-level Lock (Table Lock): Lock the entire data table
Read Lock (Shared Lock): Allows multiple transactions to simultaneously read data but prohibits modification
Write Lock (Exclusive Lock): Allows only one transaction to read and write data, other transactions are completely prohibited from access

Typical application scenarios include:

Bank account transfer operations
Inventory deduction in inventory management systems
Seat locking in airline reservation systems

Taking MySQL as an example, the implementation of pessimistic locking is as follows:

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

Advantages and Disadvantages of Pessimistic Locking:

Advantages: Can effectively avoid data conflicts and ensure data consistency
Disadvantages: Reduces system concurrent performance and may cause deadlocks

In distributed systems, the implementation of pessimistic locking becomes more complex, usually requiring intermediate components like Redis or Zookeeper to achieve cross-service lock management.

Table-Level Locking

Table-level locking locks the entire table for each operation, with the lowest degree of concurrency. Manually add table locks:

lock table table_name read|write, table2 read|write;

View locks on tables:

show open tables;

Remove table locks:

unlock tables;

MySQL Table-Level Locking Explained

Table Read Lock

When adding a read lock to a table:

Current connection:
- Can execute SELECT query operations
- Cannot execute INSERT, UPDATE, DELETE and other write operations, otherwise an immediate error is reported
Other connections:
- Can concurrently execute SELECT query operations
- Attempts to execute write operations (INSERT/UPDATE/DELETE) will be blocked until the lock is released

Table Write Lock

When adding a write lock to a table:

Current connection:
- Can execute all operations (SELECT/INSERT/UPDATE/DELETE)
Other connections:
- All operations (including SELECT queries) will be blocked
- Blocking continues until the lock holder releases the lock (executes UNLOCK TABLES)

Lock Feature Comparison

Feature	Read Lock	Write Lock
Current connection read operation	Allowed	Allowed
Current connection write operation	Forbidden	Allowed
Other connections read operation	Allowed	Blocked
Other connections write operation	Blocked	Blocked
Lock conflict	Allows multiple read locks to coexist	Mutually exclusive with all other locks

Shared Lock (Row-Level Lock - Read Lock)

A shared lock, also known as read lock, abbreviated as S lock. A shared lock means multiple transactions can share one lock and all access the data, but can only read and not modify. The method to use a shared lock is: SELECT lock in share mode.

Exclusive Lock (Row-Level Lock - Write Lock)

An exclusive lock (Exclusive Lock), also known as write lock, abbreviated as X lock, is a basic database lock mechanism. This lock has exclusivity, meaning when one transaction acquires an exclusive lock on a certain data row, other transactions cannot perform any modification operations on that row, nor can they acquire any type of lock on that row.

Exclusive Lock Features

Exclusivity: Exclusive lock is mutually exclusive with any other lock (including shared locks and other exclusive locks), cannot coexist
Exclusive access: The transaction holding the exclusive lock can read and modify data
Blocks other operations: Other transactions cannot modify data, nor can they acquire new record locks through the for update clause

Usage

SELECT * FROM employees WHERE id = 1 FOR UPDATE;

InnoDB Engine Default Behavior

In UPDATE and DELETE statements, the InnoDB engine automatically adds exclusive locks to the rows being operated on
These operations do not need to explicitly specify the FOR UPDATE clause

Implementation Mechanism

The row-level lock implementation of exclusive locks depends on indexes on the table:

When using indexes: When operations use indexes for queries, InnoDB only locks row records that meet the conditions
When not using indexes: If queries do not use indexes, InnoDB degrades to locking all records in the entire table

Practical Application Scenarios

Inventory Deduction (preventing overselling):

BEGIN;
SELECT quantity FROM products WHERE product_id = 1001 FOR UPDATE;
UPDATE products SET quantity = quantity - 1 WHERE product_id = 1001;
COMMIT;

Account Transfer:

BEGIN;
SELECT balance FROM accounts WHERE account_id = 12345 FOR UPDATE;
-- Execute transfer operation
COMMIT;

Notes

Exclusive locks reduce system concurrent performance; try to shorten the time locks are held
Avoid holding locks while executing time-consuming operations in transactions
Ensure queries use appropriate indexes to prevent accidental table locking
All exclusive locks are automatically released when the transaction ends (commits or rolls back)

Optimistic Locking

Optimistic locking is a concurrency control mechanism, in stark contrast to pessimistic locking. It is not a database system built-in function but needs to be designed and implemented by developers. This mechanism is named for its optimistic assumption: the system believes that the current transaction will not conflict with other transactions, so it does not take any locking measures during the data operation phase.

Implementation Principles

Version field mechanism: Optimistic locking is usually implemented by adding a version version number field to the data table
Workflow:
- Read phase: When transaction A reads data, it simultaneously acquires the current version number of that data
- Modify phase: When transaction A is ready to update data, it first checks whether the current version number is still the original value
- Commit phase: If the version number matches, execute the update and increment the version number by 1; otherwise, the data has been modified and the operation fails

Specific SQL example:

-- Read phase
SELECT id, name, version FROM products WHERE id = 1;

-- Update phase (atomic operation)
UPDATE products
SET name = 'new product name', version = version + 1
WHERE id = 1 AND version = 1;

Applicable Scenarios

Optimistic locking applicable scenarios:

Applications with more reads and fewer writes (such as information websites)
Systems with low probability of concurrent conflicts
Business scenarios requiring high throughput
Example: Product browsing in e-commerce systems

Pessimistic locking applicable scenarios:

Applications with frequent write operations
Systems with extremely high data consistency requirements
Scenarios with high probability of concurrent conflicts
Example: Account balance modifications in banking systems

Performance Considerations

Optimistic locking advantages:

Reduces lock overhead
Improves system throughput
Avoids deadlock problems

Pessimistic locking advantages:

Guarantees strong consistency
Simple and direct implementation
Suitable for long transaction processing

Summary Comparison

Feature	Optimistic Locking	Pessimistic Locking
Implementation	Version number/timestamp	Database built-in lock
Locking timing	Commit phase	Access phase
Applicable scenarios	More reads, fewer writes	More writes, fewer reads
Concurrent performance	Relatively high	Relatively low
Failure handling	Retry mechanism	-

MySQL InnoDB Lock Mechanism Explained

Lock Classification

By Granularity

Locks in MySQL can be divided into three categories by operation granularity:

Table-level Locking

Locks the entire table for each operation, coarsest granularity
Small overhead, fast locking, but poor concurrent performance
High conflict probability, prone to blocking and waiting
Mainly used for non-transactional storage engines like MyISAM and Memory

Row-level Locking

Only locks the row records that need to be accessed for each operation
Smallest granularity, large overhead, but optimal concurrent performance
Lowest conflict probability, different transactions can simultaneously modify different rows
Implemented by InnoDB through index item locking, default locking mechanism

Page-level Locking

Locks a group of adjacent records each time (usually 4KB)
Medium granularity, between table locks and row locks
Used by BDB storage engine

By Type

Shared Lock (S lock/Read lock)

Allows multiple transactions to simultaneously read the same data
Multiple transactions can hold S locks simultaneously
During the period when S lock is held, other transactions cannot acquire X lock

Exclusive Lock (X lock/Write lock)

Ensures exclusivity during data modification
Only one transaction can hold X lock at a time
During the period when X lock is held, other transactions cannot acquire S lock or X lock

Intention Locks (IS, IX locks)

Intention locks are table-level locks used to improve lock detection efficiency:

IS lock (intention shared lock): Acquire IS lock on the table before adding S lock
IX lock (intention exclusive lock): Acquire IX lock on the table before adding X lock

Lock Compatibility Matrix:

Request\Hold	No Lock	IS	IX	S	X
IS	✓	✓	✓	✓	✗
IX	✓	✓	✓	✗	✗
S	✓	✓	✗	✓	✗
X	✓	✗	✗	✗	✗

Optimistic Locking vs Pessimistic Locking

Optimistic Locking

Assumes low probability of concurrent conflicts
Operates directly without locking, detects conflicts at commit time
Common implementations: version number mechanism, timestamp mechanism

Example:

UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 100 AND version = 5

Applicable to scenarios with more reads and fewer writes, low conflict rate.

Pessimistic Locking

Assumes concurrent conflicts are inevitable
Acquire locks before executing operations
Includes shared locks (S lock) and exclusive locks (X lock)

Example:

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Applicable to scenarios with frequent write operations, high conflict probability.

Row Lock Principles

InnoDB row locks are implemented by locking records on index data pages, mainly three algorithms:

1. Record Lock

Locks a single row record, only locks the index record itself
Supports RC and RR isolation levels
Used when using unique index for precise queries
Example: Locks the row with id=1

2. Gap Lock

Locks the gap between index records to prevent insertion of new records
Only supports RR isolation level
Prevents phantom read problems
Example: If table has id values 1,3,5, locks gaps (1,3) and (3,5)

3. Next-Key Lock

Combination of Record Lock and Gap Lock
Locks the record itself and the gap before the record
InnoDB’s default row lock algorithm
Only supports RR isolation level
Example: Locks interval (1,3]

Note: In RR isolation level, InnoDB first uses Next-Key Lock when locking records, but degrades to RecordLock when using unique indexes.

Lock Behavior for Different SQL Statements

SELECT (Regular Query)

Uses MVCC mechanism to achieve non-blocking read
Does not impose any locks, reads snapshot data at the start of the transaction

Adds shared lock (S lock)
Processed using Next-Key Lock
Degrades to RecordLock with unique indexes

SELECT … FOR UPDATE

Adds exclusive lock (X lock)
Uses Next-Key Lock by default
Degrades to RecordLock with unique indexes

UPDATE … WHERE

Uses Next-Key Lock to lock records matching conditions
Degrades to RecordLock with unique indexes

DELETE … WHERE

Similar to UPDATE
Simultaneously locks primary key indexes and all secondary indexes

INSERT

Sets exclusive RecordLock on new rows
When there is a unique key conflict, briefly acquires shared lock to check for conflicts

Lock Behavior for Different Indexes (RR Isolation Level)

Primary key locking: Only adds X lock on primary key index records

Unique key locking: Adds X lock on unique index, then adds X lock on primary key index records

Non-unique key locking:

Adds X lock on matching records and primary keys respectively
Adds Gap Lock within the range

No index locking: All rows and gaps in the table get X lock, resulting in full table locking

Gap Lock

Locks the gap between index records
Only supports RR isolation level
Prevents phantom reads

Next-Key Lock

Combination of Record Lock and Gap Lock
Locks both the record itself and the gap before the record
InnoDB’s default row lock algorithm

Practical Application Examples

Bank Transfer Scenario

BEGIN;
SELECT balance FROM accounts WHERE id=1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id=1;
COMMIT;

SELECT Statement Locking

-- Shared lock
SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE;

-- Exclusive lock
SELECT * FROM table WHERE id=1 FOR UPDATE;

Lock Behavior for Different Indexes

Primary key locking: Only adds X lock on primary key index records
Unique key locking: Adds X lock on unique index and primary key respectively
Non-unique key locking: Adds X lock and Gap Lock
No index locking: Full table locking