The team resolves a production incident using everything they know

SHOW FULL PROCESSLIST;

-- Priya sees 80 connections in state "Waiting for lock"
-- All waiting for the same table: orders
-- One query has been running for 145 seconds

SELECT * FROM information_schema.INNODB_TRX;
-- Shows a transaction open for 148 seconds: updating orders

-- Find the blocking thread:
SELECT r.trx_id waiting_trx,
       r.trx_query waiting_query,
       b.trx_id blocking_trx,
       b.trx_query blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

KILL 4521;  -- kill the blocking transaction

-- Verify the backlog clears:
SHOW PROCESSLIST;  -- connections dropping from 80 to normal

-- What was that long-running query?
-- Check the binary log around 11am:
mysqlbinlog --start-datetime="2026-03-16 10:55:00"                 --stop-datetime="2026-03-16 11:05:00"                 /var/log/mysql/mysql-bin.000245 | head -100

-- Found: a data migration script that started a transaction,
-- updated 2 million rows, but forgot to COMMIT

-- Check that order counts are correct:
SELECT status, COUNT(*) FROM orders GROUP BY status;

-- Compare with expected from the app logs

-- Check that transactions from the blocked period completed or rolled back:
SELECT COUNT(*) FROM orders WHERE ordered_at BETWEEN '11:00:00' AND '11:30:00';

-- Add max transaction time monitoring:
SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30;
-- Run this every 60 seconds via cron and alert if any transaction is over 30 seconds old

-- Set transaction timeout:
SET GLOBAL innodb_lock_wait_timeout = 30;  -- queries wait max 30 seconds for a lock

-- Add to my.cnf for permanence:
innodb_lock_wait_timeout = 30

-- For bulk data operations: do them in small batches
-- Instead of UPDATE orders SET ... WHERE status = 'paid' (2 million rows in one transaction):
-- Process 1000 rows at a time:

SET @done = 0;
REPEAT
    UPDATE orders SET migrated = 1
    WHERE migrated = 0 AND status = 'paid'
    LIMIT 1000;
    SET @done = ROW_COUNT();
    SELECT SLEEP(0.1);  -- brief pause between batches
UNTIL @done = 0 END REPEAT;

1. SHOW FULL PROCESSLIST                           -- what is happening now
2. SHOW ENGINE INNODB STATUS                       -- locks and transactions
3. sudo tail -100 /var/log/mysql/error.log         -- server errors
4. EXPLAIN query                                   -- query execution plan
5. SHOW SLAVE STATUS                               -- replication health
6. SELECT ... FROM performance_schema...           -- historical stats
7. mysqlbinlog ...                                 -- binary log for data recovery

Common incident → First check:
App cannot connect        → max_connections, disk space, MySQL running
Queries are slow          → PROCESSLIST, EXPLAIN, slow query log
Locks and waits           → INNODB_TRX, INNODB_LOCK_WAITS, kill blocking thread
Data was accidentally deleted → binary log, last backup
Replication broken        → SHOW SLAVE STATUS, Last_Error
MySQL won't start         → error log, disk space, port conflict