问题是有时候SQL语句被加锁了，但是通过showprocesslist无法找到被加锁的SQL语句。这个时候应该怎么排查？先决条件performance_schema=on;实验一、创建表，插入三条数据mysql>usetest1;Databasechangedmysql>createtableaction1(idint);QueryOK,0rowsaffected(0.11sec)mysql>insertintoaction1values(1),(2),(3);QueryOK,3rowsaffected(0.00sec)Records:3Duplicates:0Warnings:0mysql>select*fromaction1;+------+|id|+------+|1||2||3|+------+3rowsinset(0.00sec)2.开启一个事务，删除一行记录,但是不提交mysql>begin;QueryOK,0rowsaffected(0.00sec)mysql>deletefromaction1whereid=3;QueryOK,1rowaffected(0.00sec)3.再开一个事务更新这条语句，会被锁住mysql>updateaction1setid=7whereid=3;4.showprocesslistmysql>showprocesslist;|22188|root|localhost|test1|Sleep|483||NULL||22218|root|localhost|NULL|Query|0|starting|showprocesslist|只能看到一条SQL语句正在执行|22226|root|localhost|test1|Query|3|updating|updateaction1setid=7whereid=3|+--------+------------+--------------------+--------+--------+------+-----------+----------------------------------------+5，下一步就是我们知道了，通过information_schema库里的INNODBTRX、INNODBLOCKS、INNODBLOCK_WAITS获得的一个锁信息mysql>select*fromINNODB_LOCK_WAITS;+--------------------+------------------+----------------+----------------+|requesting_trx_id|requested_lock_id|blocking_trx_id|blocking_lock_id|+----------------+--------------------+--------------+----------------+|5978292|5978292:542:3:2|5976374|5976374:542:3:2|+--------------------+--------------------+---------------+----------------+1rowinset,1warning(0.00sec)mysql>select*fromINNODB_LOCKs;+--------------+------------+------------+----------+--------------------+----------------+-----------+------------+----------+----------------+|lock_id|lock_trx_id|lock_mode|lock_type|lock_table|lock_index|lock_space|lock_page|lock_rec|lock_data|+----------------+------------+------------+------------+----------------+---------------+------------+------------+----------+---------------+|5978292:542:3:2|5978292|X|记录|`test1`.`action1`|GEN_CLUST_INDEX|542|3|2|0x00000029D504||5976374:542:3:2|5976374|X|RECORD|`test1`.`action1`|GEN_CLUST_INDEX|542|3|2|0x00000029D504|+----------------+------------+------------+----------+--------------------+----------------+----------+------------+----------+----------------+2rowsinset,1warning(0.00sec)mysql>selecttrx_id,trx_started,trx_requested_lock_id,trx_query,trx_mysql_thread_idfromINNODB_TRX;+--------+--------------------+------------------------+--------------------------------------+--------------------+|trx_id|trx_started|trx_requested_lock_id|trx_query|trx_mysql_thread_id|+----------+--------------------+---------------------+----------------------------------------+---------------------+|5978292|2020-07-2622:55:33|5978292:542:3:2|updateaction1setid=7whereid=3|22226||5976374|2020-07-2622:47:33|NULL|NULL|22188|+--------+--------------------+----------------------+--------------------------------------+--------------------+6。从上面可以看出执行的thread_id为22188的SQL语句锁定了后续的update操作，但是我们从上面显示processlist中并没有看到这个事务。在测试环境中，我们可以直接kill对应的线程号，但是在生产环境中，我们需要找到对应的SQL语句，然后根据对应的语句考虑如何处理。7.需要结合performance_schema.threads找到对应的事务号mysql>select*fromperformance_schema.threadswhereprocesslist_ID=22188\G******************************1.row***************************THREAD_ID:22225//perfoamance_schema中的事务计数器NAME:thread/sql/one_connectionTYPE:FOREGROUNDPROCESSLIST_ID:22188//从showprocesslist中看到的idPROCESSLIST_USER:rootPROCESSLIST_HOST:localhostPROCESSLIST_DB:test1PROCESSLIST_COMMAND:SleepPROCESSLIST_TIME:1527PROCESSLIST_STATE:NULLPROCESSLIST_INFO:NULLPARENT_THREAD_ID:NULLROLE:NULLINSTRUMENTED:YESHISTORY:YESCONNECTION_TYPE:SocketTHREAD_OS_ID:86321rowinset(0.00sec)8、找到事务号，可以从events_statements_current找到对应的SQLstatement:SQL_TEXTmysql>select*fromevents_statements_currentwhereTHREAD_ID=22225\G******************************1.row****************************THREAD_ID：22225EVENT_ID:14END_EVENT_ID:14EVENT_NAME:statement/sql/deleteSOURCE:TIMER_START:546246699055725000TIMER_END:546246699593817000TIMER_WAIT:538092000LOCK_TIME:238000000SQL_TEXT:deletefromaction1whereid=3//具体的sql语句DIGEST:8f9cdb489c76ec0e324f947cc3faaa7cDIGEST_TEXT:DELETEFROM`action1`WHERE`id`=?CURRENT_SCHEMA:test1OBJECT_TYPE:NULLOBJECT_SCHEMA:NULLOBJECT_NAME:NULLOBJECT_INSTANCE_BEGIN:NULLMYSQL_ERRNO:0RETURNED_SQLSTATE:00000MESSAGE_TEXT:NULLERRORS:0WARNINGS:0ROWS_AFFECTED:1ROWS_SENT:0ROWS_EXAMINED:3CREATED_TMP_DISK_TABLES:0CREATED_TMP_TABLES:0SELECT_FULL_JOIN:0SELECT_FULL_RANGE_JOIN:0SELECT_RANGE:0SELECT_RANGE_CHECK:0SELECT_SCAN:0SORT_MERGE_PASSES:0SORT_RANGE:0SORT_ROWS:0SORT_SCAN:0NO_INDEX_USED:0NO_GOOD_INDEX_USED:0NESTING_EVENT_ID:NULLNESTING_EVENT_TYPE:NULLNESTING_EVENT_LEVEL:01rowinset(0.00sec)9.可以看出删除会阻塞后续更新。在生产环境中，可以通过这条SQL语句询问开发是否需要杀掉。
