mysql锁等待查询分析

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> 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)

--将表进行加锁
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

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)

由于我这里是两个会话窗口，所以很容易判断出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查看，能看到所有产生锁的线程，但无法判断哪个才是根因。

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