Shulou(Shulou.com)06/01 Report--

After many difficult single-step debugging in the middle of the night, we finally found an ideal breakpoint, and we can see most of the process of acquiring the lock.

The code is in the static enum db_err lock_rec_lock () function of lock0lock.c, which shows the process of acquiring the lock and whether it was successful or not.

Scenario 1: delete through the 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 it mean to lock the index PRIMARY, mode = 1027? 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, this SQL only needs to add an X lock to the primary key index on the record id = 10.

Scenario 2: delete through a unique index

The table structure has been fine-tuned to add a unique index for name

Construction 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 (1Magi M'), (2Jing Jing Y'), (3Jing Jing S'), (4LQ Q'), (5Jing L'); Test sql statement delete from T2 where name = "Y"

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

Step one:

Step 2:

Conclusion: this process is to add X lock to the unique key uk_name, and then X lock to the clustered index (primary key index).

The process is as follows

Scenario 3: delete through a normal index

Construction 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), (2), (3), (4), (5), and test statements: delete from T3 where name = "N"

The debugging process is shown in the figure:

Conclusion: when updating through a general index, X locks will be added to all ordinary indexes that meet the conditions, and X locks will be added to the relevant primary key indexes.

The process is as follows

Scenario 4: delete without moving 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 (1meme M'), (2dje Y'), (3je Jing S'), (4je Jing Q'), (5j Q'); delete from T4 where name = "S"

There are a total of five X locks, but the remaining three will not be put up one by one.

Conclusion: when the index is not updated, sql will scan the whole table with the clustered index (primary key index), so every record, whether it meets the condition or not, will be locked by X. It's not over yet.

However, for the sake of efficiency, MySQL optimizes that records that do not meet the conditions will be locked after judgment, and finally hold the locks on records that meet the conditions, but the locking / unlocking actions on records that do not meet 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 your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.