MySQL location lock contention for a more serious table

1: View the current transaction

Mysql> SELECT * from Information_schema.innodb_trx \g

2: View the currently locked transactions

Mysql> SELECT * from INFORMATION_SCHEMA. Innodb_locks;

3: View transactions for the current lock

Mysql> SELECT * from INFORMATION_SCHEMA. Innodb_lock_waits;

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, Timestampdiff (SECOND, r.trx_wait_started, Current_timestamp) wait_time, R.trx_query Waiting_query, L.lock_table Wai Ting_table_lock, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, SUBSTRING (p. HO ST, 1, INSTR (p. Host, ': ')-1) blocking_host, SUBSTRING (p. Host, INSTR (p. Host, ': ') + 1) BL Ocking_port, IF (p.command = ' Sleep ', p.time, 0) Idel_in_trx, b.trx_query blocking_query from Information_schem A.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 INNER JOIN information_schema. Innodb_locks l on w.requested_lock_id = l.lock_id left JOIN information_schema. Processlist p on p.id = b.trx_mysql_thread_id ORDER by wait_time DESC;

　　

MySQL location lock contention for a more serious table