mysql鎖等待查詢分析

mysql鎖等待分析

1、簡(jiǎn)單說明
使用innodb存儲(chǔ)引擎后，mysql有三張表來分析鎖及阻塞的問題，在information_schema下面有三張表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，通過這三張表，可以更簡(jiǎn)單地監(jiān)控當(dāng)前的事務(wù)并分析可能存在的問題。
mysql> show tables like '%INNODB%';
+-----------------------------------------+
| Tables_in_information_schema (%INNODB%) |
+-----------------------------------------+
| INNODB_LOCKS                            |
| INNODB_TRX                              |
| INNODB_LOCK_WAITS                       |
INNODB_TRX表及結(jié)構(gòu)
比較常用的列：
trx_id:InnoDB存儲(chǔ)引擎內(nèi)部唯一的事物ID
trx_status:當(dāng)前事務(wù)的狀態(tài)
trx_requested_lock_id：等待事務(wù)的鎖ID
trx_wait_started：事務(wù)等待的開始時(shí)間
trx_weight：事務(wù)的權(quán)重，反應(yīng)一個(gè)事務(wù)修改和鎖定的行數(shù)，當(dāng)發(fā)現(xiàn)死鎖需要回滾時(shí)，權(quán)重越小的值被回滾
trx_mysql_thread_id：MySQL中的進(jìn)程ID，與show processlist中的ID值相對(duì)應(yīng)
trx_query：事務(wù)運(yùn)行的SQL語句

其余兩個(gè)表字段相對(duì)較少 
INNODB_LOCKS
INNODB_LOCK_WAITS

2、鎖定測(cè)試
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表進(jìn)行測(cè)試，里面的記錄如下：
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)

--將自動(dòng)提交改為手動(dòng)提交
mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| binlog_order_commits           | ON    |
| innodb_api_bk_commit_interval  | 5     |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
5 rows in set (0.00 sec)

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

mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | OFF   |
| binlog_order_commits           | ON    |
| innodb_api_bk_commit_interval  | 5     |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
5 rows in set (0.00 sec)

--將表進(jìn)行加鎖
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)

--重新開一個(gè)窗口執(zhí)行另一個(gè)語句
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

mysql> show full processlist;
+----+-------------+-----------+--------------------+---------+-------+---------------------------------+----------------------------------------------+
| Id | User        | Host      | db                 | Command | Time  | State                           | Info                                         |
+----+-------------+-----------+--------------------+---------+-------+---------------------------------+----------------------------------------------+
|  1 | system user |           | NULL               | Daemon  | 18882 | Waiting for ndbcluster to start | NULL                                         |
|  3 | root        | localhost | test               | Sleep   |  1025 |                                 | NULL                                         |
|  4 | root        | localhost | information_schema | Query   |    45 | Sending data                    | select count(*) from test.mytest1 for update |
|  5 | root        | localhost | information_schema | Query   |     0 | init                            | show full processlist                        |
|  6 | root        | localhost | test               | Sleep   |   212 |                                 | NULL                                         |
+----+-------------+-----------+--------------------+---------+-------+---------------------------------+----------------------------------------------+
5 rows in set (0.00 sec)

由于我這里是兩個(gè)會(huì)話窗口，所以很容易判斷出id 3(thread 3)，為阻塞會(huì)話！
知道會(huì)話后，可以采用kill進(jìn)行查殺
mysql> kill 3;                          --3指的是thread id(processlist中的id)
Query OK, 0 rows affected (0.00 sec)

查殺以后，第二個(gè)會(huì)話迅速將結(jié)果顯示出來

4、總結(jié)
--以前使用processlist時(shí)，顯示太多，根本找不鎖的根本原因，會(huì)話少時(shí)，可以憑直覺查看
--直接使用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)                          指出一個(gè)行鎖
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:                   等待時(shí)間
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   ;;
這一段說的是等待內(nèi)容，包括表的內(nèi)容，指出了表的內(nèi)容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)正執(zhí)有資源
如此去查看，非常耗費(fèi)時(shí)間！
--使用mysqladmin debug查看，能看到所有產(chǎn)生鎖的線程，但無法判斷哪個(gè)才是根因。

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