Quick Solutions for MySQL Table Locks

Step 1 – Check if any tables are in use show open tables where in_use > 0 ; If the result set is empty, no tables are locked and the procedure ends.

Step 2 – Inspect current processes for slow or sleeping queries show processlist;

Step 3 – List all active InnoDB transactions SELECT * FROM information_schema.INNODB_TRX;

Step 4 – View current InnoDB locks SELECT * FROM information_schema.INNODB_LOCKS;

Step 5 – Examine lock wait relationships SELECT * FROM information_schema.INNODB_LOCK_waits;

If a transaction appears in INNODB_TRX and its thread ID matches a sleeping entry in show processlist , the session is holding a lock and should be terminated.

Step 6 – Generate KILL statements for the offending sessions SELECT concat('KILL ',id,';') FROM information_schema.processlist p INNER JOIN information_schema.INNODB_TRX x ON p.id=x.trx_mysql_thread_id WHERE db='test';

Execute the resulting KILL commands, for example:

KILL 42; KILL 40;

Running these statements releases the table locks and restores normal database operation.