In-depth analysis of MySQL lock blocking
|
In daily maintenance, threads are often blocked, causing the database to respond very slowly. Let's see how to find out which thread caused the blockage. 1. Environmental DescriptionRHEL 6.4 x86_64 + MySQL 5.6.19 Transaction isolation level: RR 2. Testing process
3. View lock blocking thread informationHere are several methods for analysis: 3.1 Use show processlist to viewMySQL [(none)]> show processlist; +----+------+-----------+------+---------+------+--------------+------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+--------------+------------------------------------------+ | 2 | root | localhost | NULL | Query | 0 | init | show processlist | | 3 | root | localhost | test | Query | 70 | Sending data | select count(*) from t3 a,t3 b | | 4 | root | localhost | test | Query | 65 | updating | delete from emp where empno=7788 | | 7 | root | localhost | test | Query | 68 | updating | update emp set sal=3500 where empno=7788 | +----+------+-----------+------+---------+------+--------------+------------------------------------------+ 4 rows in set (0.00 sec) If there are many threads in the database, this method is indeed difficult to confirm. 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 threadMySQL [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. 4. ConclusionWhen 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. Original link: https://blog.csdn.net/hw_libo/article/details/39080809 This concludes this article on in-depth analysis of MySQL lock blocking. For more information on MySQL lock blocking, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future! You may also be interested in:
|
<<: Markodwn's detailed explanation of the idea of synchronous scrolling with title alignment
>>: Tutorial on installing Elasticsearch 7.6.2 in Docker
Recommend
How to install and configure the Apache Web server
Learn how to host your own website on Apache, a r...
The problem of mmx64.efi not found occurs when installing Ubuntu18 dual system on win10
During the installation of Ubuntu 18, the mmx64.e...
Examples of 4 methods for inserting large amounts of data in MySQL
Preface This article mainly introduces 4 methods ...
Introduction and use of triggers and cursors in MySQL
Trigger Introduction A trigger is a special store...
Teach you about react routing in five minutes
Table of contents What is Routing Basic use of pu...
Example of troubleshooting method to solve Nginx port conflict
Problem Description A Spring + Angular project wi...
CSS container background 10 color gradient Demo (linear-gradient())
grammar background: linear-gradient(direction,col...
MySQL multi-master and one-slave data backup method tutorial
Overview Operations on any one database are autom...
An article teaches you how to implement VUE multiple DIVs and button binding enter events
There is currently a requirement that an operatio...
Detailed explanation of uniapp painless token refresh method
When the front-end requests the interface, it is ...
Detailed explanation of Linux file permissions and group modification commands
In Linux, everything is a file (directories are a...
MySQL index leftmost principle example code
Preface I was recently reading about MySQL indexe...
Implementation of Node connection to MySQL query transaction processing
Table of contents Enter the topic mysql add, dele...
CSS Problems with Using Position:fixed and Margin-top Together on Same-Level Elements
Problem Description I want to use CSS to achieve ...
Detailed explanation of the use of this.$set in Vue
Table of contents Use of this.$set in Vue use Why...