怎么使用mysql 5.6 information schema定位事務(wù)鎖信息

這篇文章將為大家詳細講解有關(guān)怎么使用mysql 5.6 information schema定位事務(wù)鎖信息，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

引子
  mysql數(shù)據(jù)庫在運行期間，隨著業(yè)務(wù)體量增加及并發(fā)會話陡升，可能隨時會出現(xiàn)各種性能問題。其中比較常見的一種
  現(xiàn)象，某一天公司業(yè)務(wù)人員或客戶反饋說某個業(yè)務(wù)模板突然卡住了，或者開發(fā)同學(xué)說某個SQL語句不能繼續(xù)運行了。
  mysql 5.6引入的information_schema數(shù)據(jù)庫，可以完美解決上述的問題。它提供一系列的數(shù)據(jù)視圖或表，便于
  診斷及分析數(shù)據(jù)庫的各種各樣的性能問題，對于運維同學(xué)真是大大福利。本文主要介紹information_schema與鎖
  相關(guān)的幾個表，快速定位是哪些會話或事務(wù)導(dǎo)致事務(wù)操作不能持續(xù)。
概念
   information-schema是一個內(nèi)置的數(shù)據(jù)庫，通過一系列的表，比如：鎖方面的表，字符集相關(guān)的表，插件相關(guān)的
   表，進程相關(guān)的表，視圖相關(guān)的表，不一而足。運維人員可以通過不同的表的信息，有助于分析各種各樣的性能問題
   。 當(dāng)然，可以結(jié)合另一個數(shù)據(jù)庫performance_schema數(shù)據(jù)庫，更方便診斷數(shù)據(jù)庫的各種各樣的性能問題甚至故障
   情形。
   
   mysql information-schema官方手冊，請查閱如下鏈接
   https://dev.mysql.com/doc/refman/5.6/en/information-schema.html
   
   mysql鎖相關(guān)官方手冊，請查閱如下鏈接
   https://dev.mysql.com/doc/refman/5.6/en/innodb-locking-transaction-model.html
information-schema實踐
1，數(shù)據(jù)庫版本
[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，顯示事務(wù)及鎖相關(guān)的表
（注：有ORACLE從業(yè)經(jīng)驗的同學(xué)，類似于oracle 動態(tài)性能視圖v$session及l(fā)ocked_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，上述幾個表的含義
---鎖表
（注：鎖是什么，就是你需要某種資源，但此時由人家占著，你需要等待，這就是一種鎖，鎖的目標就是維護數(shù)據(jù)一致性）
mysql> desc innodb_locks;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id     | varchar(81)         | NO   |     |         |       |  鎖編號 
| lock_trx_id | varchar(18)         | NO   |     |         |       |  鎖所屬事務(wù)
| lock_mode   | varchar(32)         | NO   |     |         |       |  鎖模式
| lock_type   | varchar(32)         | NO   |     |         |       |  鎖類型
| lock_table  | varchar(1024)       | NO   |     |         |       |  鎖對應(yīng)表
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |  鎖對應(yīng)索引
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |  鎖空間
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |  鎖對應(yīng)的頁面
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |  鎖對應(yīng)的表記錄
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |
+-------------+---------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
---事務(wù)表
mysql> desc innodb_trx;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field                      | Type                | Null | Key | Default             | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id                     | varchar(18)         | NO   |     |                     |       | 事務(wù)編號 
| trx_state                  | varchar(13)         | NO   |     |                     |       | 事務(wù)狀態(tài)
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       | 事務(wù)開始時間
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       | 事務(wù)請求鎖編號
| trx_wait_started           | datetime            | YES  |     | NULL                |       | 事務(wù)等待開始時間
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       | 事務(wù)權(quán)重
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       | 事務(wù)對應(yīng)的線程
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       | 事務(wù)所屬的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   |     |         |       | 請求鎖事務(wù)編號
| requested_lock_id | varchar(81) | NO   |     |         |       | 請求鎖編號
| blocking_trx_id   | varchar(18) | NO   |     |         |       | 持鎖事務(wù)編號
| blocking_lock_id  | varchar(81) | NO   |     |         |       | 持鎖 鎖編號
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
5，為了模擬事務(wù)，關(guān)閉自動提交
（注：生產(chǎn)系統(tǒng)一定要關(guān)閉，防止不小心在生產(chǎn)系統(tǒng)產(chǎn)生誤操作無法撤回）
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，產(chǎn)生一個事務(wù)
--新開一個登陸會話，不提交
（注：如果一提交，則事務(wù)馬上消失）
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)
--查看事務(wù)表
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 3996                 
                 trx_state: RUNNING              事務(wù)運行狀態(tài)
               trx_started: 2019-11-06 05:46:18  事務(wù)開始的時間
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 25224373
       trx_mysql_thread_id: 28                   事務(wù)所屬的線程，對應(yīng)上述的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             事務(wù)影響的表記錄數(shù)
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ  事務(wù)隔離級別
         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
--由上可見如果沒有競爭資源時，不會產(chǎn)生鎖
（注：產(chǎn)生鎖的前提條件是必須在2個會話以上，當(dāng)然不包括mysql自身產(chǎn)生的bug）
mysql> select * from information_schema.innodb_locks\G;
Empty set (0.04 sec)
ERROR: 
No query specified
--沒有競資源，當(dāng)然也不會產(chǎn)生鎖等待
mysql> select * from information_schema.innodb_lock_waits\G;
Empty set (0.03 sec)
ERROR: 
No query specified
7，再開啟一個新事務(wù)會話
（注：更新上述同一個表的記錄，即會產(chǎn)生鎖等待，因為需要更新同一個表的記錄資源）
mysql> set autocommit=off;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into zxydb.t_go select 3,3;
----可見產(chǎn)生了鎖信息
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
---同時也產(chǎn)生鎖等待信息
mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 3997               請求鎖的事務(wù)id
requested_lock_id: 3997:6:55726:1
  blocking_trx_id: 3996               持鎖的事務(wù)id
 blocking_lock_id: 3996:6:55726:1     
1 row in set (0.03 sec)
ERROR: 
No query specified
8，為了方便監(jiān)控鎖等待的信息，可以編寫下述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事務(wù)是等待鎖，而3996是持鎖，所以如果你想讓3997可以繼續(xù)工作，有幾種方法：
1，繼續(xù)等待3996事務(wù)完成
2，完成3996事務(wù)
3，殺死3996事務(wù)（操作語句為:kill 28,28為事務(wù)所屬的線程）
+---------------------+--------+-----------+---------------------+-----------------------------------+-----------+----------------+-------------------+-----------------+
| 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)

培訓(xùn)課件

（收費20元）

關(guān)于“怎么使用mysql 5.6 information schema定位事務(wù)鎖信息”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。