Check the waiting or locking session in MySQL or MariaDB

Check the waiting and locking session in MySQL or MariaDB.

Check for waiting session in MariaDB or MySQL:

select * from information_schema.processlist where state like '%waiting%';
select * from information_schema.processlist where info is not null ;

SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS;

Check for the transaction waiting:

SELECT * FROM INNODB_LOCK_WAITS;

SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INNODB_TRX
WHERE TRX_STATE = 'LOCK WAIT';

Check lock on the particular table:

SELECT * FROM INNODB_LOCKS WHERE LOCK_TABLE = db_name.table_name;

show open tables where in_use>0;

Check waiting Queries:

SELECT INNODB_LOCKS.* 
FROM INNODB_LOCKS
JOIN INNODB_LOCK_WAITS
  ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID);

SELECT * 
FROM INNODB_LOCKS 
WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS);

Check transaction running or blocking:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.