Beginners understand MySQL deadlock problem from source code

After many difficult single-step debugging late at night, I finally found an ideal breakpoint. I can see that most of the lock acquisition process code is in the static enum db_err lock_rec_lock() function of lock0lock.c . This function will display the process of acquiring the lock and whether the lock acquisition is successful or not.

Scenario 1: Deleting by primary key

Table Structure

CREATE TABLE `t1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB;

delete from t1 where id = 10;

As you can see, the index PRIMARY is locked, mode = 1027. What does 1027 mean? 1027 = LOCK_REC_NOT_GAP + LOCK_X (non-gap record lock and X lock)

The process is as follows

Conclusion: To delete data based on the primary key id and without other indexes, this SQL statement only needs to add an X lock to the primary key index on the record with id = 10.

Scenario 2: Deleting via a unique index

The table structure has been slightly adjusted, and a unique index of name has been added.

Construct data CREATE TABLE `t2` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_name` (`name`)
) ;
INSERT INTO `t2` (`id`, `name`) VALUES 
 (1,'M'),
 (2,'Y'),
 (3,'S'),
 (4,'Q'),
 (5,'L');
 
Test SQL statement delete from t2 where name = "Y"

Let's look at the results of actual source code debugging

first step:

Step 2:

Conclusion: This process first adds an X lock to the unique key uk_name, and then adds an X lock to the clustered index (primary key index)

The process is as follows

Scenario 3: Deleting via a normal index

Construct data CREATE TABLE `t3` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`) 
);
INSERT INTO `t3` (`id`, `name`) VALUES 
 (1,'N'),
 (2,'G'),
 (3,'I'),
 (4,'N'),
 (5,'X');
 
Test statement:
delete from t3 where name = "N";

The debugging process is shown in the figure:

Conclusion: When updating through a common index, an X lock will be added to all common indexes that meet the conditions, and an X lock will be added to the related primary key indexes.

The process is as follows

Scenario 4: Deleting without using the index

CREATE TABLE `t4` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
)

INSERT INTO `t4` (`id`, `name`) VALUES 
 (1,'M'),
 (2,'Y'),
 (3,'S'),
 (4,'Q'),
 (5,'L');
 
delete from t4 where name = "S";

There are 5 X locks in total, and the remaining 3 are not listed one by one.

Conclusion: When updating without using the index, SQL will scan the entire table using the clustered index (primary key index), so each record will be locked regardless of whether it meets the conditions. It’s not over yet…

However, for efficiency reasons, MySQL has made an optimization. For records that do not meet the conditions, the locks will be released after judgment. The final locks held are those on records that meet the conditions, but the locking/releasing actions on records that do not meet the conditions will not be omitted.

The process is as follows

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.