MySQLwwwhj8828com18088045700鎖分析和監控

1597538646291241發表於2018-10-04
MySql

通常在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
1. row **
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）
1 row inset (0.00 sec)

上面我們已經知道了事務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
1. row **

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
lock_table: 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的數值，說明有鎖等待現象，然後點選小圓點，即可以定位到相關鎖資訊，是不是更方便了。


相關文章