MySQL connection and InnoDB lock investigation cheat sheet
Inspect MySQL identity, connection saturation, running sessions, active InnoDB transactions and lock dependencies.
MySQL 8.0/8.4 SQL client. Performance Schema access and PROCESS visibility depend on the monitoring account privileges.
Updated · MySQL · 6 min read
| Command | What it does |
|---|---|
SELECT VERSION(), @@hostname, @@port, DATABASE(), CURRENT_USER(); | Identify server and authenticated account |
SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Max_used_connections','Aborted_connects'); | Inspect connection and activity counters |
SHOW GLOBAL VARIABLES LIKE 'max_connections'; | Read the configured connection ceiling |
SHOW FULL PROCESSLIST; | Inspect visible sessions and their state |
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 20; | Inspect older InnoDB transactions |
SELECT REQUESTING_ENGINE_TRANSACTION_ID, BLOCKING_ENGINE_TRANSACTION_ID FROM performance_schema.data_lock_waits LIMIT 20; | Inspect current data-lock dependencies |
Walkthrough
Use an existing secure connection to the affected instance. These statements inspect metadata without killing connections or changing global settings. Queries and process metadata may contain sensitive information.
Step 1: Identify server and authenticated account
SELECT VERSION(), @@hostname, @@port, DATABASE(), CURRENT_USER();
Compare version, server host, port and current database with the incident environment. CURRENT_USER identifies the privilege account used by MySQL, which can differ from the client-supplied login identity. A null database can simply mean none was selected.
Step 2: Inspect connection and activity counters
SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Max_used_connections','Aborted_connects');
Compare current connections and running threads with a healthy baseline. Max_used_connections is a recorded peak; Aborted_connects is cumulative. Neither value alone proves the current application is hitting its connection limit.
Step 3: Read the configured connection ceiling
SHOW GLOBAL VARIABLES LIKE 'max_connections';
Compare the ceiling with current connections and application pool sizes across all instances. A higher ceiling is not a free performance improvement; additional sessions consume resources and can increase contention.
Step 4: Inspect visible sessions and their state
SHOW FULL PROCESSLIST;
Look for long-running commands, waits and repeated application users. FULL includes complete statement text, so avoid sharing raw results. Without the relevant privileges you may see only your own sessions; a short list does not prove the server is quiet.
Step 5: Inspect older InnoDB transactions
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 20;
Compare transaction start time and state with the incident. trx_mysql_thread_id links to the connection identity shown in processlist. An old transaction is a lead, not proof that it blocks other transactions; check the wait relationships next.
Step 6: Inspect current data-lock dependencies
SELECT REQUESTING_ENGINE_TRANSACTION_ID, BLOCKING_ENGINE_TRANSACTION_ID FROM performance_schema.data_lock_waits LIMIT 20;
Map requesting and blocking transaction IDs to INNODB_TRX while the wait exists. This is a changing snapshot, not a history of all lock incidents, and metadata-lock waits need a different view. Multiple blockers can exist for a request.
WORKED INCIDENT
Practical case: An update queue grows behind one transaction
Fictional teaching scenario; observations are examples, not captured production output.
Situation
Application updates slow down while read requests continue and the connection count is below its ceiling.
Investigation sequence
1. Inspect connection and activity counters
SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Max_used_connections','Aborted_connects');Record current connection/activity values before attributing the incident to connection exhaustion.
2. Inspect visible sessions and their state
SHOW FULL PROCESSLIST;Inspect session states and correlate long waits with the affected application user.
3. Inspect older InnoDB transactions
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 20;Record transaction IDs and their connection IDs for older open transactions.
4. Inspect current data-lock dependencies
SELECT REQUESTING_ENGINE_TRANSACTION_ID, BLOCKING_ENGINE_TRANSACTION_ID FROM performance_schema.data_lock_waits LIMIT 20;Map current lock dependencies. In this example several requesting transaction IDs point to one blocker.
Example findings
Illustrative findings: connections are below max_connections; several InnoDB requests wait behind one older transaction; the application pool retains its connection.
Interpret the findings
The wait graph supports lock contention rather than connection saturation. Determine why the blocking transaction remains open before recommending a session termination or a timeout change.
Verify the outcome
After the owner resolves the transaction through the approved process, repeat the lock and process views. Verify a representative update completes and that application latency returns to baseline.
Escalation handoff
Provide server identity, transaction/connection IDs, wait samples and start times to the DBA/application owner. Redact literal SQL values and avoid sending full processlist output publicly.