The algorithm of the row lock in MySQL

3 Algorithm for row locks

Record Lock: A lock on a single row record

Gap Lock: A lock that locks a range but does not contain the record itself

Next-key lock:gap Lock+record Lock Locks a range and locks the record itself

Record lock always locks the index record, and if the InnoDB storage engine table does not set any index at the time of establishment, then it is locked with an implicit primary key

Next-key Lock is a locking algorithm that combines gap lock and record LOCKD, and under the Nex-key lock algorithm, InnoDB queries for rows are based on this locking algorithm, such as an index of 10 11 13 20 with four values, Then the index may be next-key locking the interval is:

Take a look at the SQL statement that executes the table

Table T has a total of 1 2 53 values, which are first X-locked for a=5 in session a. Because A is a primary key and unique, so the lock is only 5 of this value, instead of (2,5) this range, so that in session B insert 4 will not block, can immediately insert and return, and locked by the Next-key lock algorithm downgrade in order to record lock, thereby improving the concurrency of the application

As described earlier, Next-key Lock is downgraded to record lock only if the queried column is a unique index, if it is a secondary index, there are different

>Create TableZ (AINTBINT,PRIMARY KEY(a),KEY(b));>INSERT  intoZSELECT 1,1;>INSERT  intoZSELECT 3,1;>INSERT  intoZSELECT 5,3;>INSERT  intoZSELECT 7,6;>INSERT  intoZSELECT Ten,8;

Column B of Table z is a secondary index, and if you execute the following SQL statement in session a

> SELECT  *  from WHERE B=3UPDATE;

Obviously, this is a SQL statement that is queried by index B, so it is locked using the traditional next-key locking technique, and because there are two indexes, it needs to be locked separately, and for a clustered index, it only adds record lock to the index of column A equals 5, and for the secondary index, With Next-key Lock, the locking range is (1,3) in particular, the InnoDB storage engine also adds Gap lock to the next key value of the secondary index, which is also a lock with a secondary index range of (3,6). Therefore, running the following statement in session B is blocked

>SELECT *  fromZWHEREA=5LOCKinchSHARE MODE;>INSERT  intoZSELECT 4,2;>INSERT  intoZSELECT 6,5;

The first SQL statement cannot be executed because the SQL statement executed in session a already has an X lock on the value of the column a=5 in the clustered index, so execution blocks, the second SQL statement, the primary key inserts 4, no problem, but the secondary index 2 is in the locked range (1,3), so execution is also blocked. The third SQL statement, insert PRIMARY key 6 is not locked, 5 is no longer range (1,3), but the inserted value 5 is in the other locked range (3,6), so the same needs to wait. The following SQL statement is not blocked. can understand the implementation

> INSERT  into SELECT 8,6; > INSERT  into SELECT 2,0; > INSERT  into SELECT 6,7;

As you can see from the example above, the role of GAP lock is to prevent multiple transactions from inserting records into the same range, which can lead to phantom problem problems. For example, in the example above, session a user has locked the b=3 record, if there is no gap lock lock (3,6) then the user can insert index b column 3 records, which will cause the user in session a to perform the same query again will return different records, resulting in Phantom The problem problem arises.

There are two ways in which you can explicitly turn off gap Lock:

To set the isolation level of a transaction to RC

Set the parameter Innodb_locks_unsafe_for_binlog to 1

In the above configuration, Gap Lock is still required in addition to foreign KEY constraints and uniqueness constraint checks, while the rest applies only to record lock locking, but it should be kept in mind that the above settings undermine the isolation of transactions and may cause the master to never be consistent for replication. Also, from a performance perspective, RC will not outperform the default transaction isolation level RR

In the InnoDB storage engine, for insert operations, it checks to see if the next record in the insert record is locked, and if it is locked, no query is allowed, and for the example above, session A has locked the b=3 record in Table Z, which is the locked (1,3) range. This is if inserting the same in other sessions will cause blocking

> INSERT  into SELECT 2,2;

Because when you insert a record with a value of 2 on secondary index B, the next record 3 is detected to be indexed, and the inserted value is modified to the following value, it can be executed immediately

> INSERT  into SELECT 2,0;

Again, for a lock on a unique key value, Next-key lock is demoted to the record lock in the unique indexed column that exists in the query. If a unique index consists of multiple columns, and the query is only one of several unique indexed columns, then the query is actually a range type query instead of a point type query, so the InnoDB storage engine still locks with Next-key lock

The algorithm of the row lock in MySQL