如何使用mysql 5.6 information schema定位事務鎖資訊
引子 mysql資料庫在執行期間，隨著業務體量增加及併發會話陡升，可能隨時會出現各種效能問題。其中比較常見的一種 現象，某一天公司業務人員或客戶反饋說某個業務模板突然卡住了，或者開發同學說某個SQL語句不能繼續執行了。 mysql 5.6引入的information_schema資料庫，可以完美解決上述的問題。它提供一系列的資料檢視或表，便於 診斷及分析資料庫的各種各樣的效能問題，對於運維同學真是大大福利。本文主要介紹information_schema與鎖 相關的幾個表，快速定位是哪些會話或事務導致事務操作不能持續。 概念 information-schema是一個內建的資料庫，通過一系列的表，比如：鎖方面的表，字符集相關的表，外掛相關的 表，程式相關的表，檢視相關的表，不一而足。運維人員可以通過不同的表的資訊，有助於分析各種各樣的效能問題 。 當然，可以結合另一個資料庫performance_schema資料庫，更方便診斷資料庫的各種各樣的效能問題甚至故障 情形。 mysql information-schema官方手冊，請查閱如下連結 https://dev.mysql.com/doc/refman/5.6/en/information-schema.html mysql鎖相關官方手冊，請查閱如下連結 https://dev.mysql.com/doc/refman/5.6/en/innodb-locking-transaction-model.html information-schema實踐 1，資料庫版本 [root@standbygtid ~]# mysql -V mysql Ver 14.14 Distrib 5.6.25, for Linux (x86_64) using EditLine wrapper 2，登陸mysql [root@standbygtid ~]# mysql -uroot -psystem 3，顯示事務及鎖相關的表 （注：有ORACLE從業經驗的同學，類似於oracle 動態效能檢視v$session及locked_objects） mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> show tables like 'INNODB%'; +----------------------------------------+ | Tables_in_information_schema (INNODB%) | +----------------------------------------+ | INNODB_LOCKS | | INNODB_TRX | 略 | INNODB_LOCK_WAITS | +----------------------------------------+ 28 rows in set (0.00 sec) 4，上述幾個表的含義 ---鎖表 （注：鎖是什麼，就是你需要某種資源，但此時由人家佔著，你需要等待，這就是一種鎖，鎖的目標就是維護資料一致性） mysql> desc innodb_locks; +-------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------+-------+ | lock_id | varchar(81) | NO | | | | 鎖編號 | lock_trx_id | varchar(18) | NO | | | | 鎖所屬事務 | lock_mode | varchar(32) | NO | | | | 鎖模式 | lock_type | varchar(32) | NO | | | | 鎖型別 | lock_table | varchar(1024) | NO | | | | 鎖對應表 | lock_index | varchar(1024) | YES | | NULL | | 鎖對應索引 | lock_space | bigint(21) unsigned | YES | | NULL | | 鎖空間 | lock_page | bigint(21) unsigned | YES | | NULL | | 鎖對應的頁面 | lock_rec | bigint(21) unsigned | YES | | NULL | | 鎖對應的表記錄 | lock_data | varchar(8192) | YES | | NULL | | +-------------+---------------------+------+-----+---------+-------+ 10 rows in set (0.00 sec) ---事務表 mysql> desc innodb_trx; +----------------------------+---------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+---------------------+------+-----+---------------------+-------+ | trx_id | varchar(18) | NO | | | | 事務編號 | trx_state | varchar(13) | NO | | | | 事務狀態 | trx_started | datetime | NO | | 0000-00-00 00:00:00 | | 事務開始時間 | trx_requested_lock_id | varchar(81) | YES | | NULL | | 事務請求鎖編號 | trx_wait_started | datetime | YES | | NULL | | 事務等待開始時間 | trx_weight | bigint(21) unsigned | NO | | 0 | | 事務權重 | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | | 事務對應的執行緒 | trx_query | varchar(1024) | YES | | NULL | | 事務所屬的SQL語句 | trx_operation_state | varchar(64) | YES | | NULL | | | trx_tables_in_use | bigint(21) unsigned | NO | | 0 | | | trx_tables_locked | bigint(21) unsigned | NO | | 0 | | | trx_lock_structs | bigint(21) unsigned | NO | | 0 | | | trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | | | trx_rows_locked | bigint(21) unsigned | NO | | 0 | | | trx_rows_modified | bigint(21) unsigned | NO | | 0 | | | trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | | | trx_isolation_level | varchar(16) | NO | | | | | trx_unique_checks | int(1) | NO | | 0 | | | trx_foreign_key_checks | int(1) | NO | | 0 | | | trx_last_foreign_key_error | varchar(256) | YES | | NULL | | | trx_adaptive_hash_latched | int(1) | NO | | 0 | | | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | | | trx_is_read_only | int(1) | NO | | 0 | | | trx_autocommit_non_locking | int(1) | NO | | 0 | | +----------------------------+---------------------+------+-----+---------------------+-------+ 24 rows in set (0.01 sec) ---鎖等待表 mysql> desc innodb_lock_waits; +-------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------+-------+ | requesting_trx_id | varchar(18) | NO | | | | 請求鎖事務編號 | requested_lock_id | varchar(81) | NO | | | | 請求鎖編號 | blocking_trx_id | varchar(18) | NO | | | | 持鎖事務編號 | blocking_lock_id | varchar(81) | NO | | | | 持鎖 鎖編號 +-------------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) 5，為了模擬事務，關閉自動提交 （注：生產系統一定要關閉，防止不小心在生產系統產生誤操作無法撤回） mysql> show variables like '%autocommit%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> set autocommit=off; Query OK, 0 rows affected (0.01 sec) mysql> show variables like '%autocommit%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) 6，產生一個事務 --新開一個登陸會話，不提交 （注：如果一提交，則事務馬上消失） mysql> update zxydb.t_go set a=3; Query OK, 16778789 rows affected (1 min 0.91 sec) Rows matched: 25168933 Changed: 16778789 Warnings: 0 --在另一會話檢視執行緒資訊 mysql> show processlist; +----+------+-----------+--------------------+---------+------+----------+---------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+--------------------+---------+------+----------+---------------------------+ | 28 | root | localhost | information_schema | Query | 19 | updating | update zxydb.t_go set a=3 | | 29 | root | localhost | NULL | Query | 0 | init | show processlist | +----+------+-----------+--------------------+---------+------+----------+---------------------------+ 2 rows in set (0.00 sec) --檢視事務表 mysql> select * from information_schema.innodb_trx\G; *************************** 1. row *************************** trx_id: 3996 trx_state: RUNNING 事務執行狀態 trx_started: 2019-11-06 05:46:18 事務開始的時間 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 25224373 trx_mysql_thread_id: 28 事務所屬的執行緒，對應上述的show processlist之id列 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 55440 trx_lock_memory_bytes: 8042024 trx_rows_locked: 25224372 trx_rows_modified: 25168933 事務影響的表記錄數 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) ERROR: No query specified --由上可見如果沒有競爭資源時，不會產生鎖 （注：產生鎖的前提條件是必須在2個會話以上，當然不包括mysql自身產生的bug） mysql> select * from information_schema.innodb_locks\G; Empty set (0.04 sec) ERROR: No query specified --沒有競資源，當然也不會產生鎖等待 mysql> select * from information_schema.innodb_lock_waits\G; Empty set (0.03 sec) ERROR: No query specified 7，再開啟一個新事務會話 （注：更新上述同一個表的記錄，即會產生鎖等待，因為需要更新同一個表的記錄資源） mysql> set autocommit=off; Query OK, 0 rows affected (0.01 sec) mysql> insert into zxydb.t_go select 3,3; ----可見產生了鎖資訊 mysql> select * from information_schema.innodb_locks\G; *************************** 1. row *************************** lock_id: 3997:6:55726:1 lock_trx_id: 3997 lock_mode: X 鎖模式，x表示排它鎖，s表示共享鎖 lock_type: RECORD lock_table: `zxydb`.`t_go` 鎖定表 lock_index: GEN_CLUST_INDEX GEN_CLUST_INDEX表示表級鎖 lock_space: 6 lock_page: 55726 lock_rec: 1 lock_data: supremum pseudo-record *************************** 2. row *************************** lock_id: 3996:6:55726:1 lock_trx_id: 3996 lock_mode: X lock_type: RECORD lock_table: `zxydb`.`t_go` lock_index: GEN_CLUST_INDEX lock_space: 6 lock_page: 55726 lock_rec: 1 lock_data: supremum pseudo-record 2 rows in set (0.03 sec) ERROR: No query specified ---同時也產生鎖等待資訊 mysql> select * from information_schema.innodb_lock_waits\G; *************************** 1. row *************************** requesting_trx_id: 3997 請求鎖的事務id requested_lock_id: 3997:6:55726:1 blocking_trx_id: 3996 持鎖的事務id blocking_lock_id: 3996:6:55726:1 1 row in set (0.03 sec) ERROR: No query specified 8，為了方便監控鎖等待的資訊，可以編寫下述SQL語句 ---獲取持鎖會話及等待鎖會話更詳細的資訊 select trx.trx_mysql_thread_id, trx.trx_id, trx.trx_state, trx.trx_started, trx.trx_query, locks.lock_type, locks.lock_table, lock_waits.requesting_trx_id, lock_waits.blocking_trx_id from information_schema.innodb_trx trx inner join information_schema.innodb_locks locks on trx.trx_id=locks.lock_trx_id inner join information_schema.innodb_lock_waits lock_waits on trx.trx_id=lock_waits.requesting_trx_id inner join information_schema.innodb_lock_waits lock_waits on trx.trx_id=lock_waits.blocking_trx_id; 可知，3997事務是等待鎖，而3996是持鎖，所以如果你想讓3997可以繼續工作，有幾種方法： 1，繼續等待3996事務完成 2，完成3996事務 3，殺死3996事務（操作語句為:kill 28,28為事務所屬的執行緒） +---------------------+--------+-----------+---------------------+-----------------------------------+-----------+----------------+-------------------+-----------------+ | trx_mysql_thread_id | trx_id | trx_state | trx_started | trx_query | lock_type | lock_table | requesting_trx_id | blocking_trx_id | +---------------------+--------+-----------+---------------------+-----------------------------------+-----------+----------------+-------------------+-----------------+ | 30 | 3997 | LOCK WAIT | 2019-11-06 05:51:04 | insert into zxydb.t_go select 3,3 | RECORD | `zxydb`.`t_go` | 3997 | 3996 | | 28 | 3996 | RUNNING | 2019-11-06 05:46:18 | NULL | RECORD | `zxydb`.`t_go` | NULL | NULL | +---------------------+--------+-----------+---------------------+-----------------------------------+-----------+----------------+-------------------+-----------------+ 2 rows in set (0.04 sec)
培訓課件
（收費20元）
聯絡方式
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-2663316/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL information_schema庫下的表型別資訊彙總MySqlORM型別
- MySQL使用小技巧（information_schema表空間）MySqlORM
- MYSQL中information_schema簡介MySqlORM
- mysql information_schema innodb_trxMySqlORM
- MySQL通過performance_schema定位未提交事務所執行的SQLMySqlORM
- MySQL 進階：INFORMATION_SCHEMA 簡介MySqlORM
- MySQL中information_schema是什麼MySqlORM
- mysql的mysql.event和information_schema.eventsMySqlORM
- MySQL 事務和鎖MySql
- MySQL事務與鎖MySql
- MySQL事務和鎖MySql
- MYSQL 5.6鎖定使用者MySql
- MySQL information_schema 系統庫介紹MySqlORM
- [MySql技術]MySQL中information_schema是什麼MySqlORM
- MySQL 筆記 - 事務&鎖MySql筆記
- mysql之鎖與事務MySql
- MySQL詳解－－鎖，事務MySql
- mysql事務和鎖InnoDBMySql
- MySQL5.6 PERFORMANCE_SCHEMA 說明MySqlORM
- 【Mysql】MySQL 5.6中如何定位DDL被阻塞的問題MySql
- MySQL入門--事務與鎖MySql
- mysql鎖與事務總結MySql
- MySQL 5.6中如何定位DDL被阻塞的問題MySql
- MySQL預設資料庫之 information_schema庫MySql資料庫ORM
- MySQL information_schema.columns表查詢慢原因分析MySqlORM
- mysql 事務，鎖，隔離機制MySql
- Mysql事務以及加鎖機制MySql
- MySQL（一）：MySQL資料庫事務與鎖MySql資料庫
- information_schema的結構ORM
- MySQL 5.6快速定位不合理索引MySql索引
- Server 層混雜資訊字典表 | 全方位認識 information_schemaServerORM
- Server層統計資訊字典表 | 全方位認識 information_schemaServerORM
- MySQL 核心三劍客 —— 索引、鎖、事務MySql索引
- mysql事務隔離級別和鎖MySql
- Mysql鎖與事務隔離級別MySql
- mysql事務處理與鎖機制MySql
- 十、Redis事務、事務鎖Redis
- mysql伺服器中的mysql與information_schema到底有何作用MySql伺服器ORM