溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章將為大家詳細講解有關怎么使用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元）
關于“怎么使用mysql 5.6 information schema定位事務鎖信息”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
