MySQL: To find blocking locking transactions

to see what is in the buffer:

SHOW ENGINE INNODB STATUS;

Find the thread ID (ID column)

SHOW FULL PROCESSLIST;

To kill the process, run the command

kill ID;

Other helpful SQLs:

show variables like 'innodb_lock_wait_timeout';

SHOW GLOBAL VARIABLES LIKE 'innodb_rollback_on_timeout';

 SHOW GLOBAL STATUS LIKE 'com_rollback';

SHOW ENGINE INNODB STATUS;

SHOW FULL PROCESSLIST;

SELECT * FROM performance_schema.data_lock_waits;

SELECT * FROM performance_schema.events_statements_history WHERE `THREAD_ID` = 1254;

SELECT min(`THREAD_ID`), max(`THREAD_ID`) FROM performance_schema.events_statements_history;

 SELECT @@performance_schema_events_statements_history_size;

  SELECT @@performance_schema_events_statements_history_long_size;

  UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_history_long';

  SELECT count(`THREAD_ID`) FROM performance_schema.events_statements_history_long;

  SELECT * FROM performance_schema.data_lock_waits;

  SELECT `THREAD_ID`,`EVENT_ID`,`EVENT_NAME`, `CURRENT_SCHEMA`,`SQL_TEXT` FROM events_statements_history_long WHERE `THREAD_ID` = BLOCKING_THREAD_ID ORDER BY `EVENT_ID`;