[Mysql] 使用innodb trx和innodb lock信息表查看锁事物

1、插入表测试数据

select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
+----+------+-------------+

2、创建三个会话，造成锁事物

sessin 1:

BEGIN;
SELECT id FROM aaa FOR UPDATE;
SELECT SLEEP(60);

session 2:

SELECT name FROM aaa FOR UPDATE;

sesion 3:

SELECT telephone FROM aaa FOR UPDATE;

3、使用以下查询来查看正在等待的事务以及阻止它们的事务：

session 4

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                  |
+----------------+----------------+--------------------------------------+-----------------+-----------------+---------------------------------+
| 2488           |             58 | SELECT telephone FROM aaa FOR UPDATE | 2487            |              57 | SELECT name FROM aaa FOR UPDATE |
| 2488           |             58 | SELECT telephone FROM aaa FOR UPDATE | 2486            |              53 | SELECT SLEEP(100)               |
| 2487           |             57 | SELECT name FROM aaa FOR UPDATE      | 2486            |              53 | SELECT SLEEP(100)               |
+----------------+----------------+--------------------------------------+-----------------+-----------------+---------------------------------+

上述sql语句可能太繁琐，也可使用下面语句查询

SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

4、如果查询造成锁事物的会话已经变成空闲，上面查询出来的数据会变为空，可以通过process_id,来查询琐事物

show full processlist;
| 57 | root        | localhost | aaaa | Sleep   |    566 |                                                        | NULL                  |

查询出process_id为57

5、通过processlist_id查询出thread_id

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 57;
+-----------+
| THREAD_ID |
+-----------+
|        79 |
+-----------+

6、根据thread_id查询出来造成锁事物的sql语句

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current 
WHERE THREAD_ID = 79\G
*************************** 1. row ***************************
THREAD_ID: 79
 SQL_TEXT: SELECT name FROM aaa FOR UPDATE
1 row in set (0.00 sec)

7、如果线程执行的最后一个查询不足以确定锁定的原因，则可以查询Performance Schema events_statements_history 表以查看该线程执行的最后10个语句。

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history 
WHERE THREAD_ID = 79 ORDER BY EVENT_ID;
+-----------+---------------------------------+
| THREAD_ID | SQL_TEXT                        |
+-----------+---------------------------------+
|        79 | SELECT name FROM aaa FOR UPDATE |
+-----------+---------------------------------+