在MySQL数据库中出现阻塞问题。如何快速查找和定位问题的根源？在开始实验之前，我们先梳理一下有哪些工具或者命令可以用来检查MySQL的阻塞情况。此外，我们还需要一一比较它们的优劣，因为有些命令在实际环境中可能并不适用。showengineinnodbstatusInnotoptoolsINNODB_TRX等系统表下面我们理论结合实践，通过实验总结一下这个问题。首先搭建测试环境，数据库测试环境为（5.7.21MySQLCommunityServer和5.6.20-enterprise-commercial，两个测试环境我都测试验证过）12));QueryOK,0rowsaffected(0.05sec)mysql>insertintotest_blocking->select1,'kerry'fromdual;QueryOK,1rowaffected(0.00sec)Records:1Duplicates:0Warnings:0mysql>insertintotest_blocking->select2,'jimmy'fromdual;QueryOK,1rowaffected(0.00sec)Records:1Duplicates:0Warnings:0mysql>insertintotest_blocking->select3,'kkk'fromdual;QueryOK,1rowaffected(0.00sec)Records:1Duplicates:0Warnings:0测试环境数据准备好后，开始实验，对于实验效果，我们先设置参数innodb_lock_wait_timeout为100。mysql>showvariablelike'innodb_lock_wait_timeout';+------------------------+------+|Variable_name|Value|+-----------------------+--------+|innodb_lock_wait_timeout|50|+--------------------------+--------+1rowinset(0.00sec)mysql>setglobalinnodb_lock_wait_timeout=100;QueryOK,0rowsaffected(0.00sec)mysql>selectconnection_id()来自双重；+----------------+|connection_id()|+----------------+|8|+-----------------+1rowinset(0.00sec)mysql>setsessionautocommit=0;QueryOK,0rowsaffected(0.00sec)mysql>select*fromtest_blockingwhereid=1forupdate;+---+-------+|id|name|+----+--------+|1|kerry|+----+--------+1rowinset(0.00sec)然后在执行第二个连接会话中的更新脚本构造阻塞案例mysql>selectconnection_id()fromdual;+----------------+|connection_id()|+---------------+|9|+----------------+1rowinset(0.00sec)mysql>updatetest_blockingsetname='kk'whereid=1;在第三个连接会话中执行如下命令查看TRANSACTIONS相关信息：mysql>showengineinnodbstatusG;使用showengineinnodbstatus命令后，可以查看输出TRANSACTIONS部分信息，如上截图所示，找到类似TRXHASBEENWATING...部分的信息，通过那部分信息，我们可以看到sql语句updatetest_blockingsetname='kk'whereid=1被阻塞14秒，一直等待获取XLockTRANSACTIONS------------Trxidcounter148281#下一个事务IDPurgedonefortrx'sn:o<148273undon:o<0state:runningbutidleHistorylistlength552LISTOFTRANSACTIONSFOREACHSESSION:---TRANSACTION0,notstartedMySQLthreadid15,OSthreadhandle0x4cc64940,queryid261localhostrootcleaningup---TRANSACTION0,notstartedMySQLthreadid14,OSthreadhandle0x4cbe2940,queryid278localhostrootinitshowengineinnodbstatus---TRANSACTION148280,ACTIVE24sec2lockstruct(s),heapsize360,1rowlock(s)MySQLthreadid8,OSthreadhandle0x4cba1940,queryid276localhostrootcleaningup---TRANSACTION148279,ACTIVE313secstartingindexreadmysqltablesinuse1,locked1LOCKWAIT2lockstruct(s),heapsize360,1rowlock(s)MySQLthreadid9,OSthreadhandle0x4cc23940,queryid277localhostrootupdating#线程ID为9，操作系统线程句柄为0x4cc23940，查询ID为277，账户为rootUPDATE操作updatetest_blockingsetname='kk'whereid=1#具体SQL语句------TRXHASBEENWAITING14SECFORTHISLOCKTOBEGRANTED:#TRXwaiting已经过了14秒锁被授予RECORDLOCKSspaceid337pageno3nbits72index`PRIMARY`oftable`MyDB`.`test_blocking`trxid148279lock_modeXlocksrecbutnotgapwaiting#在spaceid=337（test_blocking表的表空间），pageno=3的页面，表test_blocking上的主键索引正在等待XlockRecordlock,heapno2PHYSICALRECORD:n_fields4;compactformat;infobits00:len4;hex80000;001;升序；#第一个字段为主键，系统长度为4，取值为11:len6;hex000000024322;ascC";;#该字段为6字节的交易id，表示最后更新的交易id（对应的十进制为148258)2:len7;hex9a000001f20110;asc;;#这个字段是mvcc3的7字节回滚指针:len5;hex6b65727279;asckerry;;#这个字段代表这条记录的第二条记录字段，长度为5、值为kerry（如果表有多个字段，这里有记录）mysql>select*frominformation_schema.INNODB_SYS_DATAFILESwherespace=337;+--------+----------------------------+|SPACE|PATH|+------+--------------------------+|337|./MyDB/test_blocking.ibd|+--------+------------------------+1rowinset(0.00sec)mysql>但是这种方式也有一些缺点，比如生产环境非常复杂，尤其是在大量事务的情况下，无法明确判断谁在阻塞谁；其次，它是一点都不直观；另外，该方法无法定位到blocker的SQL语句。该方法只能用于辅助分析，通过查看锁的详细信息可以帮助进一步诊断问题。2：Innotop工具如下图。很多情况下，Innotop工具无法定位阻塞语句（BlockingQuery），只能获取一些锁相关的信息。3：通过查询information_schema数据库或结构下几个与事务相关的系统表在前面的测试用例中，在第一个session中使用SELECTFORUPDATE锁定一行记录mysql>useMyDB;Databasechangedmysql>setsessionautocommit=0;QueryOK,0rowsaffected(0.00sec)mysql>selectconnection_id()fromdual;+---------------+|connection_id()|+------------------+|17|+--------------+1rowinset(0.00sec)mysql>select*fromtest_blockingwhereid=1forupdate;+----+-------+|id|name|+----+-------+|1|kerry|+----+--------+1rowinset(0.00sec)mysql>然后执行在第二个连接会话中更新脚本以构造一个阻塞的案例mysql>useMyDB;Databasechangedmysql>selectconnection_id()fromdual;+----------------+|connection_id()|+-----------------+|19|+----------------+1rowinset(0.00sec)mysql>updatetest_blockingsetname='kk'其中id=1；这个时候我们在第三个连接会话中被阻塞了查找被阻塞的人SELECTb.trx_mysql_thread_idAS'blocked_thread_id',b.trx_queryAS'blocked_sql_text',c.trx_mysql_thread_idAS'blocker_thread_id',c.trx_queryAS'blocker_sql_text',(Unix_timestamp()-Unix_timestamp(c.trx_started))AS'blocked_time'FROMinformation_schema.innodb_lock_waitsaINNERJOINinformation_schema.innodb_trxbONa.requesting_trx_id=b.trx_idINNERJOINinformation_schema.innodb_trxcONa.blocking_trx_id=c.trx_idWHERE(Unix_timestamp()-Unix_timestamp(c.trx_started))>4;SELECTa.sql_text,c.id,d.trx_startedFROMperformance_schema.events_statements_currentajoinperformance_schema.threadsbONa.thread_id=b.thread_idjoininformation_schema.processlistcONb.processlist_id=c.idjoininformation_schema.innodb_trxdONc.id=d.trx_mysql_thread_idwherec.id=17ORDERBYd.trx_startedG*一条SQL语句可以查到线程19被线程17阻塞了，阻塞的SQL语句是“updatetest_blockingsetname='kk'whereid=1;”，阻塞了多久可以查到，但是查不到befound到源SQL语句，此时需要出现第二个SQL语句来找到源语句。但是不要太天真地认为第二条SQL语句在所有场景下都可以获得阻塞源SQL语句。在实际的业务场景中，会话可能正在执行一个存储过程或者复杂的业务。有可能在执行完阻塞的源SQL语句后，还会继续执行其他的SQL语句。这时候你抓到的就是这个连接会话执行的SQL语句***。如下图，我简单的构造了一个例子。可以构建这样的场景。曾经写过一篇博客《为什么数据库有时无法定位blocker源头的SQL语句》，分析了SQLServer和ORACLE定位找到了blocker源头的SQL语句。http://www.cnblogs.com/kerrycode/p/5821413.htmlmysql>select*fromtest_blockingwhereid=1forupdate;+----+--------+|id|name|+----+-------+|1|kerry|+----+--------+1rowinset(0.00sec)mysql>deletefromstudentwherestu_id=1001;QueryOK,1rowaffected(0.00sec)mysql>总结：最简单的，用上面两个SQL查询定位阻塞者的SQL语句很方便，但需要注意的是，有时它并不能找到真正被阻塞的源SQL语句。所以需要结合应用代码和上下文环境进行整体分析判断！
