本篇内容主要讲解“如何找到上锁的SQL语句”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“如何找到上锁的SQL语句”吧!

问题

有的时候 SQL 语句被锁住了，可是通过 show processlist 找不到加锁的的 SQL 语句，这个时候应该怎么排查呢

前提

performance_schema=on;

实验

1、建一个表，插入三条数据

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、通过 show processlist 只能看到一条正在执行的 SQL 语句

mysql>showprocesslist;|22188|root|localhost|test1|Sleep|483||NULL||22218|root|localhost|NULL|Query|0|starting|showprocesslist||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|RECORD|`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 语句锁住了后面的更新操作，但是我们从上文中 show 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 找到对应的 SQL 语句：SQL_TEXT

mysql>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、可以看到是一条 delete 阻塞了后续的 update，生产环境中可以拿着这条 SQL 语句询问开发，是不是有 kill 的必要。

到此，相信大家对“如何找到上锁的SQL语句”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！