一条语句查清Mysql innodb 行锁阻塞情况

渔夫数据库笔记 2021-04-18

1260

第一种查询方式(5.7及之前版本)

1、执行此查询语句的前提条件（开启相关监控项）

update setup_instruments set ENABLED='YES',TIMED='YES' where name like 'statement/sql%';
update setup_instruments set ENABLED='NO',TIMED='NO' where name like 'statement/sql%';
update setup_consumers set ENABLED='YES' where name like 'events_statements%';

2、innodb行锁阻塞查询语句

set group_concat_max_len=20480;
SELECT 
    `r`.`trx_wait_started` AS `wait_started`,
    TIMEDIFF(NOW(), `r`.`trx_wait_started`) AS `wait_age`,
    TIMESTAMPDIFF(SECOND,
        `r`.`trx_wait_started`,
        NOW()) AS `wait_age_secs`,
    `rl`.`lock_table` AS `locked_table`,
    `rl`.`lock_index` AS `locked_index`,
    `rl`.`lock_type` AS `locked_type`,
    `r`.`trx_id` AS `waiting_trx_id`,
    `r`.`trx_started` AS `waiting_trx_started`,
    TIMEDIFF(NOW(), `r`.`trx_started`) AS `waiting_trx_age`,
    `r`.`trx_rows_locked` AS `waiting_trx_rows_locked`,
    `r`.`trx_rows_modified` AS `waiting_trx_rows_modified`,
    `r`.`trx_mysql_thread_id` AS `waiting_pid`,
    `r`.`trx_query` AS `waiting_query`,
  (select group_concat(tem_r.sql_text order by EVENT_ID SEPARATOR ';\n') from (select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_history his union select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_current cur) tem_r where tem_r.thread_id=r2.thread_id) waiting_trx_statements,
    `rl`.`lock_id` AS `waiting_lock_id`,
    `rl`.`lock_mode` AS `waiting_lock_mode`,
    `b`.`trx_id` AS `blocking_trx_id`,
    `b`.`trx_mysql_thread_id` AS `blocking_pid`,
    `b`.`trx_query` AS `blocking_query`,
  (select group_concat(tem_b.sql_text order by EVENT_ID SEPARATOR ';\n') from (select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_history his union select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_current cur) tem_b where tem_b.thread_id=b2.thread_id) blocking_trx_statements,
    `bl`.`lock_id` AS `blocking_lock_id`,
    `bl`.`lock_mode` AS `blocking_lock_mode`,
    `b`.`trx_started` AS `blocking_trx_started`,
    TIMEDIFF(NOW(), `b`.`trx_started`) AS `blocking_trx_age`,
    `b`.`trx_rows_locked` AS `blocking_trx_rows_locked`,
    `b`.`trx_rows_modified` AS `blocking_trx_rows_modified`,
    CONCAT('KILL QUERY ', `b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_query`,
    CONCAT('KILL ', `b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_connection`
FROM
    `information_schema`.`innodb_lock_waits` `w`
    JOIN `information_schema`.`innodb_trx` `b` ON `b`.`trx_id` = `w`.`blocking_trx_id`
    JOIN `information_schema`.`innodb_trx` `r` ON `r`.`trx_id` = `w`.`requesting_trx_id`
    JOIN `information_schema`.`innodb_locks` `bl` ON `bl`.`lock_id` = `w`.`blocking_lock_id`
    JOIN `information_schema`.`innodb_locks` `rl` ON `rl`.`lock_id` = `w`.`requested_lock_id`    
    join  `performance_schema`.`threads` b2  on b2.processlist_id=b.trx_mysql_thread_id
    join  `performance_schema`.`threads` r2  on r2.processlist_id=r.trx_mysql_thread_id    
ORDER BY `r`.`trx_wait_started`\G;

第二种查询方式（适用5.7版本）

1、执行此查询语句的前提条件（开启相关监控项）

update setup_instruments set ENABLED='YES',TIMED='YES' where name like 'statement/sql%';
update setup_consumers set ENABLED='YES' where name like 'events_statements%';

2、innodb行锁阻塞查询语句

set group_concat_max_len=20480;
select
k.wait_started,
k.wait_age,
k.wait_age_secs,
k.locked_table,
k.locked_index,
k.locked_type,
k.waiting_trx_id,
k.waiting_trx_started,
k.waiting_trx_age,
k.waiting_trx_rows_locked,
k.waiting_trx_rows_modified,
k.waiting_pid,
k.waiting_query,
(select group_concat(tem_r.sql_text order by EVENT_ID SEPARATOR ';\n') from (select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_history his union select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_current cur) tem_r where tem_r.thread_id=r.thread_id) waiting_trx_statements,
k.waiting_lock_id,
k.waiting_lock_mode,
k.blocking_trx_id,
k.blocking_pid,
k.blocking_query,
(select group_concat(tem_b.sql_text order by EVENT_ID SEPARATOR ';\n') from (select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_history his union select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_current cur) tem_b where tem_b.thread_id=b.thread_id) blocking_trx_statements,
k.blocking_lock_id,
k.blocking_lock_mode,
k.blocking_trx_started,
k.blocking_trx_age,
k.blocking_trx_rows_locked,
k.blocking_trx_rows_modified,
k.sql_kill_blocking_query,
k.sql_kill_blocking_connection
   from sys.innodb_lock_waits k
   join  `performance_schema`.`threads` b  on b.processlist_id=k.blocking_pid
  join  `performance_schema`.`threads` r  on r.processlist_id=k.waiting_pid
ORDER BY k.`wait_started`\G;
NOTE1:在8.0之前只有存在锁等待`information_schema`.`innodb_lock_waits`和`information_schema`.`innodb_locks`才会存在记录
NOTE2:在Mysql 8.0中使用performance_schema.data_locks表和performance_schema.data_lock_waits 表替换了NOTE1中的两张表

Mysql 5.7中行锁阻塞示例

1)测试表结构及记录情况
show create table test_lock;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                          |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_lock | CREATE TABLE `test_lock` (
  `id` int(11) NOT NULL,
  `num` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_num` (`num`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
[root@127.0.0.1][test_shao]> select * from test_lock;
+----+------+---------------+
| id | num  | name          |
+----+------+---------------+
|  4 |    4 | name_8_before |
|  8 |    8 | name_8_before |
| 11 |   11 | name_11       |
+----+------+---------------+
 
 
2)会话1执行如下sql
[root@127.0.0.1][test_shao]> begin;
Query OK, 0 rows affected (0.00 sec)
[root@127.0.0.1][test_shao]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|         1013227 |
+-----------------+
1 row in set (0.00 sec)
[root@127.0.0.1][test_shao]> update test_lock set name='xxxx' where id=4;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0
3)会话2，会话3 分别执行
update test_lock set name='xxxx' where id=4;
 
4)查询阻塞情况
*************************** 1. row ***************************
                wait_started: 2020-03-24 16:08:26
                    wait_age: 00:00:20
               wait_age_secs: 20
                locked_table: `test_shao`.`test_lock`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 108345306
         waiting_trx_started: 2020-03-24 16:08:26
             waiting_trx_age: 00:00:20
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 1013231
               waiting_query: update test_lock set name='xxxx' where id=4
      waiting_trx_statements: select @@version_comment limit 1;
select USER();
update test_lock set name='xxxx' where id=4
             waiting_lock_id: 108345306:216:3:10
           waiting_lock_mode: X
             blocking_trx_id: 108345305
                blocking_pid: 1013227
              blocking_query: NULL
     blocking_trx_statements: select user,host from mysql.user;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>';
SELECT DATABASE();
show tables;
show create table test_lock;
select * from test_lock;
begin;
select connection_id();
update test_lock set name='xxxx' where id=4
            blocking_lock_id: 108345305:216:3:10
          blocking_lock_mode: X
        blocking_trx_started: 2020-03-24 16:08:24
            blocking_trx_age: 00:00:22
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 1013227
sql_kill_blocking_connection: KILL 1013227
*************************** 2. row ***************************
                wait_started: 2020-03-24 16:08:28
                    wait_age: 00:00:18
               wait_age_secs: 18
                locked_table: `test_shao`.`test_lock`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 108345307
         waiting_trx_started: 2020-03-24 16:08:28
             waiting_trx_age: 00:00:18
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 1013232
               waiting_query: update test_lock set name='xxxx' where id=4
      waiting_trx_statements: select @@version_comment limit 1;
select USER();
update test_lock set name='xxxx' where id=4
             waiting_lock_id: 108345307:216:3:10
           waiting_lock_mode: X
             blocking_trx_id: 108345305
                blocking_pid: 1013227
              blocking_query: NULL
     blocking_trx_statements: select user,host from mysql.user;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>';
SELECT DATABASE();
show tables;
show create table test_lock;
select * from test_lock;
begin;
select connection_id();
update test_lock set name='xxxx' where id=4
            blocking_lock_id: 108345305:216:3:10
          blocking_lock_mode: X
        blocking_trx_started: 2020-03-24 16:08:24
            blocking_trx_age: 00:00:22
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 1013227
sql_kill_blocking_connection: KILL 1013227
*************************** 3. row ***************************
                wait_started: 2020-03-24 16:08:28
                    wait_age: 00:00:18
               wait_age_secs: 18
                locked_table: `test_shao`.`test_lock`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 108345307
         waiting_trx_started: 2020-03-24 16:08:28
             waiting_trx_age: 00:00:18
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 1013232
               waiting_query: update test_lock set name='xxxx' where id=4
      waiting_trx_statements: select @@version_comment limit 1;
select USER();
update test_lock set name='xxxx' where id=4
             waiting_lock_id: 108345307:216:3:10
           waiting_lock_mode: X
             blocking_trx_id: 108345306
                blocking_pid: 1013231
              blocking_query: update test_lock set name='xxxx' where id=4
     blocking_trx_statements: select @@version_comment limit 1;
select USER();
update test_lock set name='xxxx' where id=4
            blocking_lock_id: 108345306:216:3:10
          blocking_lock_mode: X
        blocking_trx_started: 2020-03-24 16:08:26
            blocking_trx_age: 00:00:20
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 1013231
sql_kill_blocking_connection: KILL 1013231
3 rows in set, 3 warnings (0.01 sec)
 
ERROR: 
No query specified
NOTE:第一个会话中未提交的事务阻塞了第二个会话中的update，第一个及第二个会话又阻塞了第三个会话的update

扫描二维码

获取更多精彩

渔夫数据库笔记

mysql

文章转载自渔夫数据库笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

一条语句查清Mysql innodb 行锁阻塞情况

第一种查询方式(5.7及之前版本)

第二种查询方式（适用5.7版本）

Mysql 5.7中行锁阻塞示例

评论