MySQL如何查看元數(shù)據(jù)鎖阻塞在哪里
發(fā)布日期：2022-03-31 16:07 | 文章來源：腳本之家
MySQL如何查看元數(shù)據(jù)鎖阻塞在哪里
操作步驟：
1、session 1 執(zhí)行：
start transaction; select *from t1;
2、session 2 在第1步執(zhí)行完后執(zhí)行：
drop table t1;
此時session 2的drop語句被阻塞。那么怎么分析查看元數(shù)據(jù)鎖呢？
方法：
1）執(zhí)行show processlist;，可以看到drop語句在等待元數(shù)據(jù)鎖
mysql> show processlist; +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+ | 5 | system user | | NULL | Connect | 1050234 | Waiting for master to send event | NULL | | 6 | system user | | NULL | Connect | 983193 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 8 | root | localhost | yzs | Sleep | 93 | | NULL | | 9 | root | localhost | yzs | Query | 3 | Waiting for table metadata lock | drop table t1 | | 10 | root | localhost | NULL | Query | 0 | init | show processlist | +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+ 5 rows in set (0.00 sec)
2）可以看到當前正在運行的事務的線程是trx_mysql_thread_id:8，那么這個線程在干什么呢？
mysql> select *from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 17683
trx_state: RUNNING
trx_started: 2017-10-18 05:32:46
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 320
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.03 sec)
3）可以看到這個線程執(zhí)行的是select語句，如果執(zhí)行show engine innodb status;可以看到該事務處于sleep狀態(tài)，也就是說這個事務語句執(zhí)行完了，但是沒有提交。
執(zhí)行kill 8，將該事務的線程殺掉就可以了?；蛘邫z查業(yè)務的SQL語句，檢查下是否有未提交的SQL語句。
mysql> select *from performance_schema.events_statements_current\G
*************************** 1. row ***************************
THREAD_ID: 27
EVENT_ID: 15
END_EVENT_ID: 15
EVENT_NAME: statement/sql/select
SOURCE: mysqld.cc:962
TIMER_START: 1050544992900922000
TIMER_END: 1050544993740836000
TIMER_WAIT: 839914000
LOCK_TIME: 196000000
SQL_TEXT: select *from t1
DIGEST: 1aa32397c8ec37230aed78ef16126571
DIGEST_TEXT: SELECT * FROM `t1`
CURRENT_SCHEMA: yzs
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 10
ROWS_EXAMINED: 10
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 1
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 1
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
如有疑問請留言或者到本站社區(qū)交流討論，感謝閱讀，希望能幫助到大家，謝謝大家對本站的支持！
版權聲明：本站文章來源標注為YINGSOO的內(nèi)容版權均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學習參考，不代表本站立場，如有內(nèi)容涉嫌侵權，請聯(lián)系alex-e#qq.com處理。
相關文章
關注官方微信