Recently we saw an odd production anomaly. The code had been around for a while, and I am not sure who wrote it or why this check was added, but the rough logic looked like this:

  1. records, err := queryDB(conds)
  2. ... ... // a bunch of business logic
  3. rows, err := updateDB(conds)
  4. if len(rows) != len(records) {
  5.     reportErrorMetrics()
  6. }

The logs showed that records contained 8 rows, but the update reported 9 rows. That was puzzling. So I checked all records matching the condition and noticed that one row had created_time and updated_time very close to each other.

Our default transaction isolation level is RR, so intuitively this should not happen. But after digging into RR more carefully, I found that this situation is indeed possible. This article summarizes the RR details I learned along the way. As usual, the discussion is about MySQL InnoDB; I have not gone deeply into other storage engines here.

1. Manual Experiment: SELECT Sees 8 Rows, UPDATE Matches 9 Rows in the Same Transaction

To confirm that this was not just a counting bug in business code, we can reproduce it manually with two MySQL sessions.

Assume the user table currently has 8 rows in the range id > 10 AND id < 20, and all of those rows have status = 0. Also assume there is no row at id = 15 yet.

First, open the first session as transaction A:

  1. -- Session A
  2. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  3. BEGIN;
  5. SELECT COUNT(*) AS records
  6. FROM user
  7. WHERE id > 10 AND id < 20;

The result is:

  1. records
  2. -------
  3. 8

This step is important: this ordinary SELECT is a snapshot read. It creates a Read View in transaction A. Later ordinary SELECT statements in transaction A will keep reusing this Read View.

Then open a second session as transaction B, insert one new row into the same range, and commit:

  1. -- Session B
  2. BEGIN;
  4. INSERT INTO user(id, status) VALUES (15, 0);
  5. COMMIT;

Now go back to transaction A and run the same ordinary SELECT again:

  1. -- Session A
  2. SELECT COUNT(*) AS records
  3. FROM user
  4. WHERE id > 10 AND id < 20;

You will still see:

  1. records
  2. -------
  3. 8

So far, everything matches the intuition behind REPEATABLE READ: transaction A’s ordinary SELECT cannot see the new row inserted and committed by transaction B after A’s Read View was created.

But if transaction A then runs an UPDATE with the same range condition:

  1. -- Session A
  2. UPDATE user
  3. SET status = 1
  4. WHERE id > 10 AND id < 20;

MySQL may return something like this:

  1. Query OK, 9 rows affected
  2. Rows matched: 9  Changed: 9  Warnings: 0

This is the anomaly we wanted to reproduce:

  1. Inside the same transaction A:
  2. the earlier ordinary SELECT saw 8 rows;
  3. the later UPDATE matched 9 rows.

The key point is:

  1. An ordinary SELECT is a snapshot read and sees versions visible under the Read View;
  2. UPDATE is a current read and reads the latest currently updatable records.

So this experiment is not saying that “RR failed”. It is saying that the same transaction mixed two different read semantics: the earlier ordinary SELECT viewed the world through an old snapshot, while the later UPDATE operated on the currently updatable data.

Once this anomaly is clear, the following concepts have a concrete anchor: Read View, current read, SELECT ... FOR UPDATE, gap lock, and next-key lock all explain the same underlying question:

  1. How does InnoDB provide a consistent snapshot while still handling the current real records when data must be modified?

2. Do Not Blame RR Too Quickly: Two Different Read Semantics Are Mixed Here

When you first see the experiment result, the natural reaction is: isn’t this REPEATABLE READ? Why can the same transaction see 8 rows at one moment and 9 rows later?

The problem is that these two operations are not the same kind of read.

The earlier SELECT COUNT(*) in transaction A is an ordinary SELECT. In InnoDB, it is a snapshot read. It asks:

  1. Which versions should be visible according to my transaction snapshot?

The later UPDATE is different. It has to actually modify data, so it is a current read. It asks:

  1. Which committed records currently exist in the B+Tree and can be updated by me?

So the key point of this experiment is not “RR definitely saw a phantom read”. It is:

  1. The ordinary SELECT reused an old snapshot;
  2. the UPDATE read and modified currently updatable versions.

Once these two kinds of reads are separated, the behavior becomes much less strange.

3. Which Half of the Problem Does MVCC Solve?

Return to the experiment. After Session B inserted id = 15 and committed, Session A ran another ordinary SELECT COUNT(*), and the result was still 8.

This means MVCC is working.

When Session A ran its first ordinary SELECT, it created a Read View. Later ordinary SELECT statements keep using this Read View to decide whether a version is visible. The row inserted later by Session B is invisible to this old Read View, so the ordinary query still returns 8 rows.

From this perspective, we can say:

  1. RR + MVCC
  2. ensures that ordinary snapshot reads inside the same transaction do not see newly inserted rows committed later.

But MVCC only answers one question:

  1. Is this version visible to my snapshot?

It does not answer another question:

  1. Can someone else insert a new row into this range?

Nor does it guarantee:

  1. The later UPDATE can only update the 8 rows seen by the earlier SELECT.

So a more precise statement is: MVCC solves consistency for snapshot reads. For current reads, such as UPDATE, DELETE, and SELECT ... FOR UPDATE, we also have to look at locks.

4. When Is the Read View Created?

There is a critical detail in the experiment above: Session A’s Read View is not automatically created at the moment of BEGIN. It is created on the first ordinary snapshot read.

That is this step:

  1. -- Session A
  2. SELECT COUNT(*) AS records
  3. FROM user
  4. WHERE id > 10 AND id < 20;

After this ordinary SELECT executes, transaction A has a Read View that later ordinary queries can reuse.

Under RR, you can usually understand it this way:

  1. BEGIN only starts the transaction;
  2. the first snapshot read creates the Read View;
  3. later ordinary SELECT statements reuse the same Read View.

This also explains a common trap. Suppose transaction A only runs BEGIN, but has not executed any ordinary SELECT yet:

  1. -- Session A
  2. BEGIN;

Then transaction B commits a change first:

  1. -- Session B
  2. UPDATE user SET name = 'Alice' WHERE id = 1;
  3. COMMIT;

Now transaction A runs its first ordinary query:

  1. -- Session A
  2. SELECT name FROM user WHERE id = 1;

Transaction A can see Alice, because its Read View is created at this SELECT.

But if transaction A had already queried once:

  1. -- Session A
  2. BEGIN;
  3. SELECT name FROM user WHERE id = 1;
  4. -- The Read View is created here, and it sees Tom

Then transaction B commits Alice:

  1. -- Session B
  2. UPDATE user SET name = 'Alice' WHERE id = 1;
  3. COMMIT;

When transaction A runs another ordinary query, it will still use the old Read View:

  1. -- Session A
  2. SELECT name FROM user WHERE id = 1;
  3. -- It still sees Tom

There is one exception:

  1. START TRANSACTION WITH CONSISTENT SNAPSHOT;

This statement creates a consistent snapshot at the start of the transaction. In other words, it pins the Read View earlier.

5. What If We Use SELECT … FOR UPDATE from the Beginning?

In the earlier experiment, Session A initially ran only an ordinary SELECT. It did not lock the range, so Session B could insert id = 15 and commit.

What if the business code did not use an ordinary query, but started with:

  1. SELECT *
  2. FROM user
  3. WHERE id > 10 AND id < 20
  4. FOR UPDATE;

Then the situation changes.

SELECT ... FOR UPDATE is a locking read, and it is also a current read. It is not trying to “see what exists in an old snapshot”. It is trying to:

  1. Read the latest currently modifiable data and lock the records that may be modified next.

If the condition can use a suitable index, then under RR, InnoDB will usually lock the scanned range. If Session B then tries to insert id = 15 into that range, it may be blocked until Session A commits or rolls back.

This is the typical use case for SELECT ... FOR UPDATE. For example, inventory deduction:

  1. BEGIN;
  3. SELECT stock
  4. FROM product
  5. WHERE id = 1
  6. FOR UPDATE;
  8. UPDATE product
  9. SET stock = stock - 1
  10. WHERE id = 1;
  12. COMMIT;

It expresses this intent:

  1. I want to read this row first, and I will probably modify it next.
  2. Before I commit or roll back, other transactions should not race to modify this row.

We can compare ordinary SELECT and SELECT ... FOR UPDATE like this:

Type Ordinary SELECT SELECT ... FOR UPDATE
Read semantics Snapshot read Current read
Goal See versions visible in the snapshot Find the latest lockable version
Locks? No Takes exclusive locks
Reuses old Read View under RR? Yes No
Suitable for Display, statistics, ordinary queries Read-then-update workflows, avoiding concurrent modification of the same data set

6. A Version Visible Under a Read View Is Not the Same as the Latest Lockable Version

To make the difference more concrete, look at a single row first.

The initial data is:

  1. id = 1, name = 'Tom'

Session A runs an ordinary query:

  1. -- Session A
  2. BEGIN;
  4. SELECT name FROM user WHERE id = 1;
  5. -- Sees Tom and creates a Read View

Session B modifies the row and commits:

  1. -- Session B
  2. UPDATE user SET name = 'Alice' WHERE id = 1;
  3. COMMIT;

Session A runs another ordinary query:

  1. -- Session A
  2. SELECT name FROM user WHERE id = 1;
  3. -- Still sees Tom

At this point, Tom is the historical version visible under the Read View.

But if Session A then executes:

  1. -- Session A
  2. SELECT name FROM user WHERE id = 1 FOR UPDATE;

It cannot lock the old version Tom in the undo log. The old version is only used to construct a snapshot result; it is not the real currently lockable record in the B+Tree.

So a current read must find the latest lockable version. After Session B has committed, Session A may read and lock Alice.

Put this back into the range experiment from section 1:

  1. The ordinary SELECT sees the 8 rows visible under the Read View;
  2. UPDATE handles the 9 latest currently updatable rows in the range.

7. Back to the Experiment: Where Did the 9th Row Come From?

Now the answer to the experiment in section 1 is much clearer.

The 9th row is the id = 15 row inserted and committed by Session B. It was inserted after Session A’s Read View had been created, so Session A’s ordinary SELECT COUNT(*) cannot see it.

But UPDATE is a current read. When it evaluates the range condition, it does not use the set of 8 rows visible in the old Read View. It reads the records that are currently committed and updatable.

That is why the experiment shows:

  1. Ordinary SELECT: 8 rows
  2. UPDATE: Rows matched: 9

If the 9th row already had the target status, MySQL might instead show:

  1. Rows matched: 9
  2. Changed: 8

It is useful to distinguish these two numbers:

  1. Rows matched means how many rows matched the WHERE condition;
  2. Changed means how many rows were actually changed.

There is one more detail. After Session A finishes this UPDATE, if it runs another ordinary SELECT:

  1. SELECT *
  2. FROM user
  3. WHERE id > 10 AND id < 20;

It may also see 9 rows. The reason is that the row that was originally invisible to the old Read View has now been updated by Session A itself, and a transaction can always see its own modifications.

So the core of this production anomaly is not “RR became non-repeatable”. It is:

  1. Inside the same transaction, ordinary SELECT and UPDATE use different read semantics.

8. If UPDATE Is Already Running, Can Someone Else Still Insert?

In the earlier experiment, the order was:

  1. Session A first runs an ordinary SELECT and creates a Read View;
  2. Session B inserts and commits;
  3. Session A later runs UPDATE.

So Session B’s new row was already committed before Session A’s UPDATE ran, and Session A could match it.

If the order is reversed, the result is different.

For example, Session A has already started a range UPDATE:

  1. -- Session A
  2. BEGIN;
  4. UPDATE user
  5. SET status = 1
  6. WHERE id > 10 AND id < 20;

If there is a suitable index on id, InnoDB under RR will usually take next-key locks / gap locks on this range. At this point, if Session B tries to insert:

  1. -- Session B
  2. INSERT INTO user(id, status) VALUES (15, 0);

Session B is likely to be blocked until Session A commits or rolls back.

So when investigating this kind of issue, the timeline matters a lot:

Timeline Result
B inserts and commits first, then A runs the range UPDATE A may update the row newly inserted by B
A’s range UPDATE has already started and locked the range, then B inserts B is usually blocked
B inserts but has not committed, then A runs UPDATE A usually waits; if B commits, the row may be updated; if B rolls back, the row does not exist

This is also why the question “Can RR solve phantom reads?” is not enough by itself. You have to ask whether the read is an ordinary snapshot read or a locking current read.

9. How Do Gap Lock, Record Lock, and Next-Key Lock Connect to This Experiment?

Now the question moves one step deeper: if a current read needs to stop others from inserting new rows into a range, how does InnoDB do that?

Start with three concepts:

Lock What it locks Purpose
record lock An existing index record Prevent others from modifying or deleting this record
gap lock The gap between two index records Prevent others from inserting new records into this gap
next-key lock The semantic combination of record lock + gap lock Lock one record and the gap before it

Assume the index contains these values:

  1. 10, 20, 30

The gaps are:

  1. (-∞, 10)
  2. (10, 20)
  3. (20, 30)
  4. (30, +∞)

If a transaction performs a current read:

  1. SELECT *
  2. FROM user
  3. WHERE id > 10 AND id < 20
  4. FOR UPDATE;

Even if there are currently no records in (10, 20), InnoDB still needs to stop other transactions from inserting:

  1. INSERT INTO user(id) VALUES (15);

Otherwise, a later current read would suddenly find one more row in the range. This is the “phantom row” that current-read semantics must handle.

A next-key lock can be understood as:

  1. (previous index value, current index value]

For example, taking a next-key lock on 20 semantically locks:

  1. (10, 20]

Broken apart, that means:

  1. gap lock:    (10, 20)
  2. record lock: 20

It prevents others both from modifying 20 and from inserting 15.

10. Is Next-Key Lock a Real Independent Lock Object?

There is another easy misunderstanding here: when we say next-key lock, it sounds like it is an independent lock type.

As a behavioral explanation, that is fine:

  1. next-key lock ≈ record lock + gap lock

But if we look at InnoDB’s internal implementation, it does not necessarily create a separate physical object literally named “next-key lock”. More precisely, InnoDB expresses these semantics using different modes or flags on index record locks:

  1. record-only
  2. gap-only
  3. record + gap, which is the effect of next-key lock
  4. insert intention lock

So we can think about it at three levels:

Level Statement
Conceptual level It is fine to talk about the concept of next-key lock
Behavioral level It really behaves like “record + gap”
Implementation level It may not be an independent physical lock object; it is an index record lock plus gap semantics

For troubleshooting production issues, the behavioral level matters most: if a current read locks a range, other transactions may be prevented from inserting new rows into that range.

11. What Other Common Locks Does InnoDB Have?

Following the same thread, InnoDB locks are not limited to record locks and gap locks. Those are just the locks we encountered first because this production anomaly is closely related to range updates and current reads.

Here is a compact table of common locks:

Lock Level Purpose
S Lock / X Lock Row or record lock mode Shared lock and exclusive lock
Intention Lock Table level Indicates that the transaction intends to take S/X locks on some rows in the table
Insert Intention Lock Special lock on a gap Indicates that a transaction wants to insert a record into a gap
Auto-Inc Lock Table level Protects auto-increment value allocation
Next-Key Lock Conceptual semantics record + gap, used to prevent phantom rows in range current reads

S Lock / X Lock

Shared and exclusive locks are the most basic lock modes.

  1. SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;

This kind of statement tries to take a shared lock, or S Lock.

  1. SELECT * FROM user WHERE id = 1 FOR UPDATE;

This kind of statement tries to take an exclusive lock, or X Lock.

Back in the earlier experiment, the range UPDATE eventually needs to modify records, so it needs exclusive semantics. It is not satisfied with versions visible in the old Read View; it must find the currently modifiable index records and lock them.

Intention Lock

An intention lock is a table-level coordination lock automatically taken by InnoDB. It does not directly lock a row. Instead, it places a marker at the table level:

  1. This transaction intends to take S/X locks on some rows in this table.

There are two common types:

  1. IS: Intention Shared Lock
  2. IX: Intention Exclusive Lock

For example:

  1. SELECT *
  2. FROM user
  3. WHERE id = 1
  4. LOCK IN SHARE MODE;

Can be roughly understood as:

  1. Take IS on the table;
  2. take S lock on the index record id = 1.

And this statement:

  1. SELECT *
  2. FROM user
  3. WHERE id = 1
  4. FOR UPDATE;

Or:

  1. UPDATE user
  2. SET name = 'Alice'
  3. WHERE id = 1;

Can be roughly understood as:

  1. Take IX on the table;
  2. take X lock on the index record id = 1.

Why do we need this table-level marker?

Suppose there were no intention locks, and another transaction wanted to lock the whole table:

  1. LOCK TABLES user WRITE;

MySQL/InnoDB would have to check whether any row in the user table is already locked by another transaction. For a large table, that check would be expensive.

With intention locks, the decision is much cheaper:

  1. If the table already has IX,
  2. then some rows in the table are being, or will be, exclusively locked;
  3. therefore another transaction cannot simply take a whole-table write lock.

The easiest misunderstanding about intention locks is that multiple transactions holding IX do not conflict with each other.

For example, two transactions can update different rows:

  1. -- Session A
  2. UPDATE user SET name = 'Alice' WHERE id = 1;
  4. -- Session B
  5. UPDATE user SET name = 'Bob' WHERE id = 2;

Both can hold IX on the user table. Whether they truly conflict depends on whether the later row locks land on the same index record or the same range.

A rough compatibility matrix looks like this:

Existing / Requested IS IX S table lock X table lock
IS Compatible Compatible Compatible Conflict
IX Compatible Compatible Conflict Conflict
S table lock Compatible Conflict Compatible Conflict
X table lock Conflict Conflict Conflict Conflict

So the point of intention locks is not “locking the whole table so others cannot access it”. The point is:

  1. Use a table-level marker to let table locks and row locks quickly determine whether they can coexist.

Insert Intention Lock

Insert intention locks are related to gaps.

Assume the index contains:

  1. 10, 20

Two transactions want to insert:

  1. 15
  2. 16

Both values are in the (10, 20) gap, but their insert positions are different, so they usually do not need to fully block each other.

But if another transaction has already locked this range with a current read:

  1. SELECT *
  2. FROM user
  3. WHERE id > 10 AND id < 20
  4. FOR UPDATE;

Then transactions trying to insert 15 or 16 may be blocked.

So an insert intention lock does not mean “I want to lock the whole table”. It means:

  1. I want to insert a record at a specific position inside a gap.

Its relationship with gap locks explains why two inserts can sometimes run concurrently, but may get stuck as soon as they hit a range current read.

Auto-Inc Lock

Auto-Inc Lock is related to auto-increment primary keys:

  1. INSERT INTO orders(user_id) VALUES (1);

If a table has an AUTO_INCREMENT column, InnoDB has to allocate auto-increment values safely. The granularity of the auto-increment lock varies by MySQL configuration and statement type, but the goal is always to allocate auto-increment values correctly.

This lock is also a table-level coordination mechanism. It is not on the same dimension as record locks and gap locks: record locks and gap locks care about index records and gaps; Auto-Inc Lock cares about auto-increment value allocation for the table.

12. Separate Table Locks, MDL, and Server-Layer Locks

When troubleshooting locks, there is another trap: not every MySQL lock is an InnoDB row lock, and not every case that “looks like the whole table is locked” is really a table-level lock.

Put common cases side by side:

Scenario Layer Table-level? Typical statement
MDL, Metadata Lock MySQL Server Yes SELECT, UPDATE, ALTER TABLE
Explicit table lock MySQL Server / engine Yes LOCK TABLES user WRITE
Storage engine only supports table locks Storage engine Yes Writes on MyISAM tables
InnoDB intention lock InnoDB Yes, but only as a coordination marker Automatically takes IS / IX before row locks
InnoDB Auto-Inc Lock InnoDB Yes Inserts into tables with AUTO_INCREMENT
Global read lock MySQL Server Global FLUSH TABLES WITH READ LOCK
Named lock MySQL Server User-level GET_LOCK()

MDL: The Most Common Source of “The Table Is Stuck”

MDL means Metadata Lock. It belongs to the MySQL Server layer. It protects table metadata, such as columns, indexes, and whether the table exists, rather than a specific row.

Its purpose is direct:

  1. When someone is reading or writing a table, do not let someone else change the table structure at the same time.

For example, Session A starts a transaction and queries user:

  1. -- Session A
  2. BEGIN;
  4. SELECT *
  5. FROM user
  6. WHERE id = 1;

At this point, there will be a shared MDL on the user table. As long as Session A’s transaction has not ended, Session B has to wait if it wants to change the table structure:

  1. -- Session B
  2. ALTER TABLE user ADD COLUMN nickname VARCHAR(50);

What is even more confusing is Session C:

  1. -- Session C
  2. SELECT *
  3. FROM user
  4. WHERE id = 2;

It is only an ordinary query, but it may also get stuck. The reason is that Session B’s ALTER TABLE is already waiting for an exclusive MDL, and later new queries may line up behind it:

  1. Session A holds a shared MDL and has not committed;
  2. Session B waits for an exclusive MDL to run ALTER;
  3. Session C wants a shared MDL, but it is queued behind B and is blocked too.

This is a common production pattern: a long transaction is not committed, then a DDL gets stuck, and then many ordinary queries pile up behind it.

A rough rule of thumb:

Statement Common MDL semantics
SELECT / INSERT / UPDATE / DELETE Shared MDL
ALTER TABLE / DROP TABLE / TRUNCATE TABLE / RENAME TABLE Exclusive MDL

In an explicit transaction, MDL related to DML is usually held until the transaction ends, meaning COMMIT or ROLLBACK. So a long transaction can affect not only row locks, but also DDL.

Explicit Table Locks and Engine Table Locks

An explicit table lock is when you directly tell MySQL to lock a table:

  1. LOCK TABLES user WRITE;

This kind of lock is relatively uncommon in ordinary business code today. It is more often found in maintenance scripts, import/export workflows, and compatibility logic in older systems.

Another case is when the storage engine itself mainly uses table-level locks. For example, MyISAM writes are usually table-level locks, unlike InnoDB, which prefers row locks.

This is why discussions about MySQL locks must first specify the storage engine. This article discusses InnoDB throughout; with MyISAM, many conclusions are different.

Global Read Lock and Named Lock

FLUSH TABLES WITH READ LOCK is a global read lock:

  1. FLUSH TABLES WITH READ LOCK;

It is not an ordinary table lock on a specific table. It is a global Server-layer lock, commonly used in backup scenarios.

GET_LOCK() is a user-level named lock:

  1. SELECT GET_LOCK('job:daily-report', 10);

It is also not an InnoDB row lock. It is a named mutual-exclusion mechanism provided by MySQL Server.

Does an UPDATE Without an Index Become a Table Lock?

Here is another common misconception: if an InnoDB UPDATE condition does not use an index, does it degrade into a table-level lock?

More precisely, usually no.

For example:

  1. UPDATE user
  2. SET status = 1
  3. WHERE email = '[email protected]';

If email has no index, InnoDB may need to scan many records and lock the records it encounters during the scan. This can look like “the whole table is locked”, but in implementation it is usually still many index record locks or range locks, not a simple table-level X lock.

So next time someone says “MySQL locked the table”, it is better to split the question into:

  1. Is this Server-layer MDL, or an InnoDB-layer lock?
  2. Is it explicit LOCK TABLES, or an intention lock automatically taken by InnoDB?
  3. Is it a table-level lock, or many row locks that only look like a table lock?
  4. Is the issue about ordinary snapshot reads, or current-read locking?

13. MySQL 5.7.44 Source Location Reference

The following table can serve as a roadmap for reading the MySQL 5.7.44 source code. All paths are relative to the MySQL source root.

Concept in the article Source location What to look at
ReadView class storage/innobase/include/read0types.h:48 The core Read View class in 5.7
Version visibility check storage/innobase/include/read0types.h:169 How ReadView::changes_visible() decides whether a transaction version is visible
ReadView preparation storage/innobase/read/read0read.cc:453 ReadView::prepare() sets boundaries and the active transaction list
MVCC opens ReadView storage/innobase/read/read0read.cc:554 MVCC::view_open() allocates or reuses a ReadView
RR first snapshot read creates ReadView storage/innobase/trx/trx0trx.cc:2271 The comment on trx_assign_read_view() says it is created on the first consistent read
Ordinary SELECT consistent read storage/innobase/row/row0sel.cc:5113 When select_lock_type == LOCK_NONE, it performs a snapshot read and assigns a ReadView
SELECT ... FOR UPDATE parsing sql/sql_yacc.yy:9239 FOR UPDATE is parsed as TL_WRITE
Locking read passed down to tables sql/sql_parse.cc:6238 set_lock_for_tables() sets table lock type and MDL type
InnoDB current-read lock type storage/innobase/include/row0mysql.h:799 select_lock_type means LOCK_NONE, LOCK_S, or LOCK_X
InnoDB handler lock entry points storage/innobase/handler/ha_innodb.cc:15636, storage/innobase/handler/ha_innodb.cc:16514 external_lock() and store_lock() connect Server-layer table lock semantics to InnoDB
InnoDB basic lock modes storage/innobase/include/lock0types.h:46 LOCK_IS, LOCK_IX, LOCK_S, LOCK_X, LOCK_AUTO_INC
InnoDB lock object representation storage/innobase/include/lock0priv.h:136 type_mode ORs together lock type, lock mode, gap/record flags
Table-lock and record-lock type bits storage/innobase/include/lock0lock.h:949 LOCK_TABLE and LOCK_REC
next-key / gap / record-only storage/innobase/include/lock0lock.h:962 LOCK_ORDINARY represents next-key semantics; LOCK_GAP and LOCK_REC_NOT_GAP represent gap and record-only
Actual record locking storage/innobase/lock/lock0lock.cc:6292, storage/innobase/lock/lock0lock.cc:6371 Secondary index and clustered index locking checks
Row scan chooses gap/next-key storage/innobase/row/row0sel.cc:1232 sel_set_rec_lock() receives LOCK_ORDINARY, LOCK_GAP, or LOCK_REC_NOT_GAP
InnoDB intention lock storage/innobase/row/row0sel.cc:5123, storage/innobase/lock/lock0lock.cc:3996 Locking reads first take LOCK_IS or LOCK_IX on the table
Insert intention lock storage/innobase/include/lock0lock.h:980, storage/innobase/lock/lock0lock.cc:5975 LOCK_INSERT_INTENTION is used when an insert waits on a gap
AUTO-INC lock storage/innobase/row/row0mysql.cc:1237, storage/innobase/handler/ha_innodb.cc:7388 Table-level mutex-like lock for the auto-increment counter
Server-layer table lock sql/lock.cc:315, mysys/thr_lock.c:976 mysql_lock_tables() eventually calls thr_multi_lock()
Explicit LOCK TABLES sql/sql_parse.cc:2269, sql/sql_base.cc:6741 Parsing enters lock_tables()
MyISAM table lock storage/myisam/ha_myisam.cc:1956, storage/myisam/mi_locking.c:34 MyISAM table locks ultimately go through mi_lock_database()
MDL metadata lock sql/mdl.h:159, sql/mdl.cc:3562, sql/sql_base.cc:2790 MDL type definition, lock acquisition, and MDL acquisition when opening tables
FLUSH TABLES WITH READ LOCK sql/lock.cc:1060, sql/lock.cc:1126, sql/lock.cc:1210 Global read lock is implemented with MDL and blocks updates and COMMIT in two steps
GET_LOCK() user-level named lock sql/item_func.cc:5303, sql/item_func.cc:5510 User_level_lock and Item_func_get_lock::val_int()

The source names also reveal the detail discussed earlier: next-key lock is more of a behavioral semantic. In 5.7, the real lock object is LOCK_REC with different precise modes. LOCK_ORDINARY represents ordinary next-key behavior, LOCK_GAP represents gap-only locking, and LOCK_REC_NOT_GAP represents record-only locking.

Summary

The easiest misread in this production anomaly is that we instinctively treat the earlier ordinary SELECT and the later UPDATE as the same kind of read.

But under InnoDB RR, they are not the same:

  1. Ordinary SELECT: snapshot read, reuses Read View;
  2. UPDATE / DELETE / SELECT ... FOR UPDATE: current read, works on the latest currently operable records.

So in the experiment from section 1, the fact that Session A’s earlier ordinary SELECT saw 8 rows does not mean the later range UPDATE can only update those 8 rows. The 9th row committed by Session B is invisible to the old Read View, but it can still be matched and updated by the later current read.

These troubleshooting conclusions are worth remembering:

  • First identify whether the statement is a snapshot read or a current read.
  • Under RR, the Read View is created by the first snapshot read by default, not by BEGIN.
  • Ordinary SELECT sees historical versions visible under the Read View.
  • UPDATE / DELETE / SELECT ... FOR UPDATE sees the latest currently operable versions.
  • If you want to prevent others from inserting into a range, you need current reads plus range-lock semantics.
  • Gap locks lock gaps; record locks lock existing index records.
  • Next-key lock is the lock semantic of “record + preceding gap”; it is not necessarily an independent physical lock object.
  • InnoDB intention locks are table-level coordination markers used to coordinate table locks and row locks; they do not directly lock a row.
  • MDL belongs to the MySQL Server layer, and long transactions may use MDL to block DDL and even later queries.
  • An InnoDB UPDATE that does not use an index may look like it locked the whole table, but it is usually more accurate to say it scanned and locked many records.

So, “Can RR solve phantom reads?” is not a simple yes or no. A more precise answer is:

Under RR, ordinary snapshot reads use MVCC to maintain a consistent snapshot; current reads rely on InnoDB’s range-lock semantics to prevent new operable records from appearing inside a range.