Aurora MySQL Lock Detection Script - Complete Production Guide 2026
In a production Aurora MySQL environment, undetected locks can silently degrade application performance, cause connection pool exhaustion, and lead to cascading timeouts across microservices. A single long-running transaction holding row locks can block hundreds of queries, turning a minor issue into a critical incident.
This article provides a comprehensive Shell Script for Aurora MySQL Lock Detection and Analysis. It covers blocking sessions, InnoDB lock waits, metadata locks, and transaction isolation issues—perfect for daily monitoring, incident response, or pre-deployment validation.
Table of Contents
- Why You Must Monitor Locks in Aurora MySQL Daily
- Production-Ready Lock Detection Script (Shell + SQL)
- Script Output & Analysis Explained
- Critical Components: InnoDB Locks, Metadata Locks & Deadlocks
- Troubleshooting Common Lock Issues
- How to Automate This Monitoring (CloudWatch + Cron)
- Interview Questions: MySQL Lock Troubleshooting
- Final Summary
- FAQ
- About the Author
1. Why You Must Monitor Locks in Aurora MySQL Daily
Aurora MySQL's high-performance architecture doesn't eliminate locking issues—it amplifies their impact across reader endpoints and application tiers. Neglecting lock monitoring leads to:
- Connection Pool Exhaustion: Blocked sessions accumulate, consuming max_connections limits
- Cascading Query Timeouts: Applications experience widespread 30-second timeouts
- Metadata Lock Deadlocks: DDL operations (ALTER TABLE) stuck behind long transactions
- Replication Lag on Readers: Lock waits on the writer propagate to read replicas
- Split-Second SLA Breaches: P99 latency spikes from 50ms to 5+ seconds
Running a unified lock detection script ensures you catch blocking chains, identify victim queries, and resolve issues before they trigger PagerDuty alerts.
2. Production-Ready Lock Detection Script
This shell script combines Performance Schema queries, InnoDB lock analysis, and metadata lock detection to provide a complete locking overview.
PROCESS and SELECT privileges on performance_schema and information_schema.
#!/bin/bash
# ====================================================
# Aurora MySQL Lock Detection & Analysis Script
# Author: Chetan Yadav
# Usage: ./aurora_lock_detection.sh
# ====================================================
# MySQL Connection Parameters
MYSQL_HOST="your-aurora-cluster.cluster-xxxxx.us-east-1.rds.amazonaws.com"
MYSQL_PORT="3306"
MYSQL_USER="monitor_user"
MYSQL_PASS="your_secure_password"
MYSQL_DB="information_schema"
# Output file for detailed logging
OUTPUT_LOG="/tmp/aurora_lock_detection_$(date +%Y%m%d_%H%M%S).log"
echo "==================================================" | tee -a $OUTPUT_LOG
echo " AURORA MYSQL LOCK DETECTION - $(date) " | tee -a $OUTPUT_LOG
echo "==================================================" | tee -a $OUTPUT_LOG
# 1. Check for Blocking Sessions (InnoDB Lock Waits)
echo -e "\n[1] Detecting InnoDB Lock Waits..." | tee -a $OUTPUT_LOG
mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASS \
-D $MYSQL_DB -sN <<EOF | tee -a $OUTPUT_LOG
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id
ORDER BY wait_seconds DESC;
EOF
# 2. Check for Long-Running Transactions
echo -e "\n[2] Long-Running Transactions (>30 sec)..." | tee -a $OUTPUT_LOG
mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASS \
-D $MYSQL_DB -sN <<EOF | tee -a $OUTPUT_LOG
SELECT
trx_id,
trx_mysql_thread_id AS thread_id,
trx_state,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS runtime_sec,
trx_rows_locked,
trx_rows_modified,
SUBSTRING(trx_query, 1, 80) AS query_snippet
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30
ORDER BY runtime_sec DESC;
EOF
# 3. Check for Metadata Locks
echo -e "\n[3] Detecting Metadata Locks..." | tee -a $OUTPUT_LOG
mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASS \
-D performance_schema -sN <<EOF | tee -a $OUTPUT_LOG
SELECT
object_schema,
object_name,
lock_type,
lock_duration,
lock_status,
owner_thread_id
FROM metadata_locks
WHERE lock_status = 'PENDING'
AND object_schema NOT IN ('performance_schema', 'mysql');
EOF
# 4. Check Active Processlist
echo -e "\n[4] Active Processlist..." | tee -a $OUTPUT_LOG
mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASS \
-e "SHOW FULL PROCESSLIST;" | grep -v "Sleep" | tee -a $OUTPUT_LOG
# 5. Check Last Deadlock
echo -e "\n[5] Last Detected Deadlock..." | tee -a $OUTPUT_LOG
mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASS \
-e "SHOW ENGINE INNODB STATUS\G" | \
grep -A 50 "LATEST DETECTED DEADLOCK" | tee -a $OUTPUT_LOG
echo -e "\n==================================================" | tee -a $OUTPUT_LOG
echo " LOCK DETECTION COMPLETE. Review: $OUTPUT_LOG " | tee -a $OUTPUT_LOG
echo "==================================================" | tee -a $OUTPUT_LOG
This script consolidates five critical lock detection queries into a single diagnostic report, providing immediate visibility into blocking sessions and lock contention hotspots.
3. Script Output & Analysis Explained
| Check Component | What "Healthy" Looks Like | Red Flags |
|---|---|---|
| InnoDB Lock Waits | Empty result set (no blocking chains) | Any rows indicate active blocking; wait_time > 5 seconds is critical |
| Long Transactions | Transactions < 5 seconds | Transactions > 60 seconds with high trx_rows_locked indicate forgotten transactions |
| Metadata Locks | No PENDING locks | PENDING metadata locks block DDL; check for unclosed transactions on that table |
| Processlist | Queries in "Sending data" or "Sorting result" | Multiple queries stuck in "Waiting for table metadata lock" |
| Deadlock Log | No recent deadlocks | Review victim query patterns; consider application-level retry logic |
4. Critical Components: InnoDB Locks, Metadata Locks & Deadlocks
Understanding MySQL's locking mechanisms is vital for Aurora DBAs:
InnoDB Row Locks
Acquired automatically during DML operations (UPDATE, DELETE). Uses MVCC (Multi-Version Concurrency Control) to allow non-blocking reads while writes are in progress. Lock waits occur when two transactions try to modify the same row.
Metadata Locks (MDL)
Protect table structure during DDL operations (ALTER TABLE, DROP TABLE). A long-running SELECT can hold a metadata lock that blocks an ALTER TABLE, even though no row locks exist.
Deadlocks
Occur when two transactions acquire locks in opposite orders. InnoDB automatically detects deadlocks and rolls back the smaller transaction (the "victim"). Frequent deadlocks indicate poor transaction design or missing indexes.
Gap Locks
Used in REPEATABLE READ isolation level to prevent phantom reads. Can cause unexpected blocking when queries scan ranges without proper indexes.
5. Troubleshooting Common Lock Issues
If the script reports blocking or long lock waits, follow this workflow:
Issue: InnoDB Lock Wait Timeout (Error 1205)
Symptom: Application logs show Lock wait timeout exceeded; try restarting transaction
Root Cause: A blocking transaction is holding locks longer than innodb_lock_wait_timeout (default 50 seconds)
Resolution:
- Identify blocking thread from script output:
blocking_threadcolumn - Kill the blocking session:
KILL <blocking_thread>; - Review the blocking query for missing indexes or inefficient WHERE clauses
- Check application code for forgotten transactions (missing COMMIT/ROLLBACK)
Issue: Metadata Lock Contention
Symptom: ALTER TABLE hangs indefinitely; other queries stuck in "Waiting for table metadata lock"
Root Cause: Long-running transaction or unclosed connection holding metadata lock
Resolution:
- Find the owner: Check
owner_thread_idfrom metadata_locks query - Review processlist for that thread:
SELECT * FROM performance_schema.threads WHERE thread_id = <owner_thread_id>; - Kill the offending session:
KILL <processlist_id>; - For production DDL, use
pt-online-schema-changeto avoid metadata locks
Issue: Frequent Deadlocks
Symptom: Application retries constantly; SHOW ENGINE INNODB STATUS shows many deadlocks
Root Cause: Transactions accessing tables in different orders or using REPEATABLE READ with range scans
Resolution:
- Review deadlock victim queries in InnoDB status output
- Ensure all transactions access tables in the same order
- Add indexes to reduce gap locks
- Consider changing isolation level to READ COMMITTED (if phantom reads are acceptable)
6. How to Automate This Monitoring (CloudWatch + Cron)
Method 1: Cron-Based Monitoring
Schedule this script to run every 5 minutes during peak hours:
*/5 * * * * /home/ec2-user/scripts/aurora_lock_detection.sh >> /var/log/aurora_locks.log 2>&1
Method 2: CloudWatch Custom Metrics
Enhance the script to publish lock counts to CloudWatch:
# Add after each check
LOCK_COUNT=$(mysql -h $MYSQL_HOST ... | wc -l)
aws cloudwatch put-metric-data \
--namespace "Aurora/Locks" \
--metric-name "InnoDB_Lock_Waits" \
--value $LOCK_COUNT \
--region us-east-1
Create CloudWatch alarms:
- Trigger if
InnoDB_Lock_Waits > 5for 2 consecutive periods - SNS notification to on-call engineer
Method 3: Performance Insights Integration
Aurora's Performance Insights automatically tracks lock waits. Use this script as a supplementary deep-dive tool when Performance Insights shows spikes in wait/io/table/sql/handler or wait/lock/table/sql/handler.
7. Interview Questions: MySQL Lock Troubleshooting
Prepare for these questions in Aurora/MySQL DBA interviews:
A: InnoDB uses row-level locking for DML operations, allowing high concurrency. Table locks (LOCK TABLES) lock the entire table and block all other operations. MyISAM uses table locks by default; InnoDB uses row locks with MVCC.
A: REPEATABLE READ uses gap locks to prevent phantom reads. If two transactions scan overlapping ranges without proper indexes, they can acquire gap locks in opposite orders, causing deadlocks. READ COMMITTED avoids gap locks but allows phantom reads.
A: Query information_schema.innodb_lock_waits joined with innodb_trx to map blocking_trx_id to the actual query. Use SHOW ENGINE INNODB STATUS for detailed lock information including locked record details.
A: Long-running queries or unclosed transactions holding shared metadata locks. Even a simple SELECT with an open transaction prevents DDL operations. Use lock_wait_timeout and ensure applications properly close connections.
A: (1) Access tables in consistent order across all transactions, (2) Keep transactions short, (3) Use appropriate indexes to reduce gap locks, (4) Consider READ COMMITTED isolation if acceptable, (5) Implement exponential backoff retry logic.
8. Final Summary
A healthy Aurora MySQL cluster requires proactive lock monitoring, not just reactive troubleshooting. The script provided above delivers instant visibility into blocking sessions, long transactions, and metadata lock contention.
Use this script as part of your Daily Health Check routine and integrate it with CloudWatch alarms for real-time alerting. Combine it with Performance Insights for comprehensive lock analysis during incidents.
- InnoDB lock waits > 5 seconds require immediate investigation
- Metadata locks can block DDL even without row lock contention
- Deadlocks indicate transaction design issues or missing indexes
- Automate monitoring with CloudWatch custom metrics
9. FAQ
A: The queries access information_schema and performance_schema, which are lightweight metadata operations. Running every 5 minutes has negligible impact. Avoid running every 10 seconds on large clusters.
A: The transaction may have completed its query but hasn't committed. Check trx_state in innodb_trx—if it's "LOCK WAIT", the transaction is idle but holding locks. Kill it if it's been idle > 5 minutes.
A: GRANT SELECT ON information_schema.* TO 'monitor_user'@'%'; GRANT SELECT ON performance_schema.* TO 'monitor_user'@'%'; GRANT PROCESS ON *.* TO 'monitor_user'@'%';
A: Yes, the script uses standard MySQL 5.7+ features. Aurora MySQL 2.x (MySQL 5.7) and 3.x (MySQL 8.0) both support these queries. MySQL 8.0 has enhanced performance_schema lock tables for deeper analysis.
A: Performance Insights provides visual dashboards and historical trends. This script gives real-time CLI output with specific blocking chains and kill commands—ideal for incident response and automation.
No comments:
Post a Comment