mysql锁等待分析

向右看齐 2022-07-26 06:03 212阅读 0赞

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 w.\`blocking\_trx\_id\`=b.\`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查看，能看到所有产生锁的线程，但无法判断哪个才是根因。

所以，感觉在新的版本中，使用语句查询确实是一个好办法，能够迅速的找到阻塞的原因！