Detailed explanation of mysql deadlock checking and deadlock removal examples
|
1. Query process show processlist
2. Query the corresponding process and kill the id
Verify (check if there is still a lock after killing)
2. Check whether the table is locked show OPEN TABLES where In_use > 0; Example: Create a new session and execute the following display lock example LOCK TABLES account_data.account READ; SELECT SLEEP(160); UNLOCK TABLES account_data.account; Open another session to check the lock table status: mysql> show OPEN TABLES where In_use > 0; +--------------+---------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------+---------+--------+-------------+ | account_data | account | 1 | 0 | +--------------+---------+--------+-------------+ 1 row in set (0.00 sec) mysql> select * from information_schema.innodb_locks\G; Empty set, 1 warning (0.00 sec) ERROR: No query specified mysql> show processlist\G; *************************** 1. row *************************** Id: 5 User: root Host: 192.168.0.206:64294 db: NULL Command: Sleep Time: 4051 State: Info: NULL *************************** 2. row *************************** Id: 8 User: root Host: 192.168.0.206:64297 db: NULL Command: Sleep Time: 4042 State: Info: NULL *************************** 3. row *************************** Id: 10 User: root Host: localhost db: NULL Command: Query Time: 0 State: starting Info: show processlist *************************** 4. row *************************** Id: 19 User: root Host: 192.168.0.206:54603 db:account_data Command: Sleep Time: 245 State: Info: NULL *************************** 5. row *************************** Id: 20 User: root Host: 192.168.0.206:54604 db:information_schema Command: Query Time: 20 State: User sleep Info: select sleep(160) 5 rows in set (0.00 sec) ERROR: No query specified mysql> 3. In 5.5, three lock tables are added to the information_schema library (innoDB engine): innodb_trx ## All currently running transactions innodb_locks ## Current locks innodb_lock_waits ## Correspondence between lock waits Let's take a look at the structures of these three tables: [email protected] : information_schema 13:28:38> desc innodb_locks; +————-+———————+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +————-+———————+——+—–+———+——-+ | lock_id | varchar(81) | NO | | | |#Lock ID | lock_trx_id | varchar(18) | NO | | | |#Transaction ID that owns the lock | lock_mode | varchar(32) | NO | | | |#Lock mode | lock_type | varchar(32) | NO | | | |#Lock type | lock_table | varchar(1024) | NO | | | |#Locked table | lock_index | varchar(1024) | YES | | NULL | |#Locked index | lock_space | bigint(21) unsigned | YES | | NULL | |#Locked tablespace number | lock_page | bigint(21) unsigned | YES | | NULL | |#Locked page number | lock_rec | bigint(21) unsigned | YES | | NULL | |#Locked record number | lock_data | varchar(8192) | YES | | NULL | |#Locked data+————-+———————+——+—–+———+——-+ 10 rows in set (0.00 sec) [email protected]:information_schema 13:28:56>desc innodb_lock_waits; +——————-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——————-+————-+——+—–+———+——-+ | requesting_trx_id | varchar(18) | NO | | | |#Transaction ID requesting the lock | requested_lock_id | varchar(81) | NO | | | |#Lock ID of the requested lock | blocking_trx_id | varchar(18) | NO | | | |#Transaction ID that currently owns the lock | blocking_lock_id | varchar(81) | NO | | | |#The lock ID of the current lock +——————-+————-+——+—–+———+——-+ 4 rows in set (0.00 sec) [email protected] : information_schema 13:29:05> desc innodb_trx ; +—————————-+———————+——+—–+———————+——-+ | Field | Type | Null | Key | Default | Extra | +—————————-+———————+——+—–+———————+——-+ | trx_id | varchar(18) | NO | | | |#Transaction ID | trx_state | varchar(13) | NO | | | |#Transaction status: | trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#Transaction start time; | trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id | trx_wait_started | datetime | YES | | NULL | |#Transaction start waiting time | trx_weight | bigint(21) unsigned | NO | | 0 | |# | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#Transaction thread ID | trx_query | varchar(1024) | YES | | NULL | |#Specific SQL statement| trx_operation_state | varchar(64) | YES | | NULL | |#Current operation status of the transaction| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#How many tables are used in the transaction| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#How many locks does the transaction have| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |# | trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#Transaction locked memory size (B) : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |# +—————————-+———————+——+—–+———————+——-+ 22 rows in set (0.01 sec) View locked transactions SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
View transactions waiting for locks SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
View lock blocking thread information 3.1 Use show processlist to view 3.2 Use show engine innodb status to view directly ------------ TRANSACTIONS ------------ Trx ID counter 4131 Purge done for trx's n:o < 4119 undo n:o < 0 state: running but idle History list length 126 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 2, OS thread handle 0x7f953ffff700, query id 115 localhost root init show engine innodb status ---TRANSACTION 4130, ACTIVE 41 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 4, OS thread handle 0x7f953ff9d700, query id 112 localhost root updating delete from emp where empno=7788 ------- TRX HAS BEEN WAITING 41 SEC FOR THIS LOCK TO BE GRANTED: ## Waited for 41 seconds RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4130 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## Thread 4 is waiting to add an X lock to the primary key in test.emp, page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;; ------------------ ---TRANSACTION 4129, ACTIVE 45 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 7, OS thread handle 0x7f953ff6c700, query id 111 localhost root updating update emp set sal=3500 where empno=7788 ------- TRX HAS BEEN WAITING 45 SEC FOR THIS LOCK TO BE GRANTED: ## Waited for 45 seconds RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4129 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## Thread 7 is waiting to add an X lock to the primary key in test.emp, page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;; ------------------ ---TRANSACTION 4128, ACTIVE 51 sec 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 3, OS thread handle 0x7f953ffce700, query id 110 localhost root cleaning up We know that the main root cause is still caused by thread=3, but this result cannot be analyzed from innodb status. From the above, we can see that both thread 4 and thread 7 are waiting to add an X lock to the primary key in test.emp, page num=3. However, thread 7 waits for 45s, while thread 4 waits for 41s. The lock is applied later than thread 7, so it can be determined that thread 7 blocked thread 4. As for why thread 7 is waiting, the root cause cannot be analyzed here. 3.3 Use mysqladmin debug to view # mysqladmin -S /tmp/mysql3306.sock debug Then in the error log, you will see: Thread database.table_name Locked/Waiting Lock_type 3 test.t3 Locked - read Low priority read lock 7 test.emp Locked - write High priority write lock In this method, we can find that thread ID=3 and 7 are blockers, but it is still not accurate enough to determine that thread 7 is also blocked by thread ID=3. 3.4 Use innodb_lock_monitor to get the blocking lock thread MySQL [test]> CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; ## Create this table in any database and the lock monitor will be enabled Query OK, 0 rows affected, 1 warning (0.07 sec) MySQL [test]> show warnings\G *************************** 1. row *************************** Level: Warning Code: 131 Message: Using the table name innodb_lock_monitor to enable diagnostic output is deprecated and may be removed in future releases. Use INFORMATION_SCHEMA or PERFORMANCE_SCHEMA tables or SET GLOBAL innodb_status_output=ON. 1 row in set (0.00 sec) Note: This will cause a warning in 5.6, but will not affect usage. Then use show engine innodb status to view: ------------ TRANSACTIONS ------------ Trx ID counter 4667 Purge done for trx's n:o < 4659 undo n:o < 0 state: running but idle History list length 138 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 9, OS thread handle 0x7f813c5f7700, query id 152 localhost root init show engine innodb status ---TRANSACTION 4663, ACTIVE 78 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 4, OS thread handle 0x7f813c628700, query id 149 localhost root updating delete from emp where empno=7788 ------- TRX HAS BEEN WAITING 78 SEC FOR THIS LOCK TO BE GRANTED: ## Waited for 78 seconds RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4663 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## Thread 4 is waiting to add an X lock to the primary key in test.emp, page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;; ------------------ TABLE LOCK table `test`.`emp` trx id 4663 lock mode IX ## Before adding an X lock to the primary key row, add an intention lock IX to the table first. RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4663 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;; ---TRANSACTION 4662, ACTIVE 81 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 7, OS thread handle 0x7f813c5c6700, query id 148 localhost root updating update emp set sal=3500 where empno=7788 ------- TRX HAS BEEN WAITING 81 SEC FOR THIS LOCK TO BE GRANTED: ## Waited for 81 seconds RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4662 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## Thread 7 is waiting to add an X lock to the primary key in test.emp, page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;; ------------------ TABLE LOCK table `test`.`emp` trx id 4662 lock mode IX ## Before adding an X lock to the primary key row, add an intention lock IX to the table first. RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4662 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;; ---TRANSACTION 4615, ACTIVE 1579 sec, thread declared inside InnoDB 1222 mysql tables in use 2, locked 0 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 3, OS thread handle 0x7f813c659700, query id 147 localhost root Sending data select count(*) from t3 a,t3 b ## This is the SQL currently being executed by thread 3 Trx read view will not see trx with id >= 4662, sees < 4659 TABLE LOCK table `test`.`emp` trx id 4615 lock mode IX ## Thread 3 is holding an intention IX lock on the table and a row-level X lock on the primary key of the test.emp table, page num=3 RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4615 lock_mode X locks rec but not gap Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;; Why is thread 3 currently executing a select t3 table operation, but locking page num=3 on the test.emp table? It is possible that the transaction of thread 3 on the test.emp table was not committed in time. Therefore, we can conclude that thread 3 blocks thread 7, and thread 7 blocks thread 4, so the root cause is thread 3. Just submit or kill thread 3 as soon as possible. 3.5. Check the table lock status: mysql> show status like 'table%'; +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | Table_locks_immediate | 100 | | Table_locks_waited | 11 | +----------------------------+---------+ 3.6. Check the InnoDB_row_lock status variable to analyze the row lock contention on the system: mysql> show status like 'InnoDB_row_lock%'; +-------------------------------+--------+ | Variable_name | Value | +-------------------------------+--------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 159372 | | Innodb_row_lock_time_avg | 39843 | | Innodb_row_lock_time_max | 51154 | | Innodb_row_lock_waits | 4 | +-------------------------------+--------+ 5 rows in set (0.01 sec) mysql> 4. Conclusion When analyzing lock blocking in innodb, the comparison of several methods: (1) Using show processlist to view is unreliable; (2) Directly using show engine innodb status to check the root cause of the problem cannot be determined; (3) Using mysqladmin debug to view all threads that generate locks, you can see them, but you cannot determine which one is the root cause; (4) After enabling innodb_lock_monitor, use show engine innodb status to find the root cause of the lock blockage. Reference: https://www.jb51.net/article/201222.htm This is the end of this article about MySQL deadlock checking and deadlock removal. For more relevant MySQL deadlock checking and deadlock removal content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future! You may also be interested in:
|
<<: Create a movable stack widget function using flutter
>>: Pure CSS implementation (no script) Html command-style tooltip text prompt effect
Recommend
Table related arrangement and Javascript operation table, tr, td
Table property settings that work well: Copy code ...
Example of using Nginx reverse proxy to go-fastdfs
background go-fastdfs is a distributed file syste...
Notes on using the blockquote tag
<br />Semanticization cannot be explained in...
Summary of precautions when writing EDM in HTML (general precautions for sending emails)
Format Encoding 1. Please set the page width with...
How to fix abnormal startup of mysql5.7.21
A colleague reported that a MySQL instance could ...
mysql 8.0.19 win10 quick installation tutorial
This tutorial shares the installation tutorial of...
Analysis of mysql temporary table usage [query results can be stored in temporary tables]
This article uses an example to illustrate the us...
How to start Vue project with M1 pro chip
Table of contents introduction Install Homebrew I...
How to backup MySQL regularly and upload it to Qiniu
In most application scenarios, we need to back up...
How to solve the Docker container startup failure
Question: After the computer restarts, the mysql ...
Installation tutorial of mysql5.7.21 decompression version under win10
Install the unzipped version of Mysql under win10...
Linux uses Rsync+Inotify to achieve real-time synchronization of local and remote data
0x0 Test Environment The headquarters production ...
Deleting files with spaces in Linux (not directories)
In our daily work, we often come into contact wit...
Example code for implementing multiple line omissions using three methods of advanced CSS
Preface This is an old demand, but there are stil...
A Different Kind of "Cancel" Button
The “Cancel” button is not part of the necessary ...