This article is mainly about how to debug MySQL source code, know a SQL will really take which locks, no longer catch shrimp, guess or he Dengcheng dagod did not write the scene do not know how to deal with

After many late nights of difficult single-step debugging, I finally found an ideal breakpoint where I could see most of the lock acquisition process

Lock0lock. c static enum db_err lock_rec_lock(

For the previous content in he Dengcheng’s blog (hedengcheng.com/?p=771), let’s do experiments one by one to verify it (all the experiments introduced below are at the RC isolation level).

Scenario 1: Delete data using 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;
Copy the code

Select * from PRIMARY where mode = 1027; 1027 = LOCK_REC_NOT_GAP + LOCK_X (non-gap record lock and X lock)

The process is as follows

Scenario 2: Delete by unique index

The table structure has been tweaked to add a unique index for name

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 the SQL statement delete from T2where name = "Y"
Copy the code

To see the results of the actual source code debugging step 1:

The process is as follows

Scenario 3: Delete by normal index

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 t3where name = "N";
Copy the code

The debugging process is shown as follows:

Conclusion: When a normal index is updated, X locks are applied to all normal indexes that meet the criteria, as well as to the relevant primary key indexes

The process is as follows

Scenario 4: Delete data without going through 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";
Copy the code

Conclusion: When an update is performed without an index, SQL scans the entire table with the clustered index (primary key index), so every record, regardless of whether the condition is met, is locked with an X lock. Yet… However, for the sake of efficiency, MySQL has made optimization. For records that do not meet the conditions, it will release the lock after judgment. Finally, it holds the lock on the record that meets the conditions, but the lock on the record that does not meet the conditions will not be omitted.

The process is as follows