怎么找到上锁的 SQL 语句

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

 问题

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

[[336593]]

前提

 

  1. performance_schema = on

实验

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

 

  1. mysql> use test1; 
  2. Database changed 
  3. mysql> create table action1(id int); 
  4. Query OK, 0 rows affected (0.11 sec) 
  5.   
  6. mysql> insert into action1 values(1),(2),(3); 
  7. Query OK, 3 rows affected (0.00 sec) 
  8. Records: 3  Duplicates: 0  Warnings: 0   
  9. mysql> select * from action1; 
  10. +------+ 
  11. | id   | 
  12. +------+ 
  13. |    1 | 
  14. |    2 | 
  15. |    3 | 
  16. +------+3 rows in set (0.00 sec) 

2、开启一个事务,删除掉一行记录,但不提交

 

  1. mysql> begin
  2. Query OK, 0 rows affected (0.00 sec) 
  3.   
  4. mysql> delete from action1 where id = 3; 
  5. Query OK, 1 row affected (0.00 sec) 

3、另开启一个事务,更新这条语句,会被锁住

 

  1. mysql> update action1 set id = 7 where id = 3; 

4、通过 show processlist 只能看到一条正在执行的 SQL 语句

 

  1. mysql> show processlist; 
  2. | 22188 | root        | localhost          | test1 | Sleep   |  483 |          | NULL                                   | 
  3. | 22218 | root        | localhost          | NULL  | Query   |    0 | starting | show processlist                       | 
  4. | 22226 | root        | localhost          | test1 | Query   |    3 | updating | update action1 set id = 7 where id = 3 | 
  5. +-------+-------------+--------------------+-------+---------+------+----------+----------------------------------------+ 

5、接下来就是我们知道的,通过 information_schema 库里的 INNODBTRX、INNODBLOCKS 、INNODBLOCK_WAITS 获得的一个锁信息

 

  1. mysql> select * from INNODB_LOCK_WAITS; 
  2. +-------------------+-------------------+-----------------+------------------+ 
  3. | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | 
  4. +-------------------+-------------------+-----------------+------------------+ 
  5. | 5978292           | 5978292:542:3:2   | 5976374         | 5976374:542:3:2  | 
  6. +-------------------+-------------------+-----------------+------------------+1 row in set, 1 warning (0.00 sec) 
  7.   
  8. mysql> select * from INNODB_LOCKs; 
  9. +-----------------+-------------+-----------+-----------+-------------------+-----------------+------------+-----------+----------+----------------+ 
  10. | lock_id         | lock_trx_id | lock_mode | lock_type | lock_table        | lock_index      | lock_space | lock_page | lock_rec | lock_data      | 
  11. +-----------------+-------------+-----------+-----------+-------------------+-----------------+------------+-----------+----------+----------------+ 
  12. | 5978292:542:3:2 | 5978292     | X         | RECORD    | `test1`.`action1` | GEN_CLUST_INDEX |        542 |         3 |        2 | 0x00000029D504 | 
  13. | 5976374:542:3:2 | 5976374     | X         | RECORD    | `test1`.`action1` | GEN_CLUST_INDEX |        542 |         3 |        2 | 0x00000029D504 | 
  14. +-----------------+-------------+-----------+-----------+-------------------+-----------------+------------+-----------+----------+----------------+2 rows in set, 1 warning (0.00 sec) 
  15.    
  16. mysql> select trx_id,trx_started,trx_requested_lock_id,trx_query,trx_mysql_thread_id from INNODB_TRX; 
  17. +---------+---------------------+-----------------------+----------------------------------------+---------------------+ 
  18. | trx_id  | trx_started         | trx_requested_lock_id | trx_query                              | trx_mysql_thread_id | 
  19. +---------+---------------------+-----------------------+----------------------------------------+---------------------+ 
  20. | 5978292 | 2020-07-26 22:55:33 | 5978292:542:3:2       | update action1 set id = 7 where id = 3 |               22226 | 
  21. | 5976374 | 2020-07-26 22:47:33 | NULL                  | NULL                                   |               22188 | 
  22. +---------+---------------------+-----------------------+----------------------------------------+---------------------+ 

6、从上面可以看出来是 thread_id 为 22188 的执行的 SQL 语句锁住了后面的更新操作,但是我们从上文中 show processlist 中并未看到这条事务,测试环境我们可以直接 kill 掉对应的线程号,但如果是生产环境中,我们需要找到对应的 SQL 语句,根据相应的语句再考虑接下来应该怎么处理

7、需要结合 performance_schema.threads 找到对应的事务号

 

  1. mysql> select * from performance_schema.threads where processlist_ID = 22188\G 
  2. *************************** 1. row *************************** 
  3.           THREAD_ID: 22225  //perfoamance_schema中的事务计数器               NAME: thread/sql/one_connection 
  4.                TYPE: FOREGROUND 
  5.      PROCESSLIST_ID: 22188  //从show processlist中看到的id   PROCESSLIST_USER: root 
  6.    PROCESSLIST_HOST: localhost 
  7.      PROCESSLIST_DB: test1 
  8. PROCESSLIST_COMMAND: Sleep 
  9.    PROCESSLIST_TIME: 1527  PROCESSLIST_STATE: NULL 
  10.    PROCESSLIST_INFO: NULL 
  11.    PARENT_THREAD_ID: NULL 
  12.                ROLE: NULL 
  13.        INSTRUMENTED: YES 
  14.             HISTORY: YES 
  15.     CONNECTION_TYPE: Socket 
  16.        THREAD_OS_ID:8632 1 row in set (0.00 sec) 

8、找到事务号,可以从 events_statements_current 找到对应的 SQL 语句:SQL_TEXT

 

  1. mysql> select * from events_statements_current where THREAD_ID = 22225\G 
  2. *************************** 1. row *************************** 
  3.               THREAD_ID: 22225               EVENT_ID: 14           END_EVENT_ID: 14             EVENT_NAME: statement/sql/delete 
  4.                  SOURCE: 
  5.             TIMER_START: 546246699055725000              TIMER_END: 546246699593817000             TIMER_WAIT: 538092000              LOCK_TIME: 238000000               SQL_TEXT: delete from action1 where id = 3  //具体的sql语句                 DIGEST: 8f9cdb489c76ec0e324f947cc3faaa7c 
  6.             DIGEST_TEXT: DELETE FROM `action1` WHERE `id` = ? 
  7.          CURRENT_SCHEMA: test1 
  8.             OBJECT_TYPE: NULL 
  9.           OBJECT_SCHEMA: NULL 
  10.             OBJECT_NAME: NULL 
  11.   OBJECT_INSTANCE_BEGIN: NULL 
  12.             MYSQL_ERRNO: 0      RETURNED_SQLSTATE: 00000           MESSAGE_TEXT: NULL 
  13.                  ERRORS: 0               WARNINGS: 0          ROWS_AFFECTED: 1              ROWS_SENT: 0          ROWS_EXAMINED: 3CREATED_TMP_DISK_TABLES: 0     CREATED_TMP_TABLES: 0       SELECT_FULL_JOIN: 0 SELECT_FULL_RANGE_JOIN: 0           SELECT_RANGE: 0     SELECT_RANGE_CHECK: 0            SELECT_SCAN: 0      SORT_MERGE_PASSES: 0             SORT_RANGE: 0              SORT_ROWS: 0              SORT_SCAN: 0          NO_INDEX_USED: 0     NO_GOOD_INDEX_USED: 0       NESTING_EVENT_ID: NULL 
  14.      NESTING_EVENT_TYPE: NULL 
  15.     NESTING_EVENT_LEVEL: 01 row in set (0.00 sec) 

9、可以看到是一条 delete 阻塞了后续的 update,生产环境中可以拿着这条 SQL 语句询问开发,是不是有 kill 的必要。

 

 

责任编辑:华轩 来源: 今日头条
相关推荐

2023-03-30 09:10:06

SQLSELECTFROM

2023-12-27 09:32:47

SQL语句数据

2010-09-03 14:39:15

SQLSELECT语句

2010-09-03 15:47:40

SQL语句锁定

2010-07-08 13:26:02

SQL Server

2010-09-03 14:47:50

SQLSELECT语句

2010-09-03 11:25:58

SQL删除

2017-05-16 11:20:51

SQL语句解析

2010-09-17 16:03:17

锁定SQL表

2010-11-11 11:37:22

SQL SELECT语

2010-09-08 16:26:26

SQL循环语句

2010-07-08 13:32:22

SQL Server

2010-11-12 13:08:36

动态sql语句

2010-09-25 11:32:20

SQL主键约束

2010-07-20 13:52:27

SQL Server

2011-03-22 16:54:58

SQL语句

2010-10-21 12:16:11

SQL Server查

2010-09-17 16:53:14

SQL中CREATE

2010-04-13 15:04:16

Oracle优化

2010-09-25 14:59:54

SQL语句
点赞
收藏

51CTO技术栈公众号