Check and resolve locking transaction in MySQL/Mariadb

Check the locked Table in MySQL

-- Syntax:
SHOW OPEN TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

--Examples IN_USE 0 means no lock on table, 1 means in use by one transaction... and so on......

show open tables where in_use > 1;

show open tables like 'tab3';

show open tables in test2;

show open tables where in_use > 0 and table = 'temp'

For remove the lock from tables:

unlock tables;

Check the query using in the locking of table:

SHOW FULL PROCESSLIST;

SHOW PROCESSLIST;

select * from information_schema.processlist where info is not null 

Check the blocking and waiting session in MySQL:

-- Check deadlock or blocking session
show engine innodb status

--Check waiting session
select * from information_schema.processlist where state like '%waiting%'

--Check blocking session 
SELECT pl.id,pl.user,pl.state,it.trx_id,it.trx_mysql_thread_id 
,it.trx_query AS query ,it.trx_id AS blocking_trx_id
,it.trx_mysql_thread_id AS blocking_thread
,it.trx_query AS blocking_query
FROM information_schema.processlist AS pl 
INNER JOIN information_schema.innodb_trx AS it
	ON pl.id = it.trx_mysql_thread_id
INNER JOIN information_schema.innodb_lock_waits AS ilw
	ON it.trx_id = ilw.requesting_trx_id 
        AND it.trx_id = ilw.blocking_trx_id

--Check blocking session
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       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;

To resolve the deadlock and blocking session in MySQL

Kill sessionid

example:
show processlist
54	root	localhost:62339	sys	Sleep
55	root	localhost:62340	test	Sleep

KILL 54