MySQL DeadLock troubleshooting full process record

MySQL DeadLock troubleshooting full process record

【author】

Liu Bo: Senior Database Manager at Ctrip Technical Support Center, focusing on the operation, maintenance and troubleshooting of Sql server and MySQL.

【environment】

Version number: 5.6.21

Isolation level: REPEATABLE READ

[Problem description]

After receiving a monitoring alarm, an online application DeadLock reported an error, which would appear on time every 15 minutes. The error statistics are as follows:


Log in to the Mysql server to view the log:

mysql> show engine innodb status\G

*** (1) TRANSACTION:

TRANSACTION 102973, ACTIVE 11 sec starting index read

mysql tables in use 3, locked 3

LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s)

MySQL thread id 6, OS thread handle 140024996574976, query id 83 localhost us updating

UPDATE TestTable

SET column1 = 1,

Column2 = sysdate(),

Column3 = '026'

Column4 = 0

AND column5 = 485

AND column6 = 'SEK'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 417 page no 1493 n bits 1000 index idx_column6 of table test.TestTable trx id 102973 lock_mode X waiting

Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007e1452; asc ~ R;;

*** (2) TRANSACTION:

TRANSACTION 102972, ACTIVE 26 sec starting index read

mysql tables in use 3, locked 3

219 lock struct(s), heap size 24784, 2906 row lock(s), undo log entries 7

MySQL thread id 5, OS thread handle 140024996841216, query id 84 localhost us updating

UPDATE TestTable

SET Column1 = 1,

Column2 = sysdate(),

Column3 = '026'

Column4 = 0

AND Column5 = 485

AND Column6 = 'SEK'

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 417 page no 1493 n bits 1000 index idx_Column6 of table test.TestTable trx id 102972 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;


Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007e1452; asc ~ R;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 601 page no 89642 n bits 1000 index idx_column6 of table test.TestTable trx id 32231892482 lock_mode X locks rec but not gap waiting

Record lock, heap no 38 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007eea14; asc ~ ;;

At first glance, updating the same row with the same index should be a block, so it should report a TimeOut error. Why does it report a DeadLock error?

[Preliminary analysis]

Let’s first analyze (2) TRANSACTION, TRANSACTION 32231892482.

The waiting lock information is:

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007eea14; asc

The lock information held is:

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007eeac4; asc

Let’s analyze (1) TRANSACTION, TRANSACTION 32231892617 first.

The waiting lock information is:

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007eeac4; asc

So we can draw a deadlock table, where two resources depend on each other, causing a deadlock:

TRANSACTION Hold Wait
32231892617 53454b\80000000007eea14 53454b\80000000007eeac4
32231892482 53454b\80000000007eeac4 53454b\80000000007eea14

Let's look at the explain result again:

mysql>desc UPDATE TestTable SET Column1=1, Column2 = sysdate(), Column3 = '025' Column4 = 0 AND Column5 = 477 AND Column6 = 'SEK' \G;

*************************** 1. row ***************************

id: 1

select_type: UPDATE

table: TestTable

partitions: NULL

type: index_merge

possible_keys: column5_index,idx_column5_column6_Column1,idxColumn6

key: column5_index,idxColumn6

key_len: 8,9

ref: NULL

rows: 7

filtered: 100.00

Extra: Using intersect(column5_index,idxColumn6); Using where

You can see the EXTRA column:

Using intersect(column5_index,idxColumn6)

Starting from 5.1, the index merge optimization technology was introduced, which allows multiple indexes to be used to perform conditional scans on the same table.

Related documentation: http://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.

【Simulation and verification】

Based on the above preliminary analysis, it is speculated that it is caused by intersect, so we simulate and verify it in the test environment and open 2 sessions to simulate deadlock:

Time Series Session1 Session2
1 Begin;
2 UPDATE TestTable SET Column2 = sysdate() Column4 = 0 AND Column5 = 47 AND Column6 = 'SEK
Execution successful, affecting 7 rows
3 Begin;
4 UPDATE TestTable SET Column2 = sysdate(), Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK';
Blocked
5 UPDATE TestTable SET Column2 = sysdate(), Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK';
Execution Success		 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Based on the above information, we can find that although Session 2 is blocked, it has also obtained some X locks for the resources required by Session 1 at time series 5. We can open another query select count(Column5) from TestTable where Column5 = 485, set SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, query the row with Column5 = 485, and observe the lock waiting information:
mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id

mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id \G;

*************************** 1. row ***************************

waiting_trx_id: 103006

waiting_thread: 36

waiting_query: UPDATE TestTable SET Column1 = 1, Column2 = sysdate(), Column3 = '026' Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK'

blocking_trx_id: 103003

blocking_thread: 37

blocking_query: NULL

*************************** 2. row ***************************

waiting_trx_id: 421500433538672

waiting_thread: 39

waiting_query: select count(Column5) from TestTable where Column5 = 485

blocking_trx_id: 103006

blocking_thread: 36

blocking_query: UPDATE TestTable SET Column1 = 1, Column2 = sysdate(), Column3 = '026' Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK'

2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits \G;

*************************** 1. row ***************************

requesting_trx_id: 103006

requested_lock_id: 103006:417:1493:859

blocking_trx_id: 103003

blocking_lock_id: 103003:417:1493:859

*************************** 2. row ***************************

requesting_trx_id: 421500433538672

requested_lock_id: 421500433538672:417:749:2

blocking_trx_id: 103006

blocking_lock_id: 103006:417:749:2

2 rows in set, 1 warning (0.00 sec)
mysql> select * from INNODB_LOCKS \G;

*************************** 1. row ***************************

lock_id: 103006:417:1493:859

lock_trx_id: 103006

lock_mode: X

lock_type: RECORD

lock_table: test.TestTable

lock_index: idxColumn6

lock_space: 417

lock_page: 1493

lock_rec: 859

lock_data: 'SEK', 8262738

*************************** 2. row ***************************

lock_id: 103003:417:1493:859

lock_trx_id: 103003

lock_mode: X

lock_type: RECORD

lock_table:test.TestTable

lock_index: idxColumn6

lock_space: 417

lock_page: 1493

lock_rec: 859

lock_data: 'SEK', 8262738

*************************** 3. row ***************************

lock_id: 421500433538672:417:749:2

lock_trx_id: 421500433538672

lock_mode: S

lock_type: RECORD

lock_table: test.TestTable

lock_index: column5_index

lock_space: 417

lock_page: 749

lock_rec: 2

lock_data: 485, 8317620

*************************** 4. row ***************************

lock_id: 103006:417:749:2

lock_trx_id: 103006

lock_mode: X

lock_type: RECORD

lock_table: test.TestTable

lock_index: column5_index

lock_space: 417

lock_page: 749

lock_rec: 2

lock_data: 485, 8317620

4 rows in set, 1 warning (0.00 sec)

It can be seen that Session2, trx_id 103006 blocked trx_id 421500433538672, and trx_id 421500433538672 requested_lock is also lock_data: 485, 8317620. This shows that although Session2 is not blocked, it still obtains the lock related to Index column5_index. The reason for being blocked is due to intersect. The lock of idxColumn6 is also needed. Now the idea is clear. Let’s simplify the information of the entire lock allocation as shown in the following table (the requested lock is indicated in cyan, and the lock that needs to be obtained but has not been obtained is indicated in red):

Time Session1 Session2
1 477 SEK
2 485 SEK
3 485 SEK Deadlock occurs

It can be seen that the two resources of 485 SEK formed a loop and eventually a deadlock occurred.

【Solution】

  • The best way is to add a joint index of column5 and Column6.
  • In our environment at the time, we found that the screening degree of Column6 was very low, so we deleted the index of Column6.
    After deleting the index around 10:55, the error no longer occurred:

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

You may also be interested in:
  • MySQL error: Deadlock found when trying to get lock; try restarting transaction solution
  • Summary of online MYSQL synchronization error troubleshooting methods (must read)
  • A solution to the MYSQL master-slave out-of-sync problem
  • A MySQL slow query caused a failure
  • Super deployment tutorial of MHA high availability failover solution under MySQL
  • Overview, installation, troubleshooting, tips, and tools of MySQL replication (shared by Huo Ding)
  • Methods for detecting MySQL table failures

<<:  React antd realizes dynamic increase and decrease of form

>>:  How to deploy SpringBoot project using Dockerfile

Recommend

Get the IP and host name of all hosts on Zabbix

zabbix Zabbix ([`zæbiks]) is an enterprise-level ...

How to configure Nginx to split traffic based on the last segment of the request IP

It is mainly the configuration jump of the if jud...

Detailed explanation of the basic usage of the img image tag in HTML/XHTML

The image tag is used to display an image in a we...

Detailed tutorial on installing MYSQL under WINDOWS

1. Download the installation package -Choose the ...

JS+Canvas realizes dynamic clock effect

A dynamic clock demo based on Canvas is provided ...

Discuss the development trend of Baidu Encyclopedia UI

<br />The official version of Baidu Encyclop...

Vue implements small form validation function

This article example shares the specific code of ...

SQL to implement time series dislocation restoration case

Table of contents 1. Requirements description 2. ...

Manually install mysql5.7.10 on Ubuntu

This tutorial shares the process of manually inst...

A brief discussion on the difference between Mysql primary key index and non-primary key index

Table of contents What is an index The difference...

Some suggestions for ensuring MySQL data security

Data is the core asset of an enterprise and one o...

Implementation of form submission in html

Form submission code 1. Source code analysis <...

How to implement cross-domain API proxy forwarding through Nginx proxy forwarding configuration

Preface In WEB development, we often involve cros...

Get / delete method to pass array parameters in Vue

When the front-end and back-end interact, sometim...

Summary of several situations in which MySQL indexes fail

1. Indexes do not store null values More precisel...