mysql 找出谁持有全局读锁

数据库版本 5.7.17-log
开始三个会话

会话1

-- 全局读锁
FLUSH TABLE WITH READ LOCK;

-- 查询当前会话id  等同于 show processlist 结果的id
SELECT CONNECTION_ID();

-- 释放锁
UNLOCK TABLES;

会话2

SELECT CONNECTION_ID();
SELECT * FROM sales WHERE id = 1;

-- 会被阻塞
UPDATE sales SET DATE = NOW() WHERE id = 1;

会话3

SELECT * FROM information_schema.innodb_locks;

SELECT * FROM information_schema.innodb_lock_waits;

SELECT * FROM information_schema.innodb_trx;

SHOW ENGINE INNODB STATUS;
-- 上面均没有锁信息

-- 可以看到 id=会话2 对应的CONNECTION_ID(),state=Waiting for global read lock，表示正在等待全局读锁
SHOW  PROCESSLIST;

SHOW PROCESSLIST 结果

-- 通过metadata_locks表里排查谁持有全局读锁，全局读锁在该表中同城记录着同一个会话的OBJECT_TYPE为global和commit，LOCK_TYPE都为SHARED
SELECT * FROM performance_schema.`metadata_locks`  WHERE owner_thread_id != sys.ps_thread_id(CONNECTION_ID());

metadata_locks 查询结果

metadata_locks为空解决办法

-- 如果metadata_locks记录为空，则需手动开启对应监控
SELECT * FROM  performance_schema.setup_instruments WHERE NAME = 'wait/lock/metadata/sql/mdl';

UPDATE  performance_schema.setup_instruments SET enabled = 'YES' ,timed='YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

sys.ps_thread_id

SHOW CREATE FUNCTION sys.ps_thread_id; 查看具体的定义

SHOW CREATE FUNCTION sys.ps_thread_id; 

-- 函数核心内容
SELECT THREAD_ID FROM `performance_schema`.`threads` WHERE PROCESSLIST_ID = IFNULL(入参, CONNECTION_ID());