MySQL Concurrency: MVCC, Lock Waits, and Deadlocks in One Example
Use two sessions to understand snapshot reads and conflicting updates, inspect deadlocks, and design retries at the transaction boundary.
Read in Chinese →Engineering notes with synthetic examples. Version scope and references are included in each article.
Scope: MySQL 8.0 / InnoDB. The examples use explicit transactions and assume REPEATABLE READ unless stated otherwise. Run the experiments in an isolated test database.
The claim that “MVCC prevents read/write conflicts” is only partly true. Consistent nonlocking reads, locking reads, and modification statements use different mechanisms. Mixing them up can produce code that works with one client but oversells inventory or blocks for a long time under concurrency.
A snapshot does not enforce a business invariant
Start with a small test table:
CREATE TABLE inventory (
sku_id BIGINT PRIMARY KEY,
available INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO inventory VALUES (1, 10), (2, 10);
Session A starts a transaction and performs an ordinary read of sku_id=1. Session B updates that row and commits. A then repeats its ordinary read. Under REPEATABLE READ, A generally sees the old value through the same consistent snapshot. Under READ COMMITTED, each consistent read establishes a new snapshot. A transaction’s own changes introduce visibility exceptions, so a snapshot is not simply a frozen copy of the entire database. See consistent nonlocking reads
.
If the application reads inventory, subtracts one locally, and writes the computed value back, two requests can start from the same old value. For a single-row decrement, putting the condition and change in one statement is clearer:
UPDATE inventory
SET available = available - 1
WHERE sku_id = 1 AND available > 0;
The application checks the affected-row count: one means the decrement succeeded; zero means no qualifying row was available. If the operation also changes an order or a quota, it still needs a complete transaction. Repeated business requests need a unique constraint or another idempotency mechanism.
The access path determines what gets locked
InnoDB record locks apply to index records. Range access can also involve gap locks and next-key locks. A lookup of one unique value through a unique index does not have the same locking footprint as a range scan using nonunique conditions. The mechanisms are described in InnoDB Locking .
When two requests update different business objects but still block each other, I first collect the actual SQL, parameters, isolation level, and execution plan. Without a suitable index, a narrow business condition can lead to a much broader scan and locking footprint. I also check for network calls inside the transaction: locks may already be held while the application waits for an external service.
Reproduce a deadlock with two sessions
Execute the following steps in order. The last step closes the wait cycle:
| Step | Session A | Session B |
|---|---|---|
| 1 | BEGIN; | BEGIN; |
| 2 | Update sku_id=1 | Update sku_id=2 |
| 3 | Update sku_id=2; wait for B | No action yet |
| 4 | Still waiting | Update sku_id=1; wait for A |
With deadlock detection enabled, as it is by default, InnoDB chooses a victim transaction to roll back so that the other can proceed. A deadlock and a lock wait timeout are different events. In particular, a lock wait timeout must not be assumed to roll back the entire transaction. After an error, end the current transaction according to the driver and transaction manager’s semantics before deciding whether to retry.
After reproducing the issue, inspect:
SHOW ENGINE INNODB STATUS;
SELECT * FROM performance_schema.data_lock_waits;
The first exposes information about the latest deadlock; the second helps inspect current wait relationships. A relationship may disappear as soon as the wait ends, so retain the SQL, transaction IDs, and timestamps from the incident. See the official data_lock_waits definition .
Retry the business transaction
A direct way to reduce deadlocks is to acquire resources in a consistent order across entry points. For example, sort SKU IDs before updating multiple inventory rows. Keep transactions short, perform calculations that do not depend on locks beforehand, and index the filtering conditions appropriately. These measures do not eliminate all deadlocks; applications must still handle retries, as the official deadlock guidance explains.
Retry the complete business transaction. Retrying only its final failed statement does not restore earlier changes that were rolled back. Bound the number of attempts, use randomized backoff, and return a clear error when the overall deadline is exhausted. Side effects outside the database transaction, such as sending messages or calling a payment service, require idempotency keys or a transactional messaging design before they can safely be repeated.
Validation should check more than an improved success rate. Inventory must remain nonnegative, orders and decrements must correspond, and failed requests must not leave transactions open. Business invariants provide the evidence that concurrency control works.