mysql鎖等待分析

1、簡單說明
使用innodb儲存引擎後，mysql有三張表來分析鎖及阻塞的問題，在information_schema下面有三張表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，透過這三張表，可以更簡單地監控當前的事務並分析可能存在的問題。
mysql> show tables like '%INNODB%';
+-----------------------------------------+
| Tables_in_information_schema (%INNODB%) |
+-----------------------------------------+
| INNODB_LOCKS                            |
| INNODB_TRX                              |
| INNODB_LOCK_WAITS                       |
INNODB_TRX表及結構
比較常用的列：
trx_id:InnoDB儲存引擎內部唯一的事物ID
trx_status:當前事務的狀態
trx_requested_lock_id：等待事務的鎖ID
trx_wait_started：事務等待的開始時間
trx_weight：事務的權重，反應一個事務修改和鎖定的行數，當發現死鎖需要回滾時，權重越小的值被回滾
trx_mysql_thread_id：MySQL中的程式ID，與show processlist中的ID值相對應
trx_query：事務執行的SQL語句

其餘兩個表欄位相對較少
INNODB_LOCKS
INNODB_LOCK_WAITS

2、鎖定測試
mysql> use test;
Database changed
mysql> create table mytest1 (id int(4),pername char(10),bithday date,telphone char(11));
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mytest1        |
+----------------+
1 row in set, 1 warning (0.00 sec)
--以mytest1表進行測試，裡面的記錄如下：
mysql> select * from mytest1;
+------+---------+------------+----------+
| id   | pername | bithday    | telphone |
+------+---------+------------+----------+
|    1 | Jone    | 1994-01-02 | 11111111 |
|    2 | Tom     | 1994-04-23 | 11214115 |
|    3 | Rose    | 1993-05-02 | 21214719 |
|    4 | Jack    | 1992-07-18 | 41218613 |
|    5 | Block   | 1991-09-21 | 75294651 |
|    6 | Block   | 1990-10-21 | 65364671 |
+------+---------+------------+----------+
6 rows in set (0.00 sec)

mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

--將表進行加鎖
mysql> select * from mytest1 for update;
+------+---------+------------+----------+
| id   | pername | bithday    | telphone |
+------+---------+------------+----------+
|    1 | Jone    | 1994-01-02 | 11111111 |
|    2 | Tom     | 1994-04-23 | 11214115 |
|    3 | Rose    | 1993-05-02 | 21214719 |
|    4 | Jack    | 1992-07-18 | 41218613 |
|    5 | Block   | 1991-09-21 | 75294651 |
|    6 | Block   | 1990-10-21 | 65364671 |
+------+---------+------------+----------+
6 rows in set (0.00 sec)

--重新開一個視窗執行另一個語句
mysql> select count(*) from test.mytest1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3、檢視鎖定情況

mysql> select r.trx_id waiting_trx_id,r.trx_mysql_thread_Id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query
from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b
on b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id;
+----------------+----------------+----------------------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+----------------------------------------------+-----------------+-----------------+----------------+
| 5458 | 4 | select count(*) from test.mytest1 for update | 5450 | 3 | NULL |
+----------------+----------------+----------------------------------------------+-----------------+-----------------+----------------+
1 row in set (0.01 sec)
這裡可以很清楚的看到阻塞的thread 3，被阻塞的thread 4

由於我這裡是兩個會話視窗，所以很容易判斷出id 3(thread 3)，為阻塞會話！
知道會話後，可以採用kill進行查殺
mysql> kill 3; --3指的是thread id(processlist中的id)
Query OK, 0 rows affected (0.00 sec)

查殺以後，第二個會話迅速將結果顯示出來

4、總結
--以前使用processlist時，顯示太多，根本找不鎖的根本原因，會話少時，可以憑直覺檢視
--直接使用show engine innodb status檢視，可以檢視到一些東西，但是不全面，顯示太多
mysql> show engine innodb status;

Per second averages calculated from the last 6 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 22 srv_active, 0 srv_shutdown, 18645 srv_idle
srv_master_thread log flush and writes: 18667
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 25
OS WAIT ARRAY INFO: signal count 25
Mutex spin waits 228, rounds 723, OS waits 3
RW-shared spins 22, rounds 660, OS waits 22
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 3.17 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 5458
Purge done for trx's n:o < 5441 undo n:o < 0 state: running but idle
History list length 26
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 6, OS thread handle 0x7fb3169c1700, query id 403 localhost root init
show engine innodb status
---TRANSACTION 0, not started
MySQL thread id 5, OS thread handle 0x7fb316a02700, query id 393 localhost root cleaning up
---TRANSACTION 5457, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)                          指出一個行鎖
MySQL thread id 4, OS thread handle 0x7fb316a43700, query id 402 localhost root Sending data
select count(*) from test.mytest1 for update
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:                   等待時間
RECORD LOCKS space id 12 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`mytest1` trx id 5457 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 6; hex 000000000300; asc       ;;
1: len 6; hex 00000000152f; asc      /;;
2: len 7; hex a30000015b0110; asc     [ ;;
3: len 4; hex 80000001; asc     ;;
4: len 10; hex 4a6f6e65202020202020; asc Jone      ;;
5: len 3; hex 8f9422; asc   ";;
6: len 11; hex 3131313131313131202020; asc 11111111   ;;
這一段說的是等待內容，包括表的內容，指出了表的內容mytest1
------------------
TABLE LOCK table `test`.`mytest1` trx id 5457 lock mode IX
RECORD LOCKS space id 12 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`mytest1` trx id 5457 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 6; hex 000000000300; asc       ;;
1: len 6; hex 00000000152f; asc      /;;
2: len 7; hex a30000015b0110; asc     [ ;;
3: len 4; hex 80000001; asc     ;;
4: len 10; hex 4a6f6e65202020202020; asc Jone      ;;
5: len 3; hex 8f9422; asc   ";;
6: len 11; hex 3131313131313131202020; asc 11111111   ;;

---TRANSACTION 5450, ACTIVE 813 sec
2 lock struct(s), heap size 360, 7 row lock(s)
MySQL thread id 3, OS thread handle 0x7fb316a84700, query id 388 localhost root cleaning up
TABLE LOCK table `test`.`mytest1` trx id 5450 lock mode IX
RECORD LOCKS space id 12 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`mytest1` trx id 5450 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;
而這一段正好說明了，5450正在鎖定表mytest1，所以可以確定是5450(thread 3)正執有資源
如此去檢視，非常耗費時間！
--使用mysqladmin debug檢視，能看到所有產生鎖的執行緒，但無法判斷哪個才是根因。

所以，感覺在新的版本中，使用語句查詢確實是一個好辦法，能夠迅速的找到阻塞的原因！

mysql鎖等待查詢分析

相關文章