Alerting checklist
Six numbers cover most transaction incidents on this site. Every one is queryable with plain SQL, every mechanism behind them is proven by a scenario in the chapters above.
| # | Alert | Query source | Backed by |
|---|---|---|---|
| 1 | Oldest transaction age > minutes | min(trx_started) in information_schema.innodb_trx | find-long-transactions |
| 2 | Idle-in-transaction sessions > seconds | innodb_trx ⋈ processlist command = 'Sleep' | find-long-transactions |
| 3 | Lock waits now | rows in sys.innodb_lock_waits | who-is-blocking-whom |
| 4 | Deadlock rate | Δ lock_deadlocks (INNODB_METRICS) | deadlock-counter |
| 5 | Lock-timeout rate | Δ lock_timeouts (INNODB_METRICS) | lock timeouts |
| 6 | Purge backlog sustained | trx_rseg_history_len (INNODB_METRICS) | history-list-health |
Reading the board:
- 1, 2, and 6 firing together is one forgotten transaction: alert 2's join names it, so kill it or fix the code path and the other alerts drain on their own.
- 3 spiking while 1 stays quiet is hot-row contention, not a stuck session — look at lock queues and whether a SKIP LOCKED or atomic-update shape fits.
- 4 climbing steadily points at lock ordering first (deadlock avoidance), then gap locks if the statements involve ranges or inserts, and every consumer wants the retry loop.
- 5 without 4 means the waits are long but acyclic — usually one slow writer everyone queues behind, and alert 3's view names it.
What's deliberately not here: anomaly detection for lost updates and write skew. No counter sees them — they look like successful transactions. They're prevented by code patterns, caught by application-level invariant checks, and that's precisely why the patterns chapter exists.