通过查询日志定位持有锁的SQL语句
MYSQL 版本：
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.27-log |
+------------+
1 row in set (0.00 sec)
1. 启用查询日志
mysql> SET GLOBAL general_log=1;
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like 'general_log%';
+------------------+--------------------------------------------------+
| Variable_name | Value |
+------------------+--------------------------------------------------+
| general_log | ON |
| general_log_file | /app/oracle/oradata2/mysql-5.5.37/data/oradb.log |
+------------------+--------------------------------------------------+
2 rows in set (0.00 sec)
2. 查看MYSQL 线程信息
--MYSQL 线程1
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 1 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
--MYSQL 线程2
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 1 | root | localhost | NULL | Sleep | 145 | | NULL |
| 2 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
--MYSQL 线程3
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 1 | root | localhost | NULL | Sleep | 189 | | NULL |
| 2 | root | localhost | NULL | Sleep | 44 | | NULL |
| 3 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)
--MSYQL 线程4
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 1 | root | localhost | NULL | Sleep | 238 | | NULL |
| 2 | root | localhost | NULL | Sleep | 93 | | NULL |
| 3 | root | localhost | NULL | Sleep | 49 | | NULL |
| 4 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
4 rows in set (0.00 sec)
--MYSQL 查询日志
[root@oradb data]# tail -f oradb.log
/app/oracle/oradata2/mysql-5.6.27/bin/mysqld, Version: 5.6.27-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
161116 10:48:57 1 Query show variables like 'general_log%'
161116 10:50:07 1 Query show full processlist
161116 10:52:24 2 Connect root@localhost on
2 Query select @@version_comment limit 1
161116 10:52:32 2 Query show full processlist
161116 10:53:09 3 Connect root@localhost on
3 Query select @@version_comment limit 1
161116 10:53:16 3 Query show full processlist
161116 10:53:57 4 Connect root@localhost on
4 Query select @@version_comment limit 1
161116 10:54:05 4 Query show full processlist
ID：表示MYSQL 线程ID
3. 使用查询日志找出持有锁造成阻塞的SQL
--MYSQL 线程1 执行更新，并且不提交事务持续持有锁
use test;
START TRANSACTION;
UPDATE t1 SET NAME='test11' WHERE id<11;
--MYSQL 线程2，被阻塞
use test;
START TRANSACTION;
UPDATE t1 SET NAME='test11' WHERE id<10;
--MYSQL 线程3，被阻塞
use test;
START TRANSACTION;
UPDATE t1 SET NAME='test11' WHERE id<9;
--查看锁阻塞信息
SELECT CONCAT('thread ',b.trx_mysql_thread_id,' from ',p.host) AS who_blocks,
IF(p.command="Sleep",p.time,0) AS idle_in_trx,
MAX(TIMESTAMPDIFF(SECOND,r.trx_wait_started,NOW())) AS max_wait_time,
COUNT(*) AS num_waiters
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b ON b.trx_id=w.`blocking_trx_id`
INNER JOIN information_schema.`INNODB_TRX` AS r ON r.`trx_id`= w.`requesting_trx_id`
LEFT JOIN information_schema.`PROCESSLIST` AS p ON p.id= b.`trx_mysql_thread_id`
GROUP BY who_blocks ORDER BY num_waiters DESC\G
*************************** 1. row ***************************
who_blocks: thread 1 from localhost
idle_in_trx: 7
max_wait_time: 4
num_waiters: 2
*************************** 2. row ***************************
who_blocks: thread 2 from localhost
idle_in_trx: 0
max_wait_time: 2
num_waiters: 1
2 rows in set (0.11 sec)
我们看到 MSYQL 线程1 阻塞了 2 个 线程。
SELECT r.trx_id AS waiting_trx_id,r.`trx_mysql_thread_id` AS waiting_thread,
TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time,
r.`trx_query` AS waiting_query,l.`lock_table` AS waiting_table_lock,
b.trx_id AS blocking_trx_id,b.`trx_mysql_thread_id` AS blocking_thread,
SUBSTRING(p.host,1,INSTR(p.host,':')-1) AS blocking_host,
SUBSTRING(p.host,INSTR(p.host,':')+1) AS blocking_port,
IF(p.command="Sleep",p.time,0) AS idle_in_trx,
b.`trx_query` AS blocking_query
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b ON b.trx_id=w.blocking_trx_id
INNER JOIN information_schema.`INNODB_TRX` AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.`INNODB_LOCKS` AS l ON w.requested_lock_id=l.lock_id
LEFT JOIN information_schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id
ORDER BY wait_time DESC
*************************** 1. row ***************************
waiting_trx_id: 17159
waiting_thread: 2
wait_time: 3
waiting_query: UPDATE t1 SET NAME='test11' WHERE id<10
waiting_table_lock: `test`.`t1`
blocking_trx_id: 17158
blocking_thread: 1
blocking_host:
blocking_port: localhost
idle_in_trx: 456
blocking_query: NULL
*************************** 2. row ***************************
waiting_trx_id: 17160
waiting_thread: 3
wait_time: 2
waiting_query: UPDATE t1 SET NAME='test11' WHERE id<9
waiting_table_lock: `test`.`t1`
blocking_trx_id: 17158
blocking_thread: 1
blocking_host:
blocking_port: localhost
idle_in_trx: 456
blocking_query: NULL
*************************** 3. row ***************************
waiting_trx_id: 17160
waiting_thread: 3
wait_time: 2
waiting_query: UPDATE t1 SET NAME='test11' WHERE id<9
waiting_table_lock: `test`.`t1`
blocking_trx_id: 17159
blocking_thread: 2
blocking_host:
blocking_port: localhost
idle_in_trx: 0
blocking_query: UPDATE t1 SET NAME='test11' WHERE id<10
3 rows in set (0.01 sec)
等待 T1 表上的锁
--查询日志
161116 11:00:30 1 Query SELECT DATABASE()
1 Init DB test
1 Query show databases
1 Query show tables
1 Field List t1
1 Query START TRANSACTION
161116 11:00:45 2 Query SELECT DATABASE()
2 Init DB test
2 Query show databases
2 Query show tables
2 Field List t1
2 Query START TRANSACTION
161116 11:00:51 3 Query SELECT DATABASE()
3 Init DB test
3 Query show databases
3 Query show tables
3 Field List t1
3 Query START TRANSACTION
161116 11:00:54 4 Query SELECT DATABASE()
4 Init DB test
4 Query show databases
4 Query show tables
4 Field List t1
4 Query START TRANSACTION
161116 11:01:53 1 Query UPDATE t1 SET NAME='test11' WHERE id<11
161116 11:01:56 2 Query UPDATE t1 SET NAME='test11' WHERE id<10
161116 11:01:58 3 Query UPDATE t1 SET NAME='test11' WHERE id<9
161116 11:02:00 4 Query SELECT CONCAT('thread ',b.trx_mysql_thread_id,' from ',p.host) AS who_blocks,
IF(p.command="Sleep",p.time,0) AS idle_in_trx,
MAX(TIMESTAMPDIFF(SECOND,r.trx_wait_started,NOW())) AS max_wait_time,
COUNT(*) AS num_waiters
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b ON b.trx_id=w.`blocking_trx_id`
INNER JOIN information_schema.`INNODB_TRX` AS r ON r.`trx_id`= w.`requesting_trx_id`
LEFT JOIN information_schema.`PROCESSLIST` AS p ON p.id= b.`trx_mysql_thread_id`
GROUP BY who_blocks ORDER BY num_waiters DESC
通过查询日志结合上面查到的锁阻塞信息可以判断是 MSYQL 线程1 执行的UPDATE t1 SET NAME='test11' WHERE id<11
语句持有锁没提交事务，阻塞了线程2、3。
从查询日志的时间上可以看出来，线程1的SQL比线程2和3的SQL先执行。
