How to See the Locks a MySQL Transaction Holds

When a lock problem makes no sense, stop guessing and look at the locks. performance_schema.data_locks lists each one InnoDB holds. Run the same UPDATE three ways, by primary key, by unique index and by a plain index, and you get three different sets, which explains most of the surprises people have with row locks.

Prerequisites

• MySQL 8.0 or newer, and an account that may read performance_schema. The examples use root on MySQL 9.7.4 in Docker. How to Let a MySQL User See Locks and Transactions sets up a narrower account.
• Two sessions: one holds a transaction open, and the other looks.

By Primary Key: One Row

mysql: terminal 1

START TRANSACTION;
UPDATE products SET ReorderLevel = 6 WHERE Id = 3;

mysql: root session

SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks;

Output

+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| products    | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| products    | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 3         |
+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.000 sec)

Row	What it means
TABLE, IX	An intention lock on the table: "this transaction will lock rows in here". It blocks almost nothing
RECORD, X,REC_NOT_GAP	An exclusive lock on the row in LOCK_DATA, here 3, and not on the gap beside it

That's as small as a lock gets. End it with ROLLBACK.

By Unique Index: Two Entries

mysql: terminal 1

START TRANSACTION;
UPDATE products SET ReorderLevel = 6 WHERE Sku = 'CBL-HDMI-2M';

Output (the same query)

+-------------+-----------------+-----------+---------------+-------------+------------------+
| OBJECT_NAME | INDEX_NAME      | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA        |
+-------------+-----------------+-----------+---------------+-------------+------------------+
| products    | NULL            | TABLE     | IX            | GRANTED     | NULL             |
| products    | IX_products_Sku | RECORD    | X,REC_NOT_GAP | GRANTED     | 'CBL-HDMI-2M', 3 |
| products    | PRIMARY         | RECORD    | X,REC_NOT_GAP | GRANTED     | 3                |
+-------------+-----------------+-----------+---------------+-------------+------------------+
3 rows in set (0.002 sec)

MySQL finds the row through IX_products_Sku, and then changes it in PRIMARY, so it locks an entry in each. It's still one row.

By a Plain Index: The Row and the Gaps

CategoryId has an index, and more than one product can have the same category:

mysql: terminal 1

START TRANSACTION;
UPDATE products SET ReorderLevel = 6 WHERE CategoryId = 2;

Output (the same query)

+-------------+------------------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME             | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-------------+------------------------+-----------+---------------+-------------+-----------+
| products    | NULL                   | TABLE     | IX            | GRANTED     | NULL      |
| products    | PRIMARY                | RECORD    | X,REC_NOT_GAP | GRANTED     | 5         |
| products    | PRIMARY                | RECORD    | X,REC_NOT_GAP | GRANTED     | 6         |
| products    | IX_products_CategoryId | RECORD    | X,GAP         | GRANTED     | 3, 7      |
| products    | IX_products_CategoryId | RECORD    | X             | GRANTED     | 2, 5      |
| products    | IX_products_CategoryId | RECORD    | X             | GRANTED     | 2, 6      |
+-------------+------------------------+-----------+---------------+-------------+-----------+
6 rows in set (0.002 sec)

Here LOCK_DATA is 2, 5: the index value, then the primary key of the row.

Lock mode	What it covers
X,REC_NOT_GAP	The row only
X	The row, and the gap before it. This is a next-key lock
X,GAP	Only the gap before this entry, so here it means "no new category 2 rows after the last one"
S, S,REC_NOT_GAP	The same, shared
X,INSERT_INTENTION	A session waiting to insert into a gap

The gap locks are what keep another transaction from adding a new product in category 2 until this one ends, and that's how REPEATABLE READ stops new rows appearing. How Gap Locks Block an INSERT in MySQL shows one in action.

When a Lock Is Waiting

LOCK_STATUS is GRANTED for a lock that's held, and WAITING for one that's wanted. To see a second session waiting, use the three-terminal example: one line says GRANTED and one says WAITING, on the same LOCK_DATA. performance_schema.data_lock_waits links the two: the page says it "implements a many-to-many relationship showing which data lock requests in the data_locks table are blocked by which held data locks".

The table empties when the transactions end:

mysql: root session

SELECT COUNT(*) FROM performance_schema.data_locks;

Output

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.001 sec)

Common Mistakes

Mistake	What happens	Do this instead
Reading it after the transaction ended	The table is empty	Keep the transaction open while you look
Assuming an UPDATE locks one row	It locks what it searches through	Look at data_locks, and index the WHERE
Treating IX as a table lock that blocks everyone	It only signals intent	Look at the RECORD rows

Troubleshooting

Symptom	Likely cause	Fix
SELECT command denied	The account can't read performance_schema	The fix
A lock on supremum pseudo-record	A gap lock after the last row	How Gap Locks Block an INSERT
Many more locks than rows changed	No suitable index	Why a MySQL UPDATE Without an Index Locks Every Row

FAQ

Does a plain SELECT show up here?

No. A plain SELECT takes no row locks under the default isolation level. FOR UPDATE and FOR SHARE do.

Does this slow the server down?

The page says there's nothing to turn on: "Unlike most Performance Schema data collection, there are no instruments for controlling whether data lock information is collected". It's gathered when you ask, so query it when you need it.

Conclusion

performance_schema.data_locks shows each lock, its mode and its row. X,REC_NOT_GAP is one row, X is a row and the gap before it, and X,GAP is only the gap. If the list is longer than you expected, the WHERE is searching through more than you think.

Additional Resources

• The data_locks Table — every column.
• InnoDB Locking — record, gap and next-key locks.

Related

• How to See Which MySQL Query Is Blocking Another
• How Gap Locks Block an INSERT in MySQL
• Why a MySQL UPDATE Without an Index Locks Every Row