In-depth analysis of MySQL lock blocking

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 Description

RHEL 6.4 x86_64 + MySQL 5.6.19

Transaction isolation level: RR

2. Testing process

3. View lock blocking thread information

Here are several methods for analysis:

3.1 Use show processlist to view

MySQL [(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 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.

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.

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:
  • Analysis of MySQL lock wait and deadlock problems
  • Summary of MySQL lock knowledge points
  • Issues with locking in MySQL

<<:  Markodwn's detailed explanation of the idea of ​​synchronous scrolling with title alignment

>>:  Tutorial on installing Elasticsearch 7.6.2 in Docker

Recommend

12 types of component communications in Vue2

Table of contents 1. props 2..sync 3.v-model 4.re...

Let's talk in detail about the props attributes of components in Vue

Table of contents Question 1: How are props used ...

Four ways to compare JavaScript objects

Table of contents Preface Reference Comparison Ma...

Introduction to Spark and comparison with Hadoop

Table of contents 1. Spark vs. Hadoop 1.1 Disadva...

Installation and configuration method of Zabbix Agent on Linux platform

Here is a brief summary of the installation and c...

Linux file/directory permissions and ownership management

1. Overview of file permissions and ownership 1. ...

Linux swap partition (detailed explanation)

Table of contents linux 1. What is SWAP 2. What d...

MySQL 8.0.18 installation and configuration graphic tutorial

Learning objectives: Learn to use Windows system ...

Detailed explanation of MySQL custom functions and stored procedures

Preface This article mainly introduces the releva...

Summary of the use of MySQL date and time functions

This article is based on MySQL 8.0 This article i...

Solution to the docker command exception "permission denied"

In Linux system, newly install docker and enter t...

Mac+IDEA+Tomcat configuration steps

Table of contents 1. Download 2. Installation and...

Example code of CSS responsive layout system

Responsive layout systems are already very common...

Docker creates MySQL explanation

1. Download MySQL Image Command: docker pull mysq...

More than 300 lines of CSS code to achieve the explosive special effects of WeChat 8.0

A major feature of the WeChat 8.0 update is the s...