Recently I read the geek time MySQL45 talk, some of the locking scenarios really overturned my perception, then I wrote this article to share, the content of this article is based on MySQL45 talk summary. The first is initialization of data and tables:

CREATE TABLE `t` (
`id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL.PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0.0.0), (5.5.5), (10.10.10), (15.15.15), (20.20.20), (25.25.25);
Copy the code

I am using MySQL version 5.7 under repeatable read isolation level. Let’s start with a quick question:

session A session B session C
begin;

select * from t where c>=15 and c<=20 order by c desc for update;
Insert into t values (11,11,11);
Insert into t values (6,6,6);

This may not seem obvious to you at first glance, but I recommend giving it a try. Without giving the answer, you should know why after reading this article.

MySQL add lock rule, sum up:

  1. The basic unit of locking is next-key lock. Next-key lock is the open before closed interval.
  2. Only objects accessed during lookup are locked.
  3. A next-key lock is degraded to a row lock when a unique index is locked.
  4. The next-key lock is degraded to a gap lock when the last value does not meet the equivalence condition.
  5. A range query on a unique index accesses up to the first value that does not satisfy the condition.

Case 1: Equivalent query gap lock

session A session B session C
begin;

update t set d = d+1 where id=7
Insert into t values (8,8,8);

(block)
update t set d = d+1 where id=10

(ok)

7 is in the interval of (5,10), then the lock interval is (5,10), then the equivalent query next-key lock degrades the gap lock, and the final result is (5,10).

Case 2: Non-unique index equivalent lock

session A session B session C
begin;

select id from t where c = 5 lock in share mode;
update t set d = d+1 where id=5

(ok)
Insert into t values (7,7,7);

(block)

When I saw this, I was like oh my God, there is a bug in MySQL, this is not a random lock, this is… Let’s analyze it according to the previous rules.

At first mysql will add a read lock to c=5. The lock unit is next-key lock, so (0,5). C is a common index, MySQL does not know if there are other rows in this index, so keep searching until c=10, so the range of the lock is (5,10), similar to case 1, result is (0,5) gap lock, c=5, (5,10) gap lock. Update t set d= d+1 where id=5 Select * from c where c=5; select * from c where c=5; select * from C where c=5; Select * from t where c = 5 lock in share mode; Session B will be blocked if the query is all but id. Session A lock in share mode; If you change it to “for UPDATE”, the system will assume that you are going to update the data next, and will place a row lock on the primary key index. So what we often say about locking is that there’s a premise for locking the primary key.

Case 3: Primary key index range lock

mysql> select * from t where id=10 for update;
mysql> select * from t where id> =10 and id<11 for update;
Copy the code

Are these two SQL statements equivalent? Select * from t where id=10 for update; Next-key lock next key lock (id=10) Select * from t where id>=10 and id<11 for update; Lock (id=10, id=10, id=10, id=10, id=10);

Case 4: Non-unique index range lock

select * from t where c>=10 and c<11 for update; This situation is similar to case 3, except that instead of degenerate into a row lock when c=10 is found, it is a next-key lock, so the result is (5,10), and then 11 adds (10,15) to the interval (10,15). That’s 5,15.

Case 5: Unique index range lock

Corresponding to rule 5, a range query on a unique index accesses the first value that does not meet the condition. A unique index logically should not scan further if it finds that row, but it does not.

session A session B session C
begin;

select id from t where id >10 and id<=15 for update;
update t set d = d+1 where id=20

(block)
Insert into t values (16,16,16);

(block)

Select id from t where id >10 and id<=15 for update; Add (10,15) next-key lock (15,20) next key lock (15, 15) next key lock (15, 15) next key lock (15, 15) next key lock (15, 15) next key lock (15, 15) next key lock (15, 15) next key lock (15, 15) select id from t where id >=10 and id<=15 for update; How does this lock work? The answer is (5,10). (10,15).

Case 6: ** exists on non-unique indexequivalentExamples of “* *

So let’s add that to the data

insert into t values(30.10.30);
Copy the code

The value of index C in the table becomes

c 0 5 10 10 15 20 25
Id 0 5 10 30 15 20 25

Select * from t where c=10 for update; What about locking?

(c=5,id=5) and (c=15,id=15) there is no lock on either row.select * from t where c=10 for update limit 2; What if I put a limit on this query?

There’s just a gap missing.

Select * from t where c>=15 and c<=20 order by c desc for update; select * from t where c>=15 and C <=20 order by C desc for update; How does this SQL lock? So I’m going to find c=15 and I’m going to find c=10 and I’m going to lock it and I’m going to do the same thing for 20 and I’m going to find a next key lock for 20 and I’m going to find 25 and I’m going to find the next key lock for 20, The final result is (5,10],(10,15),(15,20),(20,25).