InnoDB row locking is usually invisible — until it causes deadlocks or long-running transactions that block other queries. Here is how to monitor and diagnose lock contention.
Detect Active Locks
sql
-- MySQL 8.0: performance_schema.data_locks
SELECT
ENGINE_LOCK_ID,
ENGINE_TRANSACTION_ID,
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
LOCK_TYPE,
LOCK_MODE,
LOCK_STATUS
FROM performance_schema.data_locks
WHERE LOCK_STATUS = 'WAITING';Find Blocking Transaction
sql
SELECT
waiting.trx_id AS waiting_id,
waiting.trx_query AS waiting_query,
blocking.trx_id AS blocking_id,
blocking.trx_query AS blocking_query,
blocking.trx_started,
TIMESTAMPDIFF(SECOND, blocking.trx_started, now()) AS blocking_seconds
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx waiting ON waiting.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx blocking ON blocking.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;Kill a Blocking Transaction
sql
-- Find the process ID
SELECT trx_mysql_thread_id
FROM information_schema.innodb_trx
WHERE trx_id = 'blocking_trx_id_here';
-- Kill it
KILL 1234;InnoDB Status: Lock Section
sql
SHOW ENGINE INNODB STATUS\G
-- Look for TRANSACTIONS section:
-- '---TRANSACTION X, ACTIVE Y sec'
-- 'LOCK WAIT Z lock struct(s), heap size'
-- 'MySQL thread id M, OS thread handle N'Prevent Lock Escalation
sql
-- Set lock wait timeout (default 50 seconds is too long)
SET GLOBAL innodb_lock_wait_timeout = 10;
-- Detect deadlocks in application (check errno 1213)
-- Implement retry logic for deadlock errors
-- Use SELECT ... FOR UPDATE only when you will immediately update
-- Use SELECT ... FOR SHARE for read locks that do not need write exclusivityKey Takeaways
- Use
performance_schema.data_locks(MySQL 8.0+) for real-time lock visibility - Set
innodb_lock_wait_timeout = 10— the default 50 seconds is too long for OLTP - Long-running transactions are the root cause of most lock contention — keep transactions short
- Implement retry logic for deadlock errors (errno 1213) in your application
JusDB Can Help
InnoDB lock contention causes latency spikes that are hard to diagnose without the right queries. JusDB can instrument your MySQL instance and resolve lock hotspots.