第一种查询方式(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;selectk.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_pidORDER 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: 03)会话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 '';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 1013227sql_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 '';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 1013227sql_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 1013231sql_kill_blocking_connection: KILL 10132313 rows in set, 3 warnings (0.01 sec) ERROR: No query specifiedNOTE:第一个会话中未提交的事务阻塞了第二个会话中的