MySQL Lock process detailed (9)-innodb record lock, Gap lock, Next-key lock

MySQL Lock process detailed (1)-Basics MySQL plus lock process details (2)-about MySQL read comprehension MySQL plus lock process details (3)-about MySQL read comprehension MySQL Lock process detailed (4)-select for Update/lock in share mode affects transactional concurrency MySQL Lock process detailed (5)-innodb Multi-version concurrency control principle detailed MySQL Lock process detailed (6)-Database Isolation Level (1) MySQL Lock process details (6)-Database Isolation Level (2)-Understanding 4 isolation levels of transactions by example MySQL Lock process details (7)-Initial understanding of MySQL Gap lock MySQL Lock process detailed (8)-Understanding InnoDB Lock (Record,gap,next-key Lock) MySQL Lock process detailed (9)-innodb record lock, Gap lock, Next-key lock

You need to know.
Before we introduced the exclusive lock, in fact InnoDB under the record lock (also called the row lock), the clearance lock, the Next-key lock all belongs to the exclusive lock.

Row lock
Record locks in fact very good understanding, the table in the record lock, called the record lock, short-line lock.

The Gap lock in life
The idea of programming comes from life, and the examples in life help us to better understand some of the ideas in programming.
The scene in the line of life, Xiao Ming, Xiao Red, floret three people in turn stood in a row, at this time, how to let the new small just can't stand next to the small red, when the small red and her in front of the gap between the seal, will be small red and her behind the small gap between the seal, then small just can't stand next to the Little red.
Here's little Red, xiaoming, Floret, small just is a database of records.
The gap between them is the clearance, and the lock that blocks the distance between them is called the gap lock.

Gap lock in MySQL
In the following table (see figure I), the ID is the primary key, and the number field has a two-level index that is not a unique index, what is the way that the table can no longer insert number=5 records?

Figure A

According to the example of life above, we can naturally think that as long as the control of a few points, number=5 can not insert records before , number=5 existing records can no longer insert new records, number=5 after You cannot insert a new record, then the new number=5 record will not be inserted.

So, how does MySQL control number=5 before, and then cannot have new records inserted (to prevent phantom reading)?
The answer is to use a gap lock, at the RR level, MySQL through the gap lock can achieve lock number=5 before the Gap, Number=5 records between the Gap, number=5 after the gap, so that the new record can not be inserted.

How is the gap divided?

Note : For the sake of understanding, we stipulate that (id=a,number=b) represents a field id=a, the record of the field Number=b, (C,d) represents an interval, which represents the range of c-d.

In figure one, according to the number column, we can be divided into several intervals: (Infinity, 2), (2,4), (4,5), (5,5), (5,11), (11, Infinity).
As long as these intervals correspond to the two critical records in the middle can be inserted into the record, it is considered that the interval corresponding to a gap between records.
For example: Interval (2,4) corresponding to the critical record is (id=1,number=2), (id=3,number=4), these two records can be inserted in the middle (id=2,number=3) and other records, then think (id=1,number=2) and (id= 3,NUMBER=4) There is a gap between them.

Many people ask, is there a gap between the record (id=6,number=5) and (id=8,number=5)?
The answer is yes, (id=6,number=5) and (id=8,number=5) can be inserted between the record (id=7,number=5), so (id=6,number=5) and (id=8,number=5) there is a gap between,

Area locked by Gap lock
Search the search criteria to the left to find the record value nearest to the search criteria a, as the left interval, to the right to find the nearest search condition record value B as the right interval, that is, the locked gap is (a, b).
In Figure one, where number=5 words, then the interval range of the clearance lock is (4,11);

The purpose of the gap lock is to prevent Phantom reading, which is achieved through two main aspects:
(1) Prevent new data from being inserted in the gap
(2) Prevent the existing data, update the data within the gap (for example, to prevent Numer=3 records from becoming number=5 by update)

InnoDB conditions for automatic use of clearance locks:
(1) must be at RR level
(2) The search criteria must be indexed (without an index, MySQL will scan the whole table, that will lock the entire table all records, including non-existent records, at this time other transactions can not be modified cannot be deleted cannot be added)

Next, the scope of the clearance lock is observed by practical operation.

Figure three table structure

Case one:

"' Session1: StartTransaction ;Select  *  fromNewswhere  Number=4  for Update; session2: StartTransaction ;Insert  intoNews Value (2,4); # (blocked)Insert  intoNews Value (2,2); # (blocked)Insert  intoNews Value (4,4); # (blocked)Insert  intoNews Value (4,5); # (blocked)Insert  intoNews Value (7,5); # (successful execution)Insert  intoNews Value (9,5); # (successful execution)Insert  intoNews Value ( One,5); # (successful execution) "

Retrieving the condition number=4, the left obtains the nearest value 2 as the left interval, and the right obtains the nearest 5 as the right interval, so that session 1 of the clearance Lock range (2,4), (4,5), as shown in:

The gap lock is locked in the interval (2,4), (4,5), that is, the gap between the record (id=1,number=2) and the record (id=3,number=4) is locked, the gap between the record (id=3,number=4) and the record (id=6,number=5) is locked.

So the record (id=2,number=4), (id=2,number=2), (id=4,number=4), (id=4,number=5) is just between (id=3,number=4) and (id=6,number=5), Therefore, it is not necessary to insert, wait for the lock to be released, while the Record (id=7,number=5), (id=9,number=5), (id=11,number=5) is not in the scope of the above lock, so it will be inserted successfully.

Case TWO:

"' Session1: StartTransaction ;Select  *  fromNewswhere  Number= -  for Update; session2: StartTransaction ;Insert  intoNews Value ( One,5); # (successful execution)Insert  intoNews Value ( A, One); # (successful execution)Insert  intoNews Value ( -, One); # (blocked)Insert  intoNews Value ( the, A); # (blocked)UpdateNewsSetId= - where  Number= One; # (blocked)UpdateNewsSetId= One where  Number= One; # (successful execution) "

检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，因此，session 1的间隙锁的范围（11，无穷大），如所示：

This table does not have a record of number=13, InnoDB will still be the left and right sides of the record with clearance lock, Clearance lock range (11, infinity).

Someone would ask, why is update news set id=14 where number=11 is blocked, but update news set id=11 where number=11 is executed successfully?

Gap lock with the specified record in front and back and the gap between the middle of the way to avoid the data is inserted, this image gap lock locked area is (11, infinity), that is, the record (id=13,number=11) can no longer insert records, update news set id=14 Where number=11 this statement, if executed, will be inserted behind (id=13,number=11), that is, between the interval (11, infinity), because the interval is locked by a gap lock, it can only block the wait, and update news set id=11 Where number=11 executes is inserted before the (id=13,number=11) record, and is not in the range of (11, Infinity), so there is no need to wait for the execution to succeed.

Case THREE:

"' Session1: StartTransaction ;Select  *  fromNewswhere  Number=5  for Update; session2: StartTransaction ;Insert  intoNews Value (4,4); # (blocked)Insert  intoNews Value (4,5); # (blocked)Insert  intoNews Value (5,5); # (blocked)Insert  intoNews Value (7, One); # (blocked)Insert  intoNews Value (9, A); # (successful execution)Insert  intoNews Value ( A, One); # (blocked)UpdateNewsSet  Number=5 whereId=1; # (blocked)UpdateNewsSetId= One where  Number= One; # (blocked)UpdateNewsSetId=2 where  Number=4; # (successful execution)UpdateNewsSetId=4 where  Number=4; # (blocked) "

Retrieving the condition number=5, the left obtains the nearest value 4 as the left interval, the right gets 11 for the right interval, so the session 1 clearance lock range (4,5), (5,11), as shown in:

Some people ask, why is insert into news value (9,12) executed successfully? The gap lock uses a gap lock on the front and back of the specified record and in the middle of the gap to prevent the data from being inserted, (ID=9,NUMBER=12) is clearly behind the record (13,11) and therefore no longer locked in the gap range.

Why is update news set number=5 where id=1 blocked?
Number=5 the front of the record, including the middle is blocked, you this update news set number=5 where id=1 is not able to execute, because InnoDB has put you can store the location is locked, because can only wait.

Similarly, update news set id=11 where number=11 because the gap between record (id=10,number=5) and record (id=13,number=11) is blocked, you can't execute this SQL, you have to wait, Because the location of the storage was blocked.

Case FOUR:

Session1: StartTransaction;Select *  fromNewswhere  Number>4  for Update; session2: StartTransaction;UpdateNewsSetId=2 where  Number=4; # (successful execution)UpdateNewsSetId=4 where  Number=4; # (blocked)UpdateNewsSetId=5 where  Number=5; # (blocked)Insert  intoNews Value (2,3); # (successful execution)Insert  intoNews Value (NULL, -); # (blocked)

Search condition Number>4, the left to get the nearest value 4 as the left interval, the right to infinity, so the session 1 clearance lock range (4, infinity), as shown in:

Some of the session2 are blocked, and some are successful because the inserted area is locked and blocked.

Next-key Lock
Next-key lock actually contains record lock and Gap lock, that is, lock a range, and lock the record itself, InnoDB default lock mode is Next-key lock.
In the above case, the SQL in Session 1 is: SELECT * from news where number=4 for update;
Next-key lock Lock Range is gap lock + record lock, i.e. interval (2,4), (4,5) plus gap lock, while number=4 record plus record lock.

From:http://www.jianshu.com/p/bf862c37c4c9

MySQL Lock process detailed (9)-innodb record lock, Gap lock, Next-key lock