mysql鎖等待查詢分析

MySQL鎖等待分析

創(chuàng)新互聯(lián)建站主要業(yè)務(wù)有網(wǎng)站營銷策劃、網(wǎng)站設(shè)計、網(wǎng)站建設(shè)、微信公眾號開發(fā)、微信小程序、H5建站、程序開發(fā)等業(yè)務(wù)。一次合作終身朋友，是我們奉行的宗旨；我們不僅僅把客戶當客戶，還把客戶視為我們的合作伙伴，在開展業(yè)務(wù)的過程中，公司還積累了豐富的行業(yè)經(jīng)驗、網(wǎng)絡(luò)營銷推廣資源和合作伙伴關(guān)系資源，并逐漸建立起規(guī)范的客戶服務(wù)和保障體系。

1、簡單說明
使用innodb存儲引擎后，mysql有三張表來分析鎖及阻塞的問題，在information_schema下面有三張表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，通過這三張表，可以更簡單地監(jiān)控當前的事務(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存儲引擎內(nèi)部唯一的事物ID
trx_status:當前事務(wù)的狀態(tài)
trx_requested_lock_id：等待事務(wù)的鎖ID
trx_wait_started：事務(wù)等待的開始時間
trx_weight：事務(wù)的權(quán)重，反應一個事務(wù)修改和鎖定的行數(shù)，當發(fā)現(xiàn)死鎖需要回滾時，權(quán)重越小的值被回滾
trx_mysql_thread_id：MySQL中的進程ID，與show processlist中的ID值相對應
trx_query：事務(wù)運行的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)

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

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

4、總結(jié)
--以前使用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   ;;
這一段說的是等待內(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í)有資源
如此去查看，非常耗費時間！
--使用mysqladmin debug查看，能看到所有產(chǎn)生鎖的線程，但無法判斷哪個才是根因。

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

網(wǎng)頁名稱：mysql鎖等待查詢分析
本文來源：http://muchs.cn/article22/ghsijc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、手機網(wǎng)站建設(shè)、面包屑導航、定制開發(fā)、自適應網(wǎng)站、App設(shè)計

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容