Transactions and locking in depth series: 1. Transactions from the inside · 2. Harmful transactions · 3. Locks in InnoDB · 4. Optimistic, pessimistic and the booking lab
When two requests change the same thing, ordering decides whether the result is right, and that’s the job of locks. After this post you’ll know InnoDB’s lock types, be able to read performance_schema.data_locks to see what each statement locks, and understand why one hot row caps throughput. Read first: transactions, isolation levels and locking (S/X locks, gap locks, deadlock basics) and part 1 (consistent reads versus current reads).
1. The problem: a transfer that makes the balance negative
Key idea: two requests both read a balance of 50, both see enough money, both subtract, and the balance ends at -20. The bug is deciding based on a value that’s already stale.
Example: A sends 30 to B and 40 to C at the same moment; the balance must never go negative.
sequenceDiagram
participant R1 as Request A→B 30
participant DB as account A
participant R2 as Request A→C 40
R1->>DB: SELECT amount (50)
R2->>DB: SELECT amount (50)
Note over R1: 50 >= 30, allowed
Note over R2: 50 >= 40, allowed
R1->>DB: UPDATE amount = amount - 30 (20)
R2->>DB: UPDATE amount = amount - 40 (-20)
This is a check-then-act race condition (the result depends on how concurrent steps interleave), not a lost update, since amount = amount - x doesn’t overwrite the other’s value. A transaction doesn’t save you: at REPEATABLE READ a plain SELECT reads the snapshot and locks nothing.
Three fixes, from cheapest to most expensive:
-- 1. Fold the check into the write: one atomic statement
UPDATE account SET amount = amount - 30 WHERE id = 'A' AND amount >= 30;
-- affected rows = 0 means insufficient funds
-- 2. Pessimistic: lock the row as you read it (exclusive lock)
START TRANSACTION;
SELECT amount FROM account WHERE id = 'A' FOR UPDATE; -- the other request waits here
UPDATE account SET amount = amount - 30 WHERE id = 'A';
COMMIT;
-- 3. Optimistic: no lock on read, detect the conflict on write
UPDATE account SET amount = 20, version = version + 1 WHERE id = 'A' AND version = 7;
The slides ask “which kind of lock solves this?”: an exclusive lock (FOR UPDATE), specifically a record lock on one record because the condition is a primary key equality (section 3 proves it).
2. MySQL’s lock types
Key idea: locks split by operation (S, X, intention) and by scope (instance, table, AUTO-INC, row). For applications, row locks matter most.
2.1. By operation: shared, exclusive and intention
Key idea: S lets many transactions do locking reads together; X allows only one holder and blocks both S and X. Intention locks (IS/IX) sit on the table to announce “I’m about to lock some rows”.
- Shared (S): a read lock that several transactions can hold. Taken by
SELECT ... FOR SHARE. - Exclusive (X): a write lock with a single holder. Taken by
SELECT ... FOR UPDATE,UPDATE,DELETE.
X only blocks locking reads; a plain SELECT still reads the row thanks to MVCC (the database keeps old row versions so readers don’t wait for writers).
Intention locks let a whole-table lock (LOCK TABLES ... WRITE) detect a conflict without scanning every row. Like an “occupied” sign at the building entrance: to lock the whole building you check the sign, not every door.
Intention lock compatibility table
| Held \ Requested | IS | IX | S (table) | X (table) |
|---|---|---|---|---|
| IS | granted | granted | granted | blocked |
| IX | granted | granted | blocked | blocked |
| S (table) | granted | blocked | granted | blocked |
| X (table) | blocked | blocked | blocked | blocked |
Two intention locks never block each other: a thousand transactions can all hold IX on one table and still run in parallel; real conflicts happen at row level.
2.2. By scope: from the instance down to a record
Key idea: scope ranges from the whole instance (backups) down to one index record. The four row lock kinds differ in whether they also lock the gap between records.
| Level | Example | When you meet it |
|---|---|---|
| Global | FLUSH TABLES WITH READ LOCK |
Backup tools need a consistent point; blocks every write on the instance |
| Table | LOCK TABLES t READ/WRITE, metadata locks (MDL), intention locks |
MDL: ALTER TABLE waits for every transaction using the table |
| AUTO-INC | A special lock while handing out AUTO_INCREMENT values |
Bulk inserts |
| Row | Record, gap, next-key, insert intention | Every UPDATE, DELETE, locking read, INSERT |
Row locks:
| Lock | Name in data_locks |
Locks the gap? | Meaning |
|---|---|---|---|
| Record lock | X,REC_NOT_GAP |
No | Locks exactly one index record |
| Gap lock | X,GAP |
Yes | Locks the gap before a record, blocking inserts into it |
| Next-key lock | X |
Yes | The record plus the gap before it |
| Insert intention | X,GAP,INSERT_INTENTION |
(waits on gaps) | Asks to insert into a gap; blocked by others’ gap/next-key locks |
AUTO-INC lock modes and the global lock for backups
The AUTO-INC lock has improved a lot (the slides note this). The innodb_autoinc_lock_mode setting:
0(traditional): a table-level lock until the end of theINSERT, so concurrent inserts queue.1(consecutive): simple inserts hold only a very short mutex; bulk inserts with an unknown row count (INSERT ... SELECT) still lock the table.2(interleaved): the default since MySQL 8.0 (I checked on 8.4: the value is 2). No table lock; ids from concurrent bulk inserts may interleave. It’s safe because the binlog defaults toROWformat.
In every mode, ids handed to an insert that rolled back are never reused, so gaps in the id sequence are normal.
3. Reading performance_schema.data_locks
Key idea: don’t guess, look: open a transaction, run one statement, read data_locks, roll back. A primary key equality locks one record; ranges, secondary indexes and unindexed columns at RR also lock gaps.
SELECT index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 'account_balance';
Test table: account_balance(account_number PK, owner_id, balance, note), secondary index idx_owner(owner_id), 4 rows with account_number = 1, 2, 5, 9 (owner_id 10, 20, 20, 30 respectively). The note column has no index. Run on MySQL 8.4.11 in Docker.
| Statement | REPEATABLE READ | READ COMMITTED |
|---|---|---|
UPDATE ... WHERE account_number = 2 |
X,REC_NOT_GAP on 2 |
same as RR |
UPDATE ... WHERE account_number BETWEEN 2 AND 5 |
X,REC_NOT_GAP on 2, X (next-key) on 5 |
X,REC_NOT_GAP on 2 and 5 |
UPDATE ... WHERE owner_id = 20 (secondary index) |
idx_owner: X on (20,2), (20,5), X,GAP on (30,9); PRIMARY: REC_NOT_GAP on 2, 5 |
REC_NOT_GAP on (20,2), (20,5) and PK 2, 5 |
UPDATE ... WHERE note = 'c' (no index) |
X on all 4 rows + the supremum pseudo-record |
only REC_NOT_GAP on 5 |
SELECT ... WHERE account_number = 4 FOR UPDATE (doesn’t exist) |
X,GAP on 5 |
no row locks at all |
Every case also has a TABLE ... IX row (the intention lock).
- Primary key equality: one record lock at both levels. The cheapest case, and the “record lock” answer for the transfer problem.
- A primary key range at RR: the next-key lock on 5 covers the gap (2, 5). I tried it:
INSERT ... VALUES (4, ...)in another session waited (X,GAP,INSERT_INTENTION,WAITINGon record 5), whileVALUES (7, ...)went straight through. - A non-unique secondary index at RR: two sets of locks (secondary index and primary key), plus a gap lock blocking inserts with
owner_idbetween 20 and 30. - No index at RR: every scanned row is locked, i.e. the whole table, including the “supremum” (a pseudo-record marking the gap after the last row). RC releases rows that don’t match the
WHEREright away.
“Why does it lock extra rows?” To keep REPEATABLE READ: rerunning a locking read in the same transaction must not show new rows (phantoms). So InnoDB locks the ranges of the index it scanned, not just the matching rows; the less selective the index, the wider the range. RC makes no such promise, so it locks far less. (The slides quote Nichter advising not to dig too deep because “InnoDB is a black box”.)
The outdated range-lock diagram and how to see who blocks whom
When there is real waiting, the sys.innodb_lock_waits view tells you who blocks whom, and even suggests the KILL statement:
waiting_query: INSERT INTO account_balance VALUES (4,40,0,'x')
blocking_pid: 21
blocking_query: DO SLEEP(6)
wait_age: 00:00:01
sql_kill_blocking_connection: KILL 21
blocking_query is the statement the blocking connection is running now, not the one that took the lock (here the lock came from an earlier UPDATE). To find the statement that caused the lock, look at performance_schema.events_statements_history for that thread.
4. Lock contention: low or high
Key idea: contention is how much requests compete for the same resource. High contention caps throughput by lock hold time, and adding servers doesn’t help.
With the same 1,000 requests per second:
- Low: spread over a million accounts, two requests touching the same row is rare.
- High: all on one row, like the seats-left counter of a flight that just went on sale.
Hold the hot row’s lock for 5 ms and that row serves at most ~200 transactions per second. Monitor it with SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%' (Innodb_row_lock_waits, Innodb_row_lock_time_avg) and sys.innodb_lock_waits.
The level of contention decides between pessimistic and optimistic locking. Part 4 compares pessimistic locking, optimistic locking, conditional updates and a Redis lock with real measurements on the booking exercise.
Exercises
Exercise 1: Which lock solves the transfer problem
Task (paraphrased): for the transfer problem in section 1, (a) which lock type by operation is needed, (b) which kind of row lock.
Solution to exercise 1
(a) An exclusive lock, because the second request must wait for both the read-to-decide and the write. FOR SHARE isn’t enough: both sides holding S and then both requesting X deadlock.
(b) A record lock: the condition is a primary key equality, so data_locks shows only X,REC_NOT_GAP on account A (the first row of the table in section 3). Better still, skip the read: UPDATE account SET amount = amount - 30 WHERE id = 'A' AND amount >= 30 takes exactly that record lock in one statement.
Exercise 2: Predict, then verify the locks
Task (my own addition): before putting an UPDATE on a hot path, predict what it locks and verify it.
Solution to exercise 2: a script that reads data_locks
The process I used for the table in section 3: create a small table with the same kinds of indexes as production, open a transaction at the application’s isolation level, run the statement, read data_locks, roll back. Turn it into a script that takes the statement as a parameter, so it can run in CI when a hot query changes:
run() { # $1 = isolation level, $2 = statement
mysql -t lab <<SQL
SET SESSION TRANSACTION ISOLATION LEVEL $1;
START TRANSACTION;
$2;
SELECT index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_name = 'account_balance' ORDER BY index_name, lock_data;
ROLLBACK;
SQL
}
run "REPEATABLE READ" "UPDATE account_balance SET balance = balance + 10 WHERE owner_id = 20"
The most common surprises: a statement filtering on a non-unique secondary index also gap-locks the next record, and a statement with no usable index locks the whole table.
Remember
- Explain why read-check-write inside a RR transaction still drives the balance negative, and the three fixes.
- Know why
FOR SHAREdeadlocks in the transfer problem whileFOR UPDATEdoesn’t. - Tell record, gap, next-key and insert intention locks apart, with their names in
data_locks. - Predict what an
UPDATElocks at RR and RC, then verify it. - Work out a hot row’s throughput ceiling from its lock hold time.
Interview questions
Junior
Why is an UPDATE by primary key the cheapest kind of locking in InnoDB?
Because it sets a single record lock (X,REC_NOT_GAP) on exactly one record, at both REPEATABLE READ and READ COMMITTED.
An equality match on the primary key finds exactly one record in the clustered index. No gap lock, no extra locks on secondary indexes, so it doesn’t block inserts or updates of other rows.
Mid
What are intention locks for?
They let a whole-table lock request detect a conflict immediately, without scanning every row.
They’re table-level locks (IS/IX) set before locking rows, announcing “this transaction holds S/X locks on some rows”. Intention locks don’t block each other, so they don’t reduce row-level concurrency.
What does an UPDATE on an unindexed column lock at REPEATABLE READ?
Effectively the whole table: next-key locks on every record it scanned plus the supremum.
It has to scan the clustered index, so it blocks all other updates and inserts. At READ COMMITTED, InnoDB releases the locks on rows that don’t match the WHERE right away. The right fix is an index for the condition.
Senior
data_locks shows X,GAP and X,GAP,INSERT_INTENTION WAITING on the same record. What is going on?
One transaction holds a gap lock on the gap before that record, and another wants to INSERT into that gap, so it waits.
The gap (or next-key) lock typically comes from a locking read or a range UPDATE at RR, or a SELECT ... FOR UPDATE on a value that doesn’t exist. If both hold the gap and both insert, it becomes a deadlock. Ways out: use a unique constraint instead of check-then-insert, lock by primary key, or consider READ COMMITTED.
A diagram says a range UPDATE also locks the record after the range. How do you verify it?
Run that statement in a transaction on the exact MySQL version you use, then read performance_schema.data_locks.
Look at index_name, lock_mode, lock_data. On MySQL 8.4, BETWEEN 2 AND 5 on the primary key only locks record 2 and next-key 5, not the following record; older versions did. Locking behaviour changes between versions, so always measure instead of trusting a diagram.
Useful phrases for interviews
- “Checking in a plain SELECT and then writing is a check-then-act race, even inside a transaction.”
- “An equality match on the primary key takes a single record lock, which is the cheapest case.”
- “At repeatable read, InnoDB locks the ranges it scanned, not just the matching rows, to prevent phantoms.”
- “Intention locks live at table level and never conflict with each other.”
- “Before shipping a hot query, I run it in a transaction and read performance_schema.data_locks.”
- “Without an index, an UPDATE at repeatable read effectively locks the whole table.”
- “One hot row caps throughput at roughly one over the lock hold time.”
Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.