Beginners Understand MySQL Deadlock Problem from Source Code

Time:2019-9-2

Through many difficult single-step debugging late at night, an ideal breakpoint has been found, and most of the lock acquisition process can be seen.
The code is inlock0lock.cOfstatic enum db_err lock_rec_lock() In the function, this function shows the process of acquiring the lock and whether the lock was successful or not.

Scenario 1: Delete 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, what does mode = 1027, 1027 mean by locking the index PRIMARY? 1027 = LOCK_REC_NOT_GAP + LOCK_X (non-gap record lock and X lock)

The process is as follows

Conclusion:If the data is deleted according to the primary key ID and there is no other index, the SQL only needs to X-lock the primary key index on the record id = 10.

Scenario 2: Delete by unique index

Table structureFine tuning, adding a unique index for name

Constructing 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');
 
Testing SQL statements
delete from t2 where name = "Y"

Look at the results of actual source debugging

Step 1:

Step 2:

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

The process is as follows

Scenario 3: Delete by ordinary index

Constructing 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 as follows:

Conclusion:When updating a common index, X-locks will be added to all common indexes that satisfy the criteria, and X-locks will be added to the relevant primary key indexes.

The process is as follows

Scenario 4: Delete without 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 five X locks in all, and the remaining three will not be put up one by one.

Conclusion:When updating without index, SQL scans the whole table by clustering index (primary key index), so every record, whether or not it meets the criteria, will be X-locked. It’s not over yet.

However, MySQL has been optimized for efficiency. For records that do not satisfy the conditions, the lock will be released after judgment. The lock on records that satisfy the conditions will be held eventually, but the lock/unlock action on records that do not satisfy the conditions will not be omitted.

The process is as follows

The above is the whole content of this article. I hope it will be helpful to everyone’s study, and I hope you will support developpaer more.