[MySQL] Table lock 조회 쿼리

[MySQL] Table lock 조회 쿼리

SHOW OPEN TABLES WHERE In_use > 0;


# MySQL 8.0
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       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;
  

# MySQL 5.7
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;

 

Ref

https://dev.mysql.com/doc/refman/8.0/en/show-open-tables.html

MySQL :: MySQL 8.0 Reference Manual :: 15.7.7.24 SHOW OPEN TABLES Statement

https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-examples.html

MySQL :: MySQL 8.0 Reference Manual :: 17.15.2.1 Using InnoDB Transaction and Locking Information