The interviewer asked me how many locks were added to an update statement? I summed up a full set of eight-part essays

Title: The interviewer asked me how many locks are added to an update statement? I summed up a full set of eight-part essays

Your interviewer is approaching, a middle-aged man in a plaid shirt with a beer belly and a severely receding hairline.
He holds a thermos cup soaked in wolfberry, and holds a MacBook under his arm. The company slogan: "I love to work overtime" is posted on the MacBook.

The interview begins, straight to the point.

Interviewer: It says on your resume that you are proficient in MySQL. Let me ask you a question about MySQL locks. Take a look at what data will this SQL lock?

 update user set name='一灯' where age=5;

The table structure is like this:

 CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB COMMENT='用户表';

Me: age is a non-unique index, MySQL's lock is added to the index, it should only lock the data with age=10.

Interviewer: Are you sure?

Me: Um..., it should be.

Interviewer: [sarcasm], is this your level of proficiency in MySQL? Come here for the interview today, and I will contact you when there is news later.

Could there be news later? When did you take the initiative to contact me?
Those who told the truth were rejected, but the eight-legged essay was accepted instead.
Well, wait for me to see how Yideng sums up MySQL's eight-part article.

Me: This SQL specifically locks which data, and also needs to see what data is in the table.

MySQL has three types of row locks:

Record Locks:

That is, lock a record.

 # 对id=1的用户加锁
update user set age=age+1 where id=1;

Gap Locks:

That is, a range is locked, but the critical data of the range is not included.

 # 对id大于1并且小于10的用户加锁
update user set age=age+1 where id>1 and id<10;

The lock range of the above SQL is (1,10).

Next-Key Locks:

It consists of record locks and gap locks, including both the record itself and the range, with left open and right closed intervals.

 # 对id大于1并且小于等于10的用户加锁
update user set age=age+1 where id>1 and id<=10;

If there are only two pieces of data in the table:

id	name	age
1	Zhang San	1
10	Li Si	10

For the age index, there are three index ranges as follows:

(-∞,1], (1,10], (10,+∞)

Just this SQL:

 update user set name='一灯' where age=5;

Since there is no record with age=5 in the table, and age=5 just falls within the range of ( 1,10] , the range of (1,10] will be locked.

We can test it with real data:

When we execute the update statement, the data ranges of age=2 and age=8 are locked.

Interviewer: The boy answered well. If data with age=5 already exists, what data will the update statement just lock?

Me: If the data in the table is like this.

id	name	age
1	Zhang San	1
5	One Light Architecture	5
10	Li Si	10

For the age index, there are four index ranges as follows:

(-∞,1], (1,5], (5,10], (10,+∞)

Just this SQL:

 update user set name='一灯' where age=5;

The data of age=5 falls within the range of (1,5 ] , so the range of (1,5] will be locked.

Do you think this is over? In order to ensure the security of data, MySQL locks will traverse to the right until the conditions are not met, and a gap lock will be added, that is, the range of (5,10] .

Therefore, the lock return of this SQL is (1,5] and (5,10] .

It is the same as the lock range (1,10] that does not exist with age=5. If you don’t believe it, you can run it again with the test case just now.

Interviewer: The guy has something. If I replace the where condition in SQL with the primary key ID, what does the locking range look like?

 update user set name='一灯' where id=5;

Me: Because the lock is added to the index.

If there is no data with id=5, the locking range is the same as the previous SQL, (1,10] .

If there is data with id=5, MySQL's Next-Key Locks will degenerate into Record Locks , that is, only the row of records with id=5 will be locked.

Interviewer: Young man, the opportunity for promotion and salary increase is reserved for people like you. Double salary, come to work tomorrow.

Summary of knowledge points:

1. MySQL locks are placed on index records.
2. If it is a non-unique index, regardless of whether the record exists in the table, in addition to locking the range where the record is located, it will also traverse to the right and lock the range that does not meet the conditions.
3. If it is a unique index, if the record exists in the table, only the row record is locked. If the record does not exist in the table, in addition to locking the range where the record is located, it will also traverse to the right and lock the range that does not meet the conditions.

---

The article is continuously updated, and you can search for "One Light Architecture" on WeChat to read more technical dry goods as soon as possible.