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

This article is to share with you about the example analysis of MYSQL from Record lock to Next-Key Locks to GAP_LOCK. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

When most people mention locks for the first time, they may think that locks may be aimed at physical data records on disk. In fact, all operations are done in memory. How can locks be aimed at physical data on disk?

After realizing that the locks are generated in memory, what the scope of the lock is, how the lock is performed, and so on, it is necessary to further understand.

There are several kinds of locks in MySQL, Record lock, Gap lock, Next-key lock.

Record lock is based on index records, that is, the goal of locking is not the record itself, but the index. Then someone raised an objection. I don't build an index. I don't have a primary key. I don't have a clustered index.

How do you shackle me, in fact, MYSQL when you do not do any of the above work, MYSQL will helplessly give you a, A hidden Clustered index, (so, build MYSQL does not build its own clustering index, belong to the behavior of rogue to MYSQL), so I saw that MYSQL does not build a primary key, and use UUID behavior I am "extremely sorry".

Next-key lock this thing is in your MYSQL in REPEATABLE READ mode by default, to prevent you from reading. Specific Next-key lock uses GAY LOCKING on INDEX row locks.

Then why do you want to figure out NEXT-KEY LOCK, the original document has such a sentence, he said that a next-key lock is an index record lock plus a GAP lock, if a session has a S or X lock (let's not consider IX IS here), other Session will not be able to insert a new INDEX RECORD before the gap lock INDEX record.

I guess after saying this sentence, more people will be dizziness.

For example, we have the following index value id 10, 11, 13, 20

Expressed in terms of index values (negative infinity, 10] (10) 11] (11) 13] (13) 20] (20, positive infinity)

The official document is followed by a sentence, NEXT-KEY LOCK will lock the gap of the maximum value of the index, In effect, this next-key lock locks only the gap following the largest index value. What does this mean? please open your mind and what effect this operation will have on the insertion.

First of all, Gap lock Gap lock exists only in repeatable read isolation level, and Gap lock exists in this level.

Let's continue with the above example of 10, 11, 13, 20.

There are three session running simultaneously

Session A

Update table set m=m+3 where id = 14

Session B

Insert into table (id) values (16)

Session C

Insert into table (id) values (21)

As a result, session B will fail to insert data, because GAP LOCK locks all the index values of this section, and data insertion in between is not allowed.

Session C, on the other hand, can insert the data directly because by 20:00, the value is closed.

So in the isolation choice of MYSQL, if you choose repeatable read, it means that your MYSQL has to pay more attention to the design of the sentence. If you don't pay attention to it, your MYSQL can only keep reporting BLOCK errors.

We're giving an example.

Session A

Select * from table where id > 10 and id < 20 for update

Session B

Insert into table (id) varlues (14)

Session C

Insert into table (id) values (21)

The result here is that Session B and Session C will fail.

The reason is the next-key lock range lock.

MYSQL using repeatable isolation will encounter more locking and BLOCK problems, so it is recommended here that MYSQL should not use repeatable isolation, and the performance of unique indexes in MYSQL is also worth studying. (in fact, some large tables are like treading on thin ice when dealing with unique indexes.)

We ended up with a case of deadlock.

Session A

Begin

Select id from table where id = 20 in share mode; (+ S)

Insert into table (id) values (15)

End

Session B

Update table set column = column + 1 where id = 20

This directly session b will deadlock.

This is the problem that MYSQL NEXT-KEY LOCK & GAP LOCK under repeatable isolation will encounter. So. I won't say any more.

The above is the example analysis of MYSQL from Record lock to Next-Key Locks to GAP_LOCK. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.