Solve MySQL deadlock routine by updating different indexes

Solve MySQL deadlock routine by updating different indexes The second statement

update t3 set b = '' where b = "2";

To sort it out, 3 X locks were added, in the following order:

The previous articles introduced how to debug lock-related information using source code. Here, we also use this tool to solve an actual online deadlock case, which is also the first two SQL statements we introduced that caused a deadlock. Because the online table structure is relatively complex, some simplifications are made as follows

CREATE TABLE `t3` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` varchar(5),
 `b` varchar(5),
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_a` (`a`),
 KEY `idx_b` (`b`) 
)
INSERT INTO `t3` (`id`, `a`, `b`) VALUES 
 (1,'1','2');
# The sql statement is as follows# Transaction 1: t1
update t3 set b = '' where a = "1";

# Transaction 2: t2
update t3 set b = '' where b = "2";

The situation where the two statements cause a deadlock is difficult to reproduce manually. Let's first analyze the locking process.

The first statement (update the record through the unique index)

update t3 set b = '' where a = "1";

After finishing, 3 X locks were added, in the following order:

Serial number index Lock Type
1 uk_a X
2 PRIMARY X
3 idx_b X

Serial number index Lock Type
1 idx_b X
2 PRIMARY X
3 idx_b X

The two statements seem to have the conditions for deadlock from the order of locking.

It is difficult to simulate manually. If you write a code to execute the two SQL statements concurrently, a deadlock will occur immediately.

------------------------
LATEST DETECTED DEADLOCK
------------------------
181102 12:45:05
*** (1) TRANSACTION:
TRANSACTION 50AF, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 34, OS thread handle 0x70000d842000, query id 549 localhost 127.0.0.1 root Searching rows for update
update t3 set b = '' where b = "2"
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 67 page no 3 n bits 72 index `PRIMARY` of table `d1`.`t3` trx id 50AF lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 6; hex 0000000050ae; asc P ;;
 2: len 7; hex 03000001341003; asc 4 ;;
 3: len 1; hex 31; asc 1;;
 4: len 0; hex ; asc ;;

*** (2) TRANSACTION:
TRANSACTION 50AE, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 35, OS thread handle 0x70000d885000, query id 548 localhost 127.0.0.1 root Updating
update t3 set b = '' where a = "1"
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 67 page no 3 n bits 72 index `PRIMARY` of table `d1`.`t3` trx id 50AE lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 6; hex 0000000050ae; asc P ;;
 2: len 7; hex 03000001341003; asc 4 ;;
 3: len 1; hex 31; asc 1;;
 4: len 0; hex ; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 67 page no 5 n bits 72 index `idx_b` of table `d1`.`t3` trx id 50AE lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 32; asc 2;;
 1: len 4; hex 80000001; asc ;;

*** WE ROLL BACK TRANSACTION (1)

Analyze the deadlock log

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 67 page no 3 n bits 72 index PRIMARY of table d1.t3 trx id 50AF lock_mode X locks rec but not gap waiting

Transaction 2: Want to obtain the X lock of the primary key index

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 67 page no 3 n bits 72 index PRIMARY of table d1.t3 trx id 50AE lock_mode X locks rec but not gap

Transaction 1: holds an X lock on the primary key index

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 67 page no 5 n bits 72 index idx_b of table d1.t3 trx id 50AE lock_mode X locks rec but not gap waiting

Transaction 1: Wants to obtain the X lock of the common index idx_b

It is exactly the same as our analysis and the online deadlock log.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:
  • Understanding MySQL deadlock routines through unique index S lock and X lock
  • Analysis of a MySQL deadlock scenario example
  • Detailed explanation of how MySQL (InnoDB) handles deadlocks
  • Examples of optimistic locking and pessimistic locking in MySQL
  • Mysql queries the transactions being executed and how to wait for locks
  • A brief understanding of the relevant locks in MySQL

<<:  How to hide and forge version number in Nginx

>>:  About Vue to eliminate repeated prompts when refreshing the page when the Token expires

Recommend

Detailed explanation of mkdir command in Linux learning

Table of contents Preface 1. Basic knowledge of f...

Detailed explanation of the use of Vue's new built-in components

Table of contents 1. Teleport 1.1 Introduction to...

Mysql database design three paradigm examples analysis

Three Paradigms 1NF: Fields are inseparable; 2NF:...

Docker configuration Alibaba Cloud image acceleration pull implementation

Today I used docker to pull the image, but the sp...

The role and methods of information communication in website visual design (picture and text)

Contemporary web visual design has gone through th...

Web realizes the code of popping up the window of uploading files by clicking the picture

Copy code The code is as follows: <style> ....

Detailed analysis of GUID display issues in Mongodb

Find the problem I recently migrated the storage ...

A brief analysis of the basic concepts of HTML web pages

What is a web page? The page displayed after the ...

A brief discussion on event-driven development in JS and Nodejs

Table of contents Event-driven and publish-subscr...

Three ways to configure Nginx virtual hosts (based on domain names)

Nginx supports three ways to configure virtual ho...

mysql method to view the currently used configuration file my.cnf (recommended)

my.cnf is the configuration file loaded when MySQ...

Does the % in the newly created MySQL user include localhost?

Normal explanation % means any client can connect...

Solutions to problems using addRoutes in Vue projects

Table of contents Preface 1. 404 Page 1. Causes 2...

Axios cancels repeated requests

Table of contents Preface 1. How to cancel a requ...

Two ways to completely delete users under Linux

Linux Operation Experimental environment: Centos7...