Repeatable-read Isolation Level && gap lock (GAP)

Repeatable-read Isolation Level && gap lock (GAP)

Table structure

CREATE TABLE T (name varchar (255) primary key, ID int not NULL, key idx_id (ID)), insert into T (name,id) VALUES (' a ', 15), (' B ', ten), (' C ', 6), (' d ', ten), (' F ', one-to-one), (' ZZ ', 2);

Session A

Mysql> begin; Query OK, 0 rows Affected (0.00 sec)

Session B

Mysql> begin; Query OK, 0 rows Affected (0.00 sec)

Session A

Mysql> SELECT * FROM t;+------+----+| name | ID |+------+----+|  ZZ | 2 | |  C | 6 | | B | 10 | | D | 10 | | f | 11 | | A | |+------+----+6 rows in Set (0.00 sec) mysql> select * FROM t where id = Ten for update;+------+----+| name | ID |+------+----+| B | 10 | | D | |+------+----+2 rows in Set (0.00 sec)

We perform the current read Select...for update operation in session A, how is this SQL statement locked? See

There is a gap lock in this picture, and the gap lock does not appear to be added to the record, it is like the position between the loading of two records, what is the gap lock?

In fact, this extra gap lock, is the RR isolation level, relative to the RC isolation level, there will be no magic read the key. Indeed, the gap locks the position of the lock, not the record itself, but the gap between two records. The so-called Phantom Reading, is the same transaction, do two times the current read (for example: SELECT * from t1 where id = ten for update;), then these two times the current read return is exactly the same record (the number of records consistent, the record itself is consistent), the second time the current read, no more than the first Returns more records (illusions).

How to guarantee two times the current read returns a consistent record, which is required between the first current read and the second current read, the other transaction will not insert a new record to satisfy the condition and commit. In order to realize this function, Gap lock was born.

, there are locations where you can insert a new item that satisfies the condition (id = 10), and given the order of the B + Tree index, the item that satisfies the condition must be stored continuously. Record [6,c] before, the id=10 record is not inserted, [6,c] and [10,b] can be inserted between [aa];[10,b] and [], you can insert a new [10,d] and so on; [10,bb],[10,c] and [10,d] can be inserted to meet the conditions of the [ 10,e],[10,z], and [11,f] does not insert a record that satisfies the condition. Therefore, in order to ensure [6,c] and [10,b] between [10,b] and [10,d], [10,d] and [11,f] will not insert new conditions to meet the record, MySQL chose to use the gap lock, the three gap to lock up.

Insert operations, such as insert [10,AA], are first positioned between [6,c] and [10,b], and before inserting, the gap is checked to see if it has been locked, and insert cannot insert a record if it is locked. Thus, by the first pass of the current reading, not only will meet the condition of the record lock (x lock), while still adding 3 gap lock, will likely be inserted to meet the condition record of the 3 gap to lock, to ensure that the subsequent insert can not insert a new id=10 record, also eliminate the same transaction of the second current read, A situation in which an illusion occurs.

Then we do a corresponding insert in session B to verify the above statement.

Session B

Perform the following insert operation

mysql> select * from t;+------+----+| name | id |+------+----+|  zz   |  2 | |  c    |  6 | |  b    | 10 | |  d    | 10 | |  f    | 11 | |  a    | 15 |+------+----+6 rows in set  (0.00 sec) Mysql> insert t (Name,id)  values  (' AA ', 10); error 1205  (HY000): lock wait timeout exceeded; try restarting  Transactionmysql> insert t (Name,id)  values  (' BB ', 10); error 1205  (HY000): lock wait timeout exceeded; try restarting  Transactionmysql> insert t (Name,id)  values  (' e ', 11); error 1205  (HY000): lock wait timeout exceeded; try restarting Transactionmysql> 

We can see that we have performed multiple insertions, both failed, because the gap on the gap lock on the cause of the insertion is not successful, it also prevents session a the second time the current reading will not appear phantom read.

When executing this SQL Insert T (name,id) VALUES (' BB ', 10), the corresponding lock information;

mysql> select * from innodb_locks;+--------------+-------------+-----------+----------- +------------+------------+------------+-----------+----------+-----------+| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table |  lock_index | lock_space | lock_page | lock_rec | lock_data  |+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----- -----+-----------+| 11662:65:4:5 | 11662       | x,gap      | RECORD    |  ' test '. ' t '  | idx_id      |         65 |          4 |        5 | 10,   ' d '    | |  11661:65:4:5 | 11661       | X          | RECORD    |  ' test '. ' t '  | idx_id      |         65 |          4 |        5 | 10,   ' d '    |+--------------+-------------+-----------+-----------+------------+------------+-- ----------+-----------+----------+-----------+2 rows in set  (0.00 sec)

===========end===========

Repeatable-read Isolation Level && gap lock (GAP)