怎么找到上锁的 SQL 语句
作者:应用开发 来源:系统运维 浏览: 【大中小】 发布时间:2025-11-04 07:34:28 评论数:
前提复制performance_schema = on; 1.
实验
1、上锁建一个表，语句插入三条数据
复制mysql> use test1; Database changed mysql> createtable action1(id int); Query OK,上锁 0 rows affected (0.11 sec) mysql> insertinto action1 values(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from action1; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+3 rows in set (0.00 sec) 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.2、开启一个事务，语句删除掉一行记录，上锁但不提交
复制mysql> begin; Query OK,语句 0 rows affected (0.00 sec) mysql> deletefrom action1 where id = 3; Query OK, 1 row affected (0.00 sec) 1.2.3.4.5.3、另开启一个事务，上锁更新这条语句，语句会被锁住
复制mysql> update action1 set id = 7 where id = 3; 1.4、上锁通过 show processlist 只能看到一条正在执行的语句 SQL 语句
复制mysql> show processlist; | 22188 | root | localhost | test1 | Sleep | 483 | | NULL | | 22218 | root | localhost | NULL | Query | 0 | starting | show processlist | | 22226 | root | localhost | test1 | Query | 3 | updating | update action1 set id = 7 where id = 3 | +-------+-------------+--------------------+-------+---------+------+----------+----------------------------------------+ 1.2.3.4.5.5、接下来就是上锁我们知道的IT技术网，通过 information_schema 库里的语句 INNODBTRX、INNODBLOCKS 、上锁INNODBLOCK_WAITS 获得的语句一个锁信息
复制mysql> select * from INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 5978292 | 5978292:542:3:2 | 5976374 | 5976374:542:3:2 | +-------------------+-------------------+-----------------+------------------+1 row in set, 1 warning (0.00 sec) mysql> select * from INNODB_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 | +-----------------+-------------+-----------+-----------+-------------------+-----------------+------------+-----------+----------+----------------+2 rows in set, 1 warning (0.00 sec) mysql> select trx_id,trx_started,trx_requested_lock_id,trx_query,trx_mysql_thread_id from INNODB_TRX; +---------+---------------------+-----------------------+----------------------------------------+---------------------+ | trx_id | trx_started | trx_requested_lock_id | trx_query | trx_mysql_thread_id | +---------+---------------------+-----------------------+----------------------------------------+---------------------+ | 5978292 | 2020-07-26 22:55:33 | 5978292:542:3:2 | update action1 set id = 7 where id = 3 | 22226 | | 5976374 | 2020-07-26 22:47:33 | NULL | NULL | 22188 | +---------+---------------------+-----------------------+----------------------------------------+---------------------+ 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.6、从上面可以看出来是上锁 thread_id 为 22188 的执行的 SQL 语句锁住了后面的更新操作，但是云南idc服务商我们从上文中 show processlist 中并未看到这条事务，测试环境我们可以直接 kill 掉对应的线程号，但如果是生产环境中，我们需要找到对应的 SQL 语句，根据相应的语句再考虑接下来应该怎么处理
7、需要结合 performance_schema.threads 找到对应的事务号
复制mysql> select * from performance_schema.threads where processlist_ID = 22188\G