MySQLwwwhj8828com18088045700鎖分析和監控

通常在MySQL的管理和監控中，Active Session（活動會話）是監控名額中的一個很重要的名額，通過活動會話監控，可以很清楚的了解到資料庫目前是否有SQL堆積，是否處于非常繁忙的狀态。那麼除了活動會話之外，還有哪些名額是非常重要的呢，本文就來給大家介紹下MySQL裡面另外幾個重要名額，事務和鎖資訊，鎖等待的監控。

我們知道事務和鎖是資料庫中最最核心的内容，有了事務和鎖，才保證了資料的ACID特性，上面說到的活動會話監控，可以反映出資料庫的一個健康狀态，但是如果監控到事務和鎖，那麼會對資料庫的運作狀态有更加全面的認識，在資料庫出現異常時也可以很快定位到一些問題。比如業務設計開發同學開啟了事務但是忘了送出，或者事務送出時間過長，都會導緻一些資料庫的問題産生，嚴重時會資料庫故障。下面就如何檢視和監控事務、鎖資訊做個簡單介紹。

大多數時候我們通過執行show engine innodb status來檢視和監控資料庫的鎖資訊，其實還有更簡單的方式，MySQL将事務和鎖資訊記錄在了information_schema資料庫中，我們隻需要查詢即可。

INNODB_TRX

記錄INNODB未送出事務資訊

INNODB_LOCKS

記錄INNODB鎖資訊，當出現鎖等待時才有資料

INNODB_LOCK_WAITS

記錄鎖等待資訊，關聯INNODB_LOCKS查詢。

我們通過執行個體分析來說明如何監控事務和鎖，首先開啟事務T1，執行update：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set name='xxxx' where id=10;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

然後查詢INNODB_TRX表，可以看到如下資訊，表示有1條事務當然沒有送出，這個事務就是上面T1沒有送出的事務。

mysql> use information_schema

mysql>SELECT * FROM INNODB_TRXG

1. row **

trx_id: 36076063  （事務ID）
             trx_state: RUNNING （事務正在運作）
           trx_started: 2018-09-08 22:35:32（事務開始時間）
 trx_requested_lock_id: NULL
      trx_wait_started: NULL
            trx_weight: 3
   trx_mysql_thread_id: 882965 （MySQL線程ID）
             trx_query: NULL （執行的SQL語句）
   trx_operation_state: NULL
     trx_tables_in_use: 0
     trx_tables_locked: 0
      trx_lock_structs: 2
 trx_lock_memory_bytes: 360
       trx_rows_locked: 1 （鎖定了1行索引記錄）
     trx_rows_modified: 1           

trx_concurrency_tickets: 0

trx_isolation_level: READ COMMITTED （目前事務隔離級别）
     trx_unique_checks: 1 （唯一性檢測，因為是UK鎖）
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 inset (0.00 sec)

然後我們開啟事務T2：

mysql>begin;

QueryOK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id<=4 lock in share mode;

ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在事務T2執行過程中我們來監控鎖資訊，首先來查詢INNODB_LOCK_WAITS資料表，可以看到上面T1，T2兩個事務已經産生了鎖等待。

mysql>SELECT * FROM INNODB_LOCK_WAITSG

requesting_trx_id:36076064 （T2請求的事務ID）

requested_lock_id:36076064:69:3:5 （T2請求的鎖ID）

blocking_trx_id: 36076063 （T1阻塞的事務ID）

blocking_lock_id: 36076063:69:3:5 （T1阻塞的鎖ID）

上面我們已經知道了事務T2在執行過程中被事務T1的鎖阻塞住了，然後我們就可以通過查詢INNODB_LOCKS查詢看到的鎖詳細資訊，具體如下所示，可以看到上面的事務T1（36076063）對t1表加了X模式的PK鎖，鎖類型為Record Lock,鎖定了1行資料,鎖定的位置為69表空間的第3個頁面的第5行記錄，鎖定記錄為10，因為是PK更新，是以這裡的lock_data: 10就是id=10的這行記錄的PK被加鎖了。再來看事務T2（36076064），在請求id=10這個鎖的時候無法擷取到鎖，導緻了鎖等待。

mysql>SELECT * FROM INNODB_LOCKSG

lock_id: 36076064:69:3:5 （鎖ID）           

lock_trx_id:36076064 （事務ID）

lock_mode: S （鎖模式）

lock_type: RECORD （鎖類型）

lock_table:

test

.

t1

（鎖了哪個表）

lock_index: PRIMARY （鎖定的索引類型）

lock_space: 69 （表空間位置）

lock_page: 3 （頁位置）

lock_rec: 5 （記錄位置）

lock_data: 10 （哪個資料被鎖了，如果是PK，這個值就是PK值）

2. row **

lock_id: 36076063:69:3:5           

lock_trx_id:36076063

lock_mode: X

lock_type: RECORD

test

t1

lock_index: PRIMARY

lock_space: 69

lock_page: 3

lock_rec: 5

lock_data: 10

2 rowsin set (0.00 sec)

現在我們知道了如何定位和查詢沒有送出的事務，以及鎖等待資訊，隻需要将上面的SQL定時采集告警即可很容易的實作事務和鎖的監控了。最近我自己也寫了一個demo，通過上面三個SQL監控了事務和鎖的資訊。

可以看到上面監控裡面有大于0的數值，說明有鎖等待現象，然後點選小圓點，即可以定位到相關鎖資訊，是不是更友善了。