MySQL: MySQL Locking Mechanisms and the Principles of MVCC
Last updated: 2026-08-26
Locks are at the heart of concurrency control—you can only write efficient concurrent code by understanding how locks work.
This lesson explains MySQL's locking mechanism.
1. What You'll Learn
- Table Locks vs. Row Locks
- Shared locks (S locks) and exclusive locks (X locks)
- Intention Lock
- MVCC (Multi-Version Concurrency Control)
- Deadlock Detection and Handling
2. Types of Locks
graph TB
A[MySQL Locks] --> B[By Granularity]
A --> C[By Type]
B --> D[Table Lock Table Lock]
B --> E[Row Lock Row Lock]
B --> F[Page Lock]
C --> G[Shared Lock S Lock]
C --> H[Exclusive Lock X Lock]
C --> I[Intent Lock Intention Lock]
| Dimension | Table Lock | Row Lock |
|---|---|---|
| Granularity | Entire table | Single row |
| Probability of Conflict | High | Low |
| Concurrency Performance | Low | High |
| Cost | Low | High |
| Engine | MyISAM/InnoDB | InnoDB |
3. Shared Locks and Exclusive Locks
(1) Shared Lock (S Lock)
SQL
-- Acquire a shared lock (read lock) — MySQL 8.0+ Recommended Syntax
SELECT * FROM users WHERE id = 1 FOR SHARE;
-- MySQL 5.7 old syntax (8.0 deprecated but still usable)
-- SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- Other transactions can read, but cannot write
(2) Exclusive Lock (X Lock)
SQL
-- Set an exclusive lock (write lock)
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- Other transactions cannot read (if also locked) or write
| Lock Type | Read | Write |
|---|---|---|
| Shared Lock S | ✅ | ❌ |
| Exclusive Lock X | ❌ | ❌ |
4. Intent Lock
An intent lock is a table-level lock used to quickly determine whether a row in a table is locked.
| Intent Lock | Description |
|---|---|
| IS (Intent Shared Lock) | The transaction intends to place an S lock on the row |
| IX (Intent Exclusive Lock) | The transaction intends to place an X lock on the row |
Purpose: When acquiring a table lock, there is no need to check each row individually; only the intended lock needs to be checked.
▶ Example: Shared vs Exclusive Lock
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
Query OK, 0 rows affected
SQL
-- Session 1: Acquire a shared lock (others can still read)
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Other sessions can also SELECT ... FOR SHARE, but cannot FOR UPDATE
-- Session 2: Attempt to acquire an exclusive lock (will wait)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Blocks until Session 1 commits
-- Session 1: Commit
COMMIT;
-- Session 2 now acquires the exclusive lock
Output:
TEXT
📖 Display only
Output displayed
▶ Example: Practical Applications of Locks
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 0 rows affected
SQL
-- Session 1: Set an exclusive lock
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- At this moment id=1 is locked
-- Session 2: Try updating (Waiting)
UPDATE accounts SET balance = 500 WHERE id = 1;
-- Wait until Session 1 commits or times out
-- Session 1: Commit
COMMIT;
-- Session 2 execution succeeds
Output:
TEXT
📖 Display only
Output displayed
5. MVCC (Multi-Version Concurrency Control)
MVCC enables lock-free reads by maintaining multiple versions of the data.
(1) How MVCC Works
graph LR
A[Current Data] --> B[Undo Log<br/>History of Versions]
B --> C[v1] --> D[v2] --> E[v3]
F[Read View] -->|Based on the quarantine level| G[Which version to read?]
(2) Read Behavior for Different Isolation Levels
| Isolation Level | Read Mode |
|---|---|
| READ UNCOMMITTED | Always reads the latest (may result in a dirty read) |
| READ COMMITTED | Create new Read View for each SELECT |
| REPEATABLE READ | Create a Read View at the start of the transaction |
| SERIALIZABLE | Locked Read |
6. Deadlock
(1) What Is a Deadlock?
Two transactions are waiting for each other to release a lock:
SQL
-- TransactionsA
START TRANSACTION;
UPDATE accounts SET balance = 100 WHERE id = 1; -- Lock id=1
UPDATE accounts SET balance = 200 WHERE id = 2; -- Waiting for id=2
-- Transaction B
START TRANSACTION;
UPDATE accounts SET balance = 300 WHERE id = 2; -- Lock id=2
UPDATE accounts SET balance = 400 WHERE id = 1; -- Waiting for id=1 (Deadlock!)
(2) Deadlock Handling
SQL
-- View deadlock information
SHOW ENGINE INNODB STATUS\G
-- Set the lock wait timeout (seconds)
SET innodb_lock_wait_timeout = 50; -- seconds
💡
\G is a MySQL command-line client directive that displays query results vertically. It only works in the mysql CLI, not in application code or GUI tools.
(3) Avoid Deadlocks
| Strategy | Description |
|---|---|
| Fixed Order | Access tables and rows in the same order |
| Reduce lock hold time | Commit transactions as soon as possible |
| Use a lower isolation level | Fewer READ COMMITTED locks |
| Create Indexes Wisely | Reduce Lock Scope |
7. Lock Wait Timeout
SQL
-- View Lock Wait Timeout
SELECT @@innodb_lock_wait_timeout;
-- Settings (seconds)
SET innodb_lock_wait_timeout = 10;
-- Timeout Error
-- ERROR 1205: Lock wait timeout exceeded
▶ Example: Observing MVCC Read Behavior
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Query OK, 1 row affected
Query OK, 0 rows affected
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
Query OK, 0 rows affected
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 0 rows affected
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
Query OK, 0 rows affected
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
SQL
-- Setup: Create a test table and insert data
CREATE TABLE mvcc_test (id INT PRIMARY KEY, value INT);
INSERT INTO mvcc_test VALUES (1, 100);
-- Session 1: Start transaction and read
START TRANSACTION;
SELECT * FROM mvcc_test WHERE id = 1; -- Returns value = 100
-- Session 2: Update the row and commit
START TRANSACTION;
UPDATE mvcc_test SET value = 200 WHERE id = 1;
COMMIT;
-- Session 1: Read again (still sees value = 100 under REPEATABLE READ)
SELECT * FROM mvcc_test WHERE id = 1; -- Returns value = 100 (snapshot)
-- Session 1: Commit and read again
COMMIT;
SELECT * FROM mvcc_test WHERE id = 1; -- Returns value = 200 (latest)
Output:
TEXT
📖 Display only
Output displayed
❓ FAQ
Q Does InnoDB use table locks or row locks?
A By default, it uses row locks (index-based). When no index is present, it falls back to table locks.
Q Does a SELECT statement acquire a lock?
A A regular SELECT statement does not acquire a lock (MVCC snapshot read).
SELECT ... FOR UPDATE acquires an exclusive lock, and SELECT ... FOR SHARE (MySQL 8.0+) acquires a shared lock. The legacy syntax LOCK IN SHARE MODE has been deprecated in 8.0.Q How do I view the current locks?
A
SELECT * FROM performance_schema.data_locks; (MySQL 8.0)Q Does InnoDB use table locks or row locks?
A By default, it uses row locks, which are implemented based on indexes. When no indexes are present, it falls back to table locks.
Q Does a SELECT statement acquire a lock?
A A regular SELECT statement does not acquire a lock (MVCC snapshot read); FOR UPDATE acquires an exclusive lock; FOR SHARE acquires a shared lock.
📖 Summary
- Table locks have a coarse granularity and low concurrency, while row locks have a fine granularity and high concurrency.
- Share locks (S) allow multiple reads, while exclusive locks (X) allow exclusive writes
- MVCC enables lock-free reads through multi-version concurrency control
- Deadlock occurs when processes wait for each other; MySQL automatically detects it and rolls back one of the transactions.
- Avoid deadlocks: Fixed access order, Reduce lock holding time
📝 Exercises
-
Basic Question (Difficulty: ⭐): Demonstrate the difference between a shared lock and an exclusive lock.
-
Advanced Exercise (Difficulty: ⭐⭐): Construct a deadlock scenario and observe how MySQL handles it.
-
Challenge Question (Difficulty: ⭐⭐⭐): Compare concurrent read and write behavior under different isolation levels.
