快速定位MySQL锁等待问题

演示MySQL版本为MySQL5.7.22

​

通过sys.innodb_lock_waits 视图 表查看Innodb 当前事务锁等待信息，默认是按照发生锁等待的开始时间升序排列的;wait_started字段即innodb_trx 表的trx_wait_started字段

sys.innodb_lock_waits视图数据来源:

登录后复制

information_schema.innodb_trx, information_schema.innodb_locks ,information_schema.innodb_lock_waits
1.

提示:

mysql8.0之后的版本，sys.innodb_lock_waits 视图信息来源自information_schema.innodb_trx, performance_schema.data_locks, performance_schema.data_wait_lock

查看sys下的视图innodb_lock_waits:

登录后复制

'tidb03' root@localhost 16:46:03 test001>select TABLE_SCHEMA , TABLE_NAME from information_schema.TABLES where table_type='view' and TABLE_SCHEMA ='sys' and TABLE_NAME='innodb_lock_waits';  
+--------------+-------------------+

| TABLE_SCHEMA | TABLE_NAME        |

+--------------+-------------------+

| sys          | innodb_lock_waits |

+--------------+-------------------+

1 row in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

会话一:​ 发起一个未提交的update事务

登录后复制

'tidb03' root@localhost 16:03:25 test001>select * from test001;

+----+----------+----------+---------------------+

| id | username | password | create_time         |

+----+----------+----------+---------------------+

|  1 | 小花     | abc123   | 2021-09-19 13:31:07 |

+----+----------+----------+---------------------+

1 row in set (0.00 sec)



'tidb03' root@localhost 16:04:21 test001>begin;

Query OK, 0 rows affected (0.00 sec)



'tidb03' root@localhost 16:03:34 test001>update test001 set username='小白' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

会话二：update更新同一条记录

登录后复制

'tidb03' root@localhost 16:04:04 test001>update test001 set username='大米' where id=1;

Query OK, 1 row affected (9 min 32.98 sec)

Rows matched: 1  Changed: 1  Warnings: 0
1.
2.
3.
4.
5.

会话三: 查看sys.innodb_lock_waits视图获取锁等待信息

登录后复制

'tidb03' root@localhost 16:06:24 sys>select * from sys.innodb_lock_waits\G

*************************** 1. row ***************************

                wait_started: 2021-09-20 16:04:20  ##发生锁等待的开始时间

                    wait_age: 00:02:25             ##锁已经等待了多久了，是一个时间格式的值

               wait_age_secs: 145                  ##锁已经等待了145s,是一个整型的数值，此字段是mysql5.7.9版本中新增

                locked_table: `test001`.`test001`  ##锁等待的表名称

                locked_index: PRIMARY              ##锁等待的索引名称

                 locked_type: RECORD               ##锁等待的类型

              waiting_trx_id: 47819075             ## 锁等待的事务id

         waiting_trx_started: 2021-09-20 16:04:20  ##锁等待的开始时间  
             waiting_trx_age: 00:02:25             ## 发生锁等待的事务的总的等待时间

     waiting_trx_rows_locked: 1                    ##发生锁等待事务已经锁定的行数（如果是复杂的事务会累计）

   waiting_trx_rows_modified: 0                    ##发生锁等待事务已经修改的行数（如果是复杂的事务会累计）

                 waiting_pid: 204862               ##发生锁等待事务的processlist id 号

               waiting_query: update test001 set username='大米' where id=1   ##发生锁等待事务的sql语句文本

             waiting_lock_id: 47819075:693:3:2     ##  发生锁等待的锁id

           waiting_lock_mode: X                    ##发生锁等待的锁模式

             blocking_trx_id: 47819074             ##持有锁的事务id

                blocking_pid: 204829               ##持有锁的事务的processlist id号  
              blocking_query: NULL                 ##持有锁的事务的sql文本

            blocking_lock_id: 47819074:693:3:2     ##持有锁的锁id

          blocking_lock_mode: X                     ##持有锁的锁模式

        blocking_trx_started: 2021-09-20 16:03:34  ##持有锁的事务的开始时间

            blocking_trx_age: 00:03:11              ##持有锁的事务已经执行了多长时间

    blocking_trx_rows_locked: 1                     ##持有锁的事务锁定的行数

  blocking_trx_rows_modified: 1                     ##持有锁的事务需要修海的行数

     sql_kill_blocking_query: KILL QUERY 204829     ##执行KILL 语句来杀死持有锁的查询语句（而不是终止会话）此字段MySQL5.7.9新增

sql_kill_blocking_connection: KILL 204829           ##执行KILL 语句来来终止持有锁的语句的会话，，此字段MySQL5.7.9新增

1 row in set, 3 warnings (0.01 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.