锁等待分析

锁等待分析

1、通过 innodb_lock_waits 分析锁等待

mysql> select *from sys.innodb_lock_waits \G;
*************************** 1. row ***************************
                wait_started: 2020-05-24 13:33:12
                    wait_age: 00:00:11
               wait_age_secs: 11
                locked_table: `test`.`a1`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 1279019
         waiting_trx_started: 2020-05-24 13:33:12
             waiting_trx_age: 00:00:11
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 11
               waiting_query: update a1 set b=2222222222 where id=10001
             waiting_lock_id: 1279019:210:7:2
           waiting_lock_mode: X
             blocking_trx_id: 1279018
                blocking_pid: 2
              blocking_query: NULL
            blocking_lock_id: 1279018:210:7:2
          blocking_lock_mode: X
        blocking_trx_started: 2020-05-24 13:31:37
            blocking_trx_age: 00:01:46
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 2
sql_kill_blocking_connection: KILL 2
1 row in set, 3 warnings (0.01 sec)

输出项说明

# wait_started ：锁定等待开始的时间。
# wait_age ：TIME值,已等待锁多长时间 。
# wait_age_secs ：等待锁定的时间（以秒为单位）。
# locked_table ：锁定表的名称。
# locked_index ：锁定索引的名称。
# locked_type ：等待锁的类型。
# waiting_trx_id ：等待事务的ID。
# waiting_trx_started ：等待事务开始的时间。
# waiting_trx_age ：等待的事务已经等待了多长时间，作为一个 TIME值。
# waiting_trx_rows_locked ：等待的事务锁定的行数。
# waiting_trx_rows_modified ：等待的事务修改的行数。
# waiting_pid ：等待事务的进程列表标识。
# waiting_query ：等待锁的语句。
# waiting_lock_id ：等待锁的ID。
# waiting_lock_mode ：等待锁的模式。
# blocking_trx_id ：正在阻止等待锁的事务的ID。
# blocking_pid ：阻止事务的进程列表ID。
# blocking_query ：阻止事务正在执行的语句。如果发出阻止查询的会话变为空闲，则此字段报告NULL。有关更多信息，请参见在发布会话变为空闲后识别阻塞查询。
# blocking_lock_id :正在阻止等待锁的锁的ID。
# blocking_lock_mode :锁定正在等待的锁定的模式。
# blocking_trx_started :阻止事务开始的时间。
# blocking_trx_age :阻塞事务已执行多长时间，作为一个 TIME值。
# blocking_trx_rows_locked :阻塞事务锁定的行数。
# blocking_trx_rows_modified :阻塞事务修改的行数。
# sql_kill_blocking_query ：该KILL语句执行以杀死阻塞语句。
# sql_kill_blocking_connection ：KILL执行该语句以杀死正在运行的阻塞语句的会话。

2、通过 processlist 查看进程信息

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+-------------------------------------------+
| Id | User | Host      | db   | Command | Time | State    | Info                                      |
+----+------+-----------+------+---------+------+----------+-------------------------------------------+
|  2 | root | localhost | test | Sleep   |  134 |          | NULL                                      |
| 11 | root | localhost | test | Query   |   48 | updating | update a1 set b=2222222222 where id=10001 |
| 12 | root | localhost | test | Query   |    0 | starting | show processlist                          |
+----+------+-----------+------+---------+------+----------+-------------------------------------------+
3 rows in set (0.00 sec)

​ 会话 blocking_pid: 2 阻塞了会话 waiting_pid: 11,提交或者杀掉会话 2 , 会话 11 就可以正常执行。

3、总结

1. 查看 hold 锁的信息

#从 PROCESSLIST 查看 hold 锁的进程信息
select * from sys.session where conn_id in (select blocking_pid from sys.innodb_lock_waits) \G;
或
select * from information_schema.PROCESSLIST where Id in (select blocking_pid from sys.innodb_lock_waits) \G;

#从 INNODB_TRX 查看 hold 锁的事务信息
select * from information_schema.INNODB_TRX where trx_mysql_thread_id in (select blocking_pid from sys.innodb_lock_waits) \G;

2. 查看等待锁的信息

#从 PROCESSLIST 查看等待锁的进程信息
select * from sys.session where conn_id in (select waiting_pid from sys.innodb_lock_waits) \G;
或
select * from information_schema.PROCESSLIST where Id in (select waiting_pid from sys.innodb_lock_waits) \G;

#从 INNODB_TRX 查看等待锁的事务信息
select * from information_schema.INNODB_TRX where trx_mysql_thread_id in (select waiting_pid from sys.innodb_lock_waits) \G;