概述

很多時候我們在MySQL數據庫中會經常出現事務之間阻塞的問題，也就是阻塞lock,oracle的話實際上之前提供的腳本已經可以很直觀看出阻塞的問題，那麼對於mysql數據庫我們應如何快速查找定位問題根源？

之前分享了innotop工具和show engine innodb status都不能很好的解決我們的需求，所以今天主要基於幾張事務表來寫sql看能不能定位到。

一、環境準備

數據庫：mysql5.7.24 操作系統:centos7.3

1、數據準備

create database t DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
use t;
create table test_blocking(id int primary key, name varchar(12));
insert into test_blocking select 1, 'hwb' from dual;
insert into test_blocking select 2, 'hwb2' from dual;
insert into test_blocking select 3, 'hwb3' from dual;

2、參數設置

為了實驗效果，我們先將參數innodb_lock_wait_timeout設置為100，否則很快就會提示ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

show variables like 'innodb_lock_wait_timeout';
set global innodb_lock_wait_timeout=100 ;

二、被阻塞環境模擬及定位

1、第一個會話

select connection_id() from dual;
set session autocommit=0;
select * from test_blocking where id=1 for update;

2、第二個會話

在第二個連接會話中執行更新腳本

select connection_id() from dual;
update test_blocking set name='kk' where id=1;

3、第三個會話–通過查詢information_schema數據庫下與事務相關的幾個系統表

--查看哪個線程被哪個堵塞,waiting_thread_id代表等待線程，blocking_thread_id代表堵塞線程
SELECT
	r.trx_id waiting_trx_id,
	r.trx_mysql_thread_id waiting_thread_id,
	r.trx_query waiting_query,
	b.trx_id blocking_trx_id,
	b.trx_mysql_thread_id blocking_thread_id,
	b.trx_query blocking_query,
	now( ) - r.TRX_STARTED blocking_time 
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;
 
--查看源頭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;