MySQL使用information_schema获取锁表信息

最新推荐文章于 2024-06-24 23:09:23 发布

ZhongWeicheng

最新推荐文章于 2024-06-24 23:09:23 发布

阅读量4.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.youkuaiyun.com/staricqxyz/article/details/10054839

MySQL 专栏收录该内容

41 篇文章

订阅专栏

本文深入探讨了使用SQL查询分析InnoDB锁等待情况的方法，通过查询信息，了解MySQL数据库中并发操作引起的锁等待问题，帮助优化数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

information_schema

Version >= 5.1

SELECT
	r.trx_id waiting_trx_id,
	r.trx_mysql_thread_id waiting_thread,
	TIMESTAMPDIFF(
		SECOND,
		r.trx_wait_started,
		CURRENT_TIMESTAMP
	) wait_time,
	r.trx_query waiting_query,
	l.lock_table waiting_table_lock,
	b.trx_id blocking_trx_id,
	b.trx_mysql_thread_id blocking_thread,
	SUBSTRING(
		p. HOST,
		1,
		INSTR(p. HOST, ':') - 1
	) blocking_host,
	SUBSTRING(p. HOST, INSTR(p. HOST, ':') + 1) blocking_port,

IF (p.COMMAND = 'Sleep', p.TIME, 0) idel_in_trx,
 b.trx_query 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
	wait_time DESC;