Shulou(Shulou.com)05/31 Report--

This article focuses on "how to find locked SQL statements". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to find locked SQL statements.

problem

Sometimes the SQL statement is locked, but the locked SQL statement cannot be found through show processlist. How to troubleshoot it at this time?

Premise

Performance_schema = on

Experiment

1. Create a table and insert three pieces of data

Mysql > use test1; Database changed mysql > create table action1 (id int); Query OK, 0 rows affected (0.11 sec) mysql > insert into action1 values (1), (2), (3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 mysql > select * from action1; +-+ | id | +-+ | 1 | 2 | 3 | +-+ 3 rows in set (0.00 sec)

2. Open a transaction, delete a row of records, but do not commit

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

3. Open another transaction, update this statement, and it will be locked

Mysql > update action1 set id = 7 where id = 3

4. You can only see an executing SQL statement through show processlist

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 | +-| -+

5. Next is the lock information we know from INNODBTRX, INNODBLOCKS, and INNODBLOCK_WAITS in the information_schema library.

Mysql > select * from INNODB_LOCK_WAITS +-+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +- -+ | 5978292 | 5978292 / 542 / 542 / 3 / 3 | 5976374 | 5976374 / 542 / 542 / 3 / 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 / 542 / 3 / 3 | 5978292 | | X | RECORD | `test1`.`action1` | GEN_CLUST_INDEX | 542 | 3 | 2 | 0x00000029D504 | | 5976374VO542 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 where id 542 update action1 set id = 3 | 22226 | | 5976374 | 2020-07-26 22:47:33 | NULL | NULL | | 22188 | +-- -+

6. From the above, we can see that the SQL statement executed with thread_id 22188 locks the subsequent update operation, but we do not see this transaction from the show processlist above. In the test environment, we can kill the corresponding thread number directly, but if it is a production environment, we need to find the corresponding SQL statement and consider what to do next according to the corresponding statement.

7. You need to find the corresponding transaction number with performance_schema.threads.

Mysql > select * from performance_schema.threads where processlist_ID = 22188\ G * * 1. Row * * THREAD_ID: 22225 / / transaction counter NAME: thread/sql/one_connection in perfoamance_schema TYPE: FOREGROUND PROCESSLIST_ID: 22188 / / id PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: test1 PROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 1527 PROCESSLIST_STATE: NULL PROCESSLIST_INFO: NULL PARENT_THREAD_ID: NULL ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID:8632 1 row in set seen from show processlist

8. Find the transaction number, and you can find the corresponding SQL statement from events_statements_current: SQL_TEXT

Mysql > select * from events_statements_current where THREAD_ID = 22225\ G * * 1. Row * * THREAD_ID: 22225 EVENT_ID: 14 END_EVENT_ID: 14 EVENT_NAME: Statement/sql/delete SOURCE: TIMER_START: 546246699055725000 TIMER_END: 546246699593817000 TIMER_WAIT: 538092000 LOCK_TIME: 238000000 SQL_TEXT: delete from action1 where id = 3 / / specific sql statement DIGEST: 8f9cdb489c76ec0e324f947cc3faaa7c DIGEST_TEXT: DELETE FROM `action1`WHERE `id` =? CURRENT_SCHEMA: test1 OBJECT_TYPE: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: NULL MYSQL_ERRNO: 0 RETURNED_SQLSTATE: 00000 MESSAGE_TEXT: NULL 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 NESTING_EVENT_TYPE: NULL NESTING_EVENT_LEVEL: 01 row in set (0.00 sec)

9. You can see that it is a delete that blocks the subsequent update. In the production environment, you can use this SQL statement to ask the developer if it is necessary to kill.

At this point, I believe you have a deeper understanding of "how to find locked SQL statements". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.