MariaDB – Identify Blocking/Blocked Queries

Here is a SQL script that you can use to identify Blocking and Blocked Queries in MariaDB.

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 
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 AS r 
		ON r.trx_id = w.requesting_trx_id

Cheers!

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter