TL;DR: When row lock waits hit Aurora/RDS MySQL, you often miss the culprit by the time you log in. This post shows how to automatically capture lock/blocking forensics every few seconds—including the exact blocking SQL—so you can diagnose incidents after the fact. We’ll also walk through a practical tuning checklist to reduce contention long‑term.
Who this is for
- Teams running Aurora MySQL 3.x (MySQL 8.0)—Serverless v2 or provisioned—and classic RDS MySQL 8.0.
- You want drop‑in tooling that is safe for production, low‑overhead, and easy to turn on/off.
Using MySQL 5.7 / Aurora 2.x? See the Variants section for a compatible procedure.
What you’ll build
- A
dba.lock_wait_capturestable that records snapshots of waiters, blockers, SQL text on both sides, lock/table/index details, and wait ages. - A scheduled event (default every 5 seconds) that writes a snapshot only when lock waits exist.
- A daily purge job (default 7 days) to keep storage in check.
- Handy queries to find worst offenders and hot tables/indexes.
Prereqs
- Permissions to create objects in a
dbaschema and change Performance Schema settings. sysschema available (default on RDS/Aurora). If not, use the fallback that queriesperformance_schemadirectly.- Event Scheduler enabled on the instance/cluster.
Step 1 — Enable lightweight instrumentation
What: Turn on just enough Performance Schema (P_S) consumers and lock instruments to capture the SQL text and the lock-wait relationships.
Why: By default, MySQL may not retain statement history or time lock waits. Enabling these lets us correlate who waited on whom with the exact SQL.
How: These are dynamic toggles (no restart). If your environment resets P_S on restart/patching, re-run this snippet at startup.
These consumers/instruments are safe for production in typical OLTP workloads and are dynamic (no restart).
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Statement histories help us pull SQL text UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME IN ('events_statements_history','events_statements_history_long', 'global_instrumentation','thread_instrumentation'); -- Make sure lock waits are tracked and timed UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE 'wait/lock/innodb/%' OR NAME LIKE 'wait/lock/metadata/sql/mdl'; |
Tip: Re‑run the snippet after maintenance windows if your environment resets P_S settings.
Step 2 — Create the capture table
What: A durable dba.lock_wait_captures table to store snapshots of waiters, blockers, lock metadata, and SQL text.
Why: Incidents are transient; persisting the state every few seconds lets you investigate after the fact.
How: One InnoDB table with a composite PK (captured_at, waiting_thread_id). Retention is handled by a scheduled purge in Step 4.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE DATABASE IF NOT EXISTS dba; CREATE TABLE IF NOT EXISTS dba.lock_wait_captures ( captured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, waiting_thread_id BIGINT, waiting_trx_id VARCHAR(100), waiting_account VARCHAR(128), waiting_host VARCHAR(255), waiting_schema VARCHAR(128), waiting_sql LONGTEXT, waiting_age_s INT, lock_table VARCHAR(512), lock_index VARCHAR(128), lock_type VARCHAR(64), lock_mode VARCHAR(64), lock_data TEXT, blocking_thread_id BIGINT, blocking_trx_id VARCHAR(100), blocking_account VARCHAR(128), blocking_host VARCHAR(255), blocking_schema VARCHAR(128), blocking_sql LONGTEXT, blocking_age_s INT, PRIMARY KEY (captured_at, waiting_thread_id) ) ENGINE=InnoDB; |
Step 3 — Procedure to snapshot current lock waits (MySQL 8.0+/Aurora 3.x)
What: A stored procedure dba.capture_locks() that inserts a single snapshot row-set for each sampling tick.
Why: Centralizes the logic and allows the scheduler to call a single routine. We only write when waits exist to keep overhead and table size low.
How: Query sys.innodb_lock_waits (a curated view over P_S + InnoDB internals) and insert the results into dba.lock_wait_captures with the current timestamp.
Preferred approach: use sys.innodb_lock_waits to keep the joins simple.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
DELIMITER // DROP PROCEDURE IF EXISTS dba.capture_locks// CREATE PROCEDURE dba.capture_locks() BEGIN IF EXISTS (SELECT 1 FROM sys.innodb_lock_waits) THEN INSERT INTO dba.lock_wait_captures ( captured_at, waiting_thread_id, waiting_trx_id, waiting_account, waiting_host, waiting_schema, waiting_sql, waiting_age_s, lock_table, lock_index, lock_type, lock_mode, lock_data, blocking_thread_id, blocking_trx_id, blocking_account, blocking_host, blocking_schema, blocking_sql, blocking_age_s ) SELECT NOW(), ilw.trx_waiting_thread_id, ilw.trx_waiting, ilw.waiting_account, ilw.waiting_host, ilw.waiting_schema, ilw.waiting_query, ilw.wait_age, ilw.lock_table, ilw.lock_index, ilw.lock_type, ilw.lock_mode, ilw.lock_data, ilw.trx_blocking_thread_id, ilw.trx_blocking, ilw.blocking_account, ilw.blocking_host, ilw.blocking_schema, ilw.blocking_query, ilw.block_age FROM sys.innodb_lock_waits AS ilw; END IF; END// DELIMITER ; |
Fallback (same engines) if sys isn’t available
What/Why: Some builds omit the sys schema. This variant joins performance_schema.data_lock_waits and friends directly to produce equivalent output.
How: The join pulls waiting/blocking transactions, lock metadata, and the most recent SQL text from events_statements_current.
Use performance_schema.data_lock_waits with companion tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
DELIMITER // DROP PROCEDURE IF EXISTS dba.capture_locks// CREATE PROCEDURE dba.capture_locks() BEGIN IF EXISTS (SELECT 1 FROM performance_schema.data_lock_waits) THEN INSERT INTO dba.lock_wait_captures ( captured_at, waiting_thread_id, waiting_trx_id, waiting_account, waiting_host, waiting_schema, waiting_sql, waiting_age_s, lock_table, lock_index, lock_type, lock_mode, lock_data, blocking_thread_id, blocking_trx_id, blocking_account, blocking_host, blocking_schema, blocking_sql, blocking_age_s ) SELECT NOW(), r.trx_mysql_thread_id, r.trx_id, pw.USER, pw.HOST, pw.DB, esw.SQL_TEXT, TIMESTAMPDIFF(SECOND, r.trx_started, NOW()), CONCAT(dl.OBJECT_SCHEMA,'.',dl.OBJECT_NAME), dl.INDEX_NAME, dl.LOCK_TYPE, dl.LOCK_MODE, dl.LOCK_DATA, b.trx_mysql_thread_id, b.trx_id, pb.USER, pb.HOST, pb.DB, esb.SQL_TEXT, TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) FROM performance_schema.data_lock_waits w JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id JOIN performance_schema.data_locks dl ON dl.engine_lock_id = w.requesting_engine_lock_id LEFT JOIN performance_schema.threads tr ON tr.PROCESSLIST_ID = r.trx_mysql_thread_id LEFT JOIN performance_schema.events_statements_current esw ON esw.THREAD_ID = tr.THREAD_ID LEFT JOIN information_schema.PROCESSLIST pw ON pw.ID = r.trx_mysql_thread_id LEFT JOIN performance_schema.threads tb ON tb.PROCESSLIST_ID = b.trx_mysql_thread_id LEFT JOIN performance_schema.events_statements_current esb ON esb.THREAD_ID = tb.THREAD_ID LEFT JOIN information_schema.PROCESSLIST pb ON pb.ID = b.trx_mysql_thread_id; END IF; END// DELIMITER ; |
Using MySQL 5.7/Aurora 2.x? Replace Step 3 with the 5.7 variant that queries
information_schema.innodb_*tables.
Step 4 — Schedule automatic snapshots & a daily purge
What: Two MySQL Events—one to take snapshots every few seconds and one to purge old rows daily.
Why: Automation ensures coverage during off-hours. Purge keeps storage bounded.
How: Enable event_scheduler and create events in the dba schema. On RDS/Aurora, persist event_scheduler=ON in a DB parameter group.
|
1 2 3 4 5 6 7 8 9 10 11 |
SET GLOBAL event_scheduler = ON; -- persist via DB parameter group in RDS/Aurora CREATE EVENT IF NOT EXISTS dba.capture_locks_every_5s ON SCHEDULE EVERY 5 SECOND DO CALL dba.capture_locks(); CREATE EVENT IF NOT EXISTS dba.purge_lock_captures_daily ON SCHEDULE EVERY 1 DAY DO DELETE FROM dba.lock_wait_captures WHERE captured_at < NOW() - INTERVAL 7 DAY; |
Step 5 — Read the forensics after an incident
What: A few investigation queries you’ll use 90% of the time.
How to read them:
- Last hour shows the most recent waits so you can align with app/error logs.
- Worst waits surfaces the biggest pain points by wait age.
- Hot tables/indexes identifies data structures with contention.
- Frequent blockers highlights app users/hosts and a sample of the blocking SQL text.
Last hour:
|
1 2 3 4 5 |
SELECT * FROM dba.lock_wait_captures WHERE captured_at >= NOW() - INTERVAL 1 HOUR ORDER BY captured_at DESC; |
Worst waits:
|
1 2 3 4 5 |
SELECT * FROM dba.lock_wait_captures ORDER BY waiting_age_s DESC, captured_at DESC LIMIT 50; |
Hot tables/indexes (24h):
|
1 2 3 4 5 6 |
SELECT lock_table, lock_index, COUNT(*) AS hits FROM dba.lock_wait_captures WHERE captured_at >= NOW() - INTERVAL 1 DAY GROUP BY lock_table, lock_index ORDER BY hits DESC; |
Frequent blockers:
|
1 2 3 4 5 6 7 8 9 |
SELECT blocking_account, blocking_host, LEFT(REPLACE(REPLACE(blocking_sql,'\n',' '),'\t',' '), 160) AS sample_sql, COUNT(*) AS times_blocked FROM dba.lock_wait_captures WHERE captured_at >= NOW() - INTERVAL 1 DAY GROUP BY blocking_account, blocking_host, sample_sql ORDER BY times_blocked DESC LIMIT 20; |
Row‑lock tuning playbook (high impact first)
- Keep transactions tiny: don’t hold a txn across sleeps, API calls, or long loops; autocommit single‑row ops.
- Index for precision: every FK column indexed; make sure UPDATE/DELETE
WHEREclauses are backed by targeted indexes; prefer PK/range‑narrow scans. - Reduce gap locks: consider
transaction_isolation = READ-COMMITTED(session/global) for OLTP; test before rollout. - Lock smarter: avoid range
FOR UPDATE; use point key locks and 8.0’sFOR UPDATE SKIP LOCKED/NOWAITfor worker queues. - Consistent lock order across code paths (A→B everywhere) to kill deadlocks.
- Batch writes: commit in small chunks (e.g., 500 rows); shard hot counters/queues.
- Auto‑increment: ensure
innodb_autoinc_lock_mode = 2(default in 8.0). - DDL hygiene: schedule off‑hours; set
lock_wait_timeoutfor DDL sessions. - Observe:
innodb_print_all_deadlocks=ONto error log; use Performance Insights “Waits” for time‑slice context.
Aurora/RDS specifics
- Serverless v2: Raising min ACUs won’t fix logical conflicts but can reduce CPU latency, shortening lock hold times. Offload heavy reads to reader endpoints.
- Parameter changes (isolation level, timeouts) belong in a DB parameter group; most are dynamic in 8.0.
- Logs: Deadlock traces stream to the MySQL error log (CloudWatch Logs on RDS/Aurora).
Variants
MySQL 5.7 / Aurora MySQL 2.x
Use the 5.7 procedure that reads from information_schema.innodb_lock_waits, innodb_trx, and innodb_locks, with PROCESSLIST joins for SQL text. (We include the full 5.7 version in the GitHub Gist linked below.)
Troubleshooting & safety
- No rows captured? Ensure
event_scheduler=ON, events are ENABLED, and lock waits actually occurred. - SQL text is NULL? Make sure
events_statements_history[_long]andthread_instrumentationconsumers are ON. - Overhead concerns? Increase interval to 10–15s, or capture only during known windows.
- Persistence: Put
event_scheduler=ONin a DB parameter group; re-run instrumentation after restarts if needed.
Copy‑paste checklist
Goal: Get from zero to useful captures in minutes.
1) One-time setup
- Create schema & table
|
1 2 3 |
CREATE DATABASE IF NOT EXISTS dba; -- (Run the CREATE TABLE from Step 2) |
- Enable instrumentation (safe in prod)
|
1 2 3 4 5 6 7 8 |
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME IN ('events_statements_history','events_statements_history_long', 'global_instrumentation','thread_instrumentation'); UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE 'wait/lock/innodb/%' OR NAME LIKE 'wait/lock/metadata/sql/mdl'; |
- Create the procedure (pick one variant)
|
1 2 3 4 5 6 7 8 9 10 11 |
-- 8.0/Aurora 3.x using sys.innodb_lock_waits DELIMITER // DROP PROCEDURE IF EXISTS dba.capture_locks// CREATE PROCEDURE dba.capture_locks() BEGIN IF EXISTS (SELECT 1 FROM sys.innodb_lock_waits) THEN INSERT INTO dba.lock_wait_captures (...columns...) SELECT NOW(), ... FROM sys.innodb_lock_waits; END IF; END// DELIMITER ; |
2) Turn on automation
- Enable the scheduler and create events
|
1 2 3 4 5 6 7 8 9 |
SET GLOBAL event_scheduler = ON; -- persist in parameter group CREATE EVENT IF NOT EXISTS dba.capture_locks_every_5s ON SCHEDULE EVERY 5 SECOND DO CALL dba.capture_locks(); CREATE EVENT IF NOT EXISTS dba.purge_lock_captures_daily ON SCHEDULE EVERY 1 DAY DO DELETE FROM dba.lock_wait_captures WHERE captured_at < NOW() - INTERVAL 7 DAY; |
3) Sanity checks
- Verify the view exists (8.0 engines)
|
1 2 |
SELECT COUNT(*) FROM sys.innodb_lock_waits; |
- Or verify P_S objects
|
1 2 |
SELECT COUNT(*) FROM performance_schema.data_lock_waits; |
- Generate a quick test wait (optional)
|
1 2 3 4 5 6 7 |
-- Session A START TRANSACTION; SELECT * FROM your_table WHERE id=42 FOR UPDATE; -- leave open -- Session B UPDATE your_table SET col = col WHERE id=42; -- will wait -- Now check captures: SELECT * FROM dba.lock_wait_captures ORDER BY captured_at DESC LIMIT 5; |
4) Nice-to-haves
- Send MySQL error log to CloudWatch; set
innodb_print_all_deadlocks=ON - Add a Grafana/PI panel for
row lockwaits to spot spikes - Bake Step 1 instrumentation into an init job so it survives restarts
