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
The most complete 50 Mysql database query exercises
This database query statement is one of 50 databa...
Mysql database master-slave separation example code
introduce Setting up read-write separation for th...
The top fixed div can be set to a semi-transparent effect
Copy code The code is as follows: <!DOCTYPE ht...
Hyperlink icon specifications: improve article readability
1. What is the hyperlink icon specification ?<...
Alibaba Cloud Nginx configures https to implement domain name access project (graphic tutorial)
Step 1: Sign a third-party trusted SSL certificat...
How to implement the prototype pattern in JavaScript
Overview The prototype pattern refers to the type...
Share MySql8.0.19 installation pit record
The previous article introduced the installation ...
Detailed explanation of using Docker to quickly deploy the ELK environment (latest version 5.5.1)
After installing Docker on the Linux server, Pull...
When the interviewer asked the difference between char and varchar in mysql
Table of contents Difference between char and var...
HTML table tag tutorial (25): vertical alignment attribute VALIGN
In the vertical direction, you can set the row al...
Detailed analysis of SQL execution steps
Detailed analysis of SQL execution steps Let'...
Detailed explanation of MySQL monitoring tool mysql-monitor
1. Overview mysql-monitor MYSQL monitoring tool, ...
MySQL 5.7.18 release installation guide (including bin file version)
The installation process is basically the same as...
Tutorial on installing Pycharm and Ipython on Ubuntu 16.04/18.04
Under Ubuntu 18.04 1. sudo apt install python ins...
The experience gained from comparing and analyzing the homepages of domestic social networking websites is shared with everyone (picture)
This article hopes to gain some insights through a...