Logs and counters

For MySQL 8.4 InnoDB, distinguish a measured counter delta from a retained incident log. The following schedule checks deadlock detection and metric enablement, raises 1213, and asserts a server-wide lock_deadlocks increase of at least one.

Deadlocks: a counter delta, not a permanent log

The counter is useful within an enabled collection interval, with restart and reset handled by your monitoring system.

A

B

debit id=1

debit id=2

credit id=2→ ⏳ waits

credit id=1← 1213 Deadlock found when trying to get lock

⏵ credit id=2→ completes

M> SELECT @@innodb_deadlock_detect AS detection;
 detection 
-----------
         1 
(1 row)

M> SELECT status FROM information_schema.INNODB_METRICS WHERE name = 'lock_deadlocks';
 status  
---------
 enabled 
(1 row)

The same two opposite-order transfers as chapter 3 — one will be the victim.

A> BEGIN;
Query OK

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

B> BEGIN;
Query OK

B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
Query OK, 1 row affected

A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
⏳ A is waiting for a lock…

B> UPDATE accounts SET balance = balance + 25 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

⏵ A resumes:
Query OK, 1 row affected

A> COMMIT;
Query OK

The detected deadlock is also visible as a counter delta in this run:

M> SELECT im.count - b.n >= 1 AS deadlocks_since_snapshot
   FROM information_schema.INNODB_METRICS im, deadlocks_before b
   WHERE im.name = 'lock_deadlocks'
    -- sample rates within one enabled interval; account for restart and reset
 deadlocks_since_snapshot 
--------------------------
                        1 
(1 row)

Documented separately: SHOW ENGINE INNODB STATUS reports the latest detected deadlock; innodb_print_all_deadlocks enables error-log reports. This scenario does not inspect either log output or retention.

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

The counters worth graphing

The MySQL metrics manual states: "Counter status is not persistent." Check available names, STATUS, enablement, and collection/reset times. Rates require successive samples in a comparable interval.

Counter	Interpretation	Evidence boundary
lock_deadlocks	Detected deadlocks during collection	The schedule above asserts a delta, not log retention
lock_timeouts	InnoDB row-lock timeouts during collection	Chapter 3 asserts 1205, not this counter's delta
trx_rseg_history_len	History-list length, not bytes or a diagnosis	History list health asserts a lower bound
lock_row_lock_waits / lock_row_lock_time	Row-lock wait count / accumulated time	Documented metric names; lock queues demonstrate waits, not these counters

The logs worth having

The deadlock manual documents SHOW ENGINE INNODB STATUS for the latest deadlock and innodb_print_all_deadlocks for error-log reports. These are Documented contracts; this scenario does not inspect log destinations, permissions, or retention. Use retained reports to investigate actual lock order before selecting a repair.

The slow-query-log manual states: "By default, the slow query log is disabled." Its inclusion filters and output destination affect what you capture. High query time with few examined rows is a clue, not proof of a blocker or proof that an index is unnecessary. Confirm live waits through the lock views. No slow-log record is asserted here.

Lost updates and write skew can commit without an error in the anomaly schedules. These concurrency counters do not validate application invariants. Check those invariants and participating writers as well.

Further reading

• MySQL docs: InnoDB INFORMATION_SCHEMA Metrics Table
• MySQL docs: The Slow Query Log
• The same lesson on PostgreSQL