MySQL Lock process details (7)-Initial understanding of MySQL Gap lock

Initial understanding of MySQL Gap lock

Beginning to know the gap of MySQL, think this design is unique, and other database practices are not the same, so tidy up a simple memo (although about the gap lock, the relevant information is many)

1. What is Gap

To put it bluntly, gap is the gap in the index tree where new records are inserted. The corresponding gap lock is added to the gap lock, there is a next-key lock, is a record + record the gap in front of the combination of locks.

2. The role of Gap lock or Next-key lock

Http://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

Simply speaking is to prevent Phantom reading. The insertion of a new record that prevents a particular condition from being locked, because the gap lock (insert Intention Locks) is also acquired when inserting.

3. When will I get gap lock or Nextkey lock?

This is related to the isolation level, in which only specific operations under repeatable read or above isolation level will get gap lock or nextkey lock.

Http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

2.1 Repeatable READ

Locking Reads,update and Delete, a gap or Next-key lock is acquired in addition to a unique search for a unique index. Lock the range of its scan.

The following is a test of a non-unique index.

The table is defined as follows:

Mysql> Show CREATE TABLE tb2;+-------+------------------------------------------------------------------------- -----------------------------------------------------------------------+| Table | Create Table                                                                                                                                   |+-------+------------------------------------------------------------------------------------ ------------------------------------------------------------+| TB2   | CREATE TABLE ' tb2 ' (  ' id ' int (one) default NULL,  ' C1 ' int (one) ' Default NULL,  

There are 3 records in the table: 10,20,30.

Mysql> SELECT * FROM tb2;+------+------+| ID   | C1   |+------+------+|   Ten |    0 | |   |    0 | |   30 |    

Under REPEATABLE read, update a record without committing, and then see which actions can block another session.

SESSION 1:

Record of update id=20 in SESSION 1

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Update TB2 set c1=2 where id=20; Query OK, 1 row affected (0.04 sec) Rows matched:1  changed:1  

SESSION 2:

SESSION 2, the insert operation was performed and the [10,30] range was found to be unable to insert data.

For update operations, only 20 of this record cannot be updated because the update operation does not get the gap lock.

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Update TB2 set c1=4 where id=10; Query OK, 0 rows Affected (0.00 sec) rows matched:1  changed:0  warnings:0mysql> update tb2 set c1=4 where id=2 0; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transactionmysql> update TB2 set c1=4 where id=30; Query OK, 0 rows Affected (0.00 sec) rows Matched:2  changed:0  

If the table scan of Session 1 is not indexed, then gap or next-key locks the entire table, meaning that no value can be inserted.

2.2 READ COMMITTED

Only locks the existing record, does not add the gap lock.

2.3 SERIALIZABLE

The main difference between repeatable and read is to turn a normal select into a select ... Lock in SHARE MODE, that is, a gap or Next-key lock is acquired for normal select.

4. Repeatable Read and Phantom reading

In "Consistent-read", repeatable read reads the snapshot at the beginning of the transaction, even though other transactions are often not visible when the new rows are inserted, so it is possible to avoid phantom reading in common situations. However, "locking read" or the update, when deleted, will see the submitted modifications, including the newly inserted rows.

Http://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html

Let's look at an example

SESSION 1:

Mysql> START TRANSACTION; Query OK, 0 rows Affected (0.00 sec) mysql> Select Id,c1 from tb1 where id=1;+----+------+| ID | C1   |+----+------+|  1 |  

SESSION 2:

mysql> Update TB1 set c1=101 where id = 1; Query OK, 1 row affected (0.03 sec) Rows matched:1  changed:1  

SESSION 1:

Mysql> Select Id,c1 from tb1 where id=1 LOCK in SHARE mode;+----+------+| ID | C1   |+----+------+|  1 |  101 |+----+------+1 row in Set (0.00 sec) mysql> Select Id,c1 from tb1 where id=1;+----+------+| ID | C1   |+----+------+|  1 |  |+----+------+1 row in Set (0.00 sec) mysql> Update tb1 set c1=c1+1000 where id=1; Query OK, 1 row affected (0.02 sec) Rows matched:1  changed:1  warnings:0mysql> Select Id,c1 from tb1 where id= 1;+----+------+| ID | C1   |+----+------+|  

The above update behavior violates REPEATABLE Read's promise to see the concurrency update of other transactions since the start of the transaction. This requires special attention to application development, where other databases are usually error-aware.

5. Other

There is an important difference between RR and RC, the scanned but mismatched records are not locked, or the semi-consistent read is added first. However, the scanned records under RR are locked. This difference is greatly affected by the updated scene with full table scan. Detailed reference http://hedengcheng.com/?p=771, about MySQL lock processing, this article speaks very thoroughly!

6. Reference

• http://hedengcheng.com/?p=771
• Http://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html
• Http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
• Http://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html
• Http://blog.chinaunix.net/uid-20726500-id-3902528.html
• http://blog.itpub.net/22664653/viewspace-750824/
• Http://www.bitscn.com/pdb/mysql/201405/227973.html

Transferred from: http://blog.chinaunix.net/uid-20726500-id-5749804.html

MySQL Lock process details (7)-Initial understanding of MySQL Gap lock