MYSQL锁表查询

-- 第一个会话执行
drop table if exists test1;
create table test1(
     id     int not null
    ,name   int
    ,primary key(id)
    ,unique key(name)
) engine=innodb
;

begin;
insert into test1 values(2017,827);

-- 第二个会话执行
insert into test1 values(2016,827);

-- 第三个会话执行
-- 5.6 5.7
SELECT
     r.trx_id              as waiting_trx_id
    ,r.trx_mysql_thread_id as waiting_thread
    ,TIMESTAMPDIFF(
         SECOND
        ,r.trx_wait_started
        ,CURRENT_TIMESTAMP
    )                      as wait_time
    ,r.trx_query           as waiting_query
    ,l.lock_table          as waiting_table_lock
    ,b.trx_id              as blocking_trx_id
    ,b.trx_mysql_thread_id as blocking_thread
    ,SUBSTRING(
        p.HOST
        ,1
        ,INSTR(p.HOST, ':') - 1
    )                      as blocking_host
    ,SUBSTRING(p. HOST, INSTR(p. HOST, ':') + 1) as blocking_port
    ,IF (p.COMMAND = 'Sleep', p.TIME, 0)         as idel_in_trx
    ,b.trx_query           as 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
INNER JOIN information_schema.INNODB_LOCKS l 
    ON w.requested_lock_id = l.lock_id
LEFT JOIN information_schema.PROCESSLIST p 
    ON p.ID = b.trx_mysql_thread_id
ORDER BY
    r.trx_wait_started DESC
;

-- mysql8.0已经废弃掉innodb_lock_waits和inndb_locks表，取而代之的是performance_schema库中新增的data_lock_waits,data_locks。
SELECT
     b.trx_mysql_thread_id as blocking_id        -- 导致锁的会话ID
    ,r.trx_mysql_thread_id as waiting_id         -- 被锁的会话ID
    ,l.OBJECT_NAME         as waiting_table_lock -- 被锁的表
    ,b.trx_query           as blocking_query     -- 导致锁的SQL
    ,r.trx_query           as waiting_query
    ,b.trx_id              as blocking_trx_id
    ,r.trx_id              as waiting_trx_id
    ,TIMESTAMPDIFF(
         SECOND
        ,r.trx_wait_started
        ,CURRENT_TIMESTAMP
    )                      as wait_time
    ,SUBSTRING(
        p.HOST
        ,1
        ,INSTR(p.HOST, ':') - 1
    )                      as blocking_host      -- 来源IP
    ,SUBSTRING(p. HOST, INSTR(p. HOST, ':') + 1) as blocking_port -- 来源端口
    ,IF (p.COMMAND = 'Sleep', p.TIME, 0)         as idel_in_trx   -- 空闲时间
-- select count(1) as cnt
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.INNODB_TRX b 
    ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
INNER JOIN information_schema.INNODB_TRX r 
    ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
INNER JOIN performance_schema.data_locks l 
    ON w.requesting_engine_lock_id = l.engine_lock_id
LEFT JOIN information_schema.PROCESSLIST p 
    ON p.ID = b.trx_mysql_thread_id
ORDER BY
    r.trx_wait_started DESC
;