概述

很多时候我们在关系型数据库数据库中会经常出现事务之间阻塞的问题，也就是阻塞锁，先知的话实际上之前提供的脚本已经可以很直观看出阻塞的问题，那么对于关系型数据库数据库我们应如何快速查找定位问题根源？

之前分享了innotop工具和显示引擎innodb状态都不能很好的解决我们的需求，所以今天主要基于几张事务表来写结构化查询语言看能不能定位到。

一、环境准备

数据库：mysql5.7.24操作系统：centos7.3

1、数据准备

创建数据库t DEFAULT CHARSET utf 8 COLLATE utf 8 _ general _ ci；

使用t；

创建表测试阻塞(id int主键，名称varchar(12))；

插入测试阻塞选择1,从双重的中选择“hwb”；

插入测试阻塞选择2,从双重的中选择“hwb 2”；

插入测试阻塞选择3,从双重的中选择“hwb 3”；

2、参数设置

为了实验效果，我们先将参数innodb_lock_wait_timeout设置为100,否则很快就会提示错误1205 (HY000):超过锁定等待超时；尝试重新启动事务

显示像" innodb_lock_wait_timeout "这样的变量；

设置全局innodb _ lock _ wait _ timeout=100

二、被阻塞环境模拟及定位

1、第一个会话

从双重的中选择connection _ id()；

设置会话自动提交=0;

从测试阻塞中选择*进行更新，其中id=1；

2、第二个会话

在第二个连接会话中执行更新脚本

从双重的中选择connection _ id()；

更新测试_阻塞集名称='kk '，其中id=1；

3、第三个会话通过查询信息模式数据库下与事务相关的几个系统表

-查看哪个线程被哪个堵塞，等待_线程_id代表等待线程，阻塞_线程_id代表堵塞线程

挑选

r.trx_id waiting_trx_id，

r。Trx _ MySQL _ thread _ id waiting _ thread _ id，

r.trx _查询等待_查询，

b.trx_id阻塞_trx_id，

b。Trx _ MySQL _ thread _ id blocking _ thread _ id，

b.trx _查询阻塞_查询，

现在()- r.TRX_STARTED阻塞时间

从

信息模式。innodb _ lock _ waits w

INNER JOIN信息_ schema。innodb _ Trx b ON b . Trx _ id=w . blocking _ Trx _ id

内部连接

information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id; --查看源头sql SELECT a.sql_text, c.id, d.trx_started, b.processlist_user, b.processlist_host FROM PERFORMANCE_SCHEMA.events_statements_current a JOIN PERFORMANCE_SCHEMA.threads b ON a.thread_id = b.thread_id JOIN information_schema.PROCESSLIST c ON b.processlist_id = c.id JOIN information_schema.innodb_trx d ON c.id = d.trx_mysql_thread_id WHERE c.id = 304192 ORDER BY d.trx_started;

如下截图所示，第一个SQL语句能够查到线程304193被线程304192阻塞了， 被阻塞的SQL语句为“update test_blocking set name=’kk’ where id=1;”, 能够查到被阻塞了多长时间，但是无法查到源头SQL语句。此时就需要第二个SQL语句登场，找到源头语句。

---

ps:附一段查看阻塞线程更多信息的sql

SELECT
	p2.HOST Blockedhost,
	p2.USER BlockedUser,
	r.trx_id BlockedTrxId,
	r.trx_mysql_thread_id BlockedThreadId,
	TIMESTAMPDIFF( SECOND, r.trx_wait_started, CURRENT_TIMESTAMP ) WaitTime,
	r.trx_query BlockedQuery,
	l.lock_table BlockedTable,
	m.lock_mode BlockedLockMode,
	m.lock_type BlockedLockType,
	m.lock_index BlockedLockIndex,
	m.lock_space BlockedLockSpace,
	m.lock_page BlockedLockPage,
	m.lock_rec BlockedLockRec,
	m.lock_data BlockedLockData,
	p.HOST blocking_host,
	p.USER blocking_user,
	b.trx_id BlockingTrxid,
	b.trx_mysql_thread_id BlockingThreadId,
	b.trx_query BlockingQuery,
	l.lock_mode BlockingLockMode,
	l.lock_type BlockingLockType,
	l.lock_index BlockingLockIndex,
	l.lock_space BlockingLockSpace,
	l.lock_page BlockingLockPage,
	l.lock_rec BlockingLockRec,
	l.lock_data BlockingLockData,
IF
	( p.COMMAND = 'Sleep', CONCAT( p.TIME, ' seconds' ), 0 ) idel_in_trx 
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.blocking_lock_id = l.lock_id 
	AND l.lock_trx_id = b.trx_id
	INNER JOIN information_schema.INNODB_LOCKS m ON m.lock_id = w.requested_lock_id AND m.lock_trx_id = r.trx_id
	INNER JOIN information_schema.PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
	INNER JOIN information_schema.PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id 
ORDER BY
	WaitTime DESC G;

---

这里不要太天真的认为第二个SQL语句能够获取所有场景下的阻塞源头SQL语句，实际业务场景，会话可能在执行一个存储过程或复杂的业务，有可能它执行完阻塞源头SQL后，继续在执行其它SQL语句，此时，你抓取的是这个连接会话最后执行的SQL语句。