Monitoring locks

MySQL 8.4 exposes InnoDB data locks and metadata locks through different Performance Schema tables. Monitoring requires sufficient privileges and, for metadata events, enabled instrumentation.

What one UPDATE really holds, and spotting the waiter

This existing-row PRIMARY-key UPDATE holds a table IX lock and an X,REC_NOT_GAP record lock. The transcript asserts these rows, B's WAITING request, and its blocker. It does not establish a two-lock count for every UPDATE: other access paths can lock more index records, gaps, and clustered records.

A> BEGIN;
Query OK

A> UPDATE accounts SET balance = 200 WHERE id = 1;
Query OK, 1 row affected

This existing PRIMARY-key UPDATE shows a table IX lock and an exclusive record lock.

M> SELECT object_name, index_name, lock_type, lock_mode, lock_status, lock_data
   FROM performance_schema.data_locks
   WHERE object_schema = DATABASE() AND object_name = 'accounts'
   ORDER BY lock_type DESC;
 object_name | index_name | lock_type |   lock_mode   | lock_status | lock_data 
-------------+------------+-----------+---------------+-------------+-----------
 accounts    |            | TABLE     | IX            | GRANTED     |           
 accounts    | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     |         1 
(2 rows)

B> UPDATE accounts SET balance = 300 WHERE id = 1;
⏳ B is waiting for a lock…

The waiter's tell: a record-lock request with lock_status = WAITING.

M> SELECT object_name, lock_mode, lock_status, lock_data
   FROM performance_schema.data_locks
   WHERE object_schema = DATABASE() AND object_name = 'accounts' AND lock_status = 'WAITING';
 object_name |   lock_mode   | lock_status | lock_data 
-------------+---------------+-------------+-----------
 accounts    | X,REC_NOT_GAP | WAITING     |         1 
(1 row)

You rarely need to decode data_locks by hand — sys.innodb_lock_waits names the culprit.

M> SELECT waiting_pid, blocking_pid FROM sys.innodb_lock_waits
   WHERE locked_table_schema = DATABASE() AND locked_table_name = 'accounts';
 waiting_pid | blocking_pid 
-------------+--------------
 pid(B)      | pid(A)       
(1 row)

A> COMMIT;
Query OK

⏵ B resumes:
Query OK, 1 row affected

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

The production cheat sheet

These are documented diagnostic interfaces; the transcript exercises the first two.

-- InnoDB waiters and blockers, with available query information:
SELECT * FROM sys.innodb_lock_waits;

-- Data locks held or requested, not metadata locks:
SELECT * FROM performance_schema.data_locks;

-- Metadata locks, including pending requests (requires instrumentation):
SELECT * FROM performance_schema.metadata_locks;

-- Sessions reporting a metadata-lock wait:
SELECT * FROM performance_schema.processlist
WHERE state = 'Waiting for table metadata lock';

The data_locks contract and sys view contract describe data-lock requests and their blockers. MDL has its own metadata_locks table; processlist is not its only diagnostic interface.

Monitoring is not an atomic picture

The transaction-information consistency manual provides transaction and wait diagnostics. Different queries can observe different moments. Do not infer a permanently unchanged transaction from a repeated monitoring result.

If an operator chooses cancellation, the KILL manual distinguishes KILL QUERY <processlist_id> (current statement) from KILL <processlist_id> (connection). Canceling a statement does not by itself end an open transaction or release all of its earlier locks. These operations are not executed by this lesson's scenario. Next: MVCC and the undo log.

Further reading

• The PostgreSQL counterpart

The reconstructed support assessment records the exact manual support and execution limits for this lesson.