Algorithms for Locking in InnoDB (2)

Ⅰ, previous section review

Session1:

([email protected]) [test]> select * from l;+---+------+------+------+| a | b    | c    | d    |+---+------+------+------+| 2 |    4 |    6 |    8 || 4 |    6 |    8 |   10 || 6 |    8 |   10 |   12 || 8 |   10 |   12 |   14 |+---+------+------+------+4 rows in set (0.00 sec)([email protected]) [test]> show variables like ‘tx_isolation‘;+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| tx_isolation  | REPEATABLE-READ |+---------------+-----------------+1 row in set (0.01 sec)([email protected]) [test]> begin;Query OK, 0 rows affected (0.00 sec)([email protected]) [test]> select * from l where b = 6 for update;+---+------+------+------+| a | b    | c    | d    |+---+------+------+------+| 4 |    6 |    8 |   10 |+---+------+------+------+1 row in set (0.09 sec)pk  2   4   6   8key 4   6   8   10二级索引锁住的是(4,6]&&(6,8)主键锁住的是4

Session2:

([email protected]) [(test)]> begin;Query OK, 0 rows affected (0.00 sec)([email protected]) [test]> insert into l values (3,4,14,20);hang~~~

Session1:

([email protected]) [(none)]> show engine innodb status\G...MySQL thread id 1087, OS thread handle 139830446065408, query id 7300 localhost root updateinsert into l values (3,4,14,20)------- TRX HAS BEEN WAITING 19 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 1358 page no 5 n bits 72 index b of table `test`.`l` trx id 31220594 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000006; asc     ;; 1: len 4; hex 80000004; asc     ;;...4在被锁住的范围之内,所以插不进去

Can that (1,4,14,20) be inserted? You can insert the answer first.

Why?
There's a vague question here.

The secondary index is sorted by the primary key value sort, and the lock range contains the primary key value, as follows:

((4,2),(6,4)],((6,4),(8,6))

(4,3) Within this range, so cannot be inserted, and (4,1) is not in this range, can be inserted

Insert (5,4,14,20) also blocks, (4,5) in the range

Ⅱ, walk index query record does not exist 2.1 RR transaction ISOLATION LEVEL

([email protected]) [test]> begin;Query OK, 0 rows affected (0.00 sec)([email protected]) [test]> select * from l where b = 12 for update;Empty set (0.00 sec)([email protected]) [test]> show engine innodb status\G...---TRANSACTION 31220600, ACTIVE 7 sec2 lock struct(s), heap size 1136, 1 row lock(s)MySQL thread id 1104, OS thread handle 139830452774656, query id 7383 localhost root startingshow engine innodb statusTABLE LOCK table `test`.`l` trx id 31220600 lock mode IXRECORD LOCKS space id 1358 page no 5 n bits 72 index b of table `test`.`l` trx id 31220600 lock_mode XRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;...heap no 1    0和1的heap no表示的是min和max的记录,虚拟的n_fields 1   只有一个列,伪列key min 4 6 8 10 max

A lock on Max with a range of (10, positive infinity) means that inserting any record greater than 10 is not possible

RR level, if searching for a record is not found, it will be locked on Max, which means that the range of records not found can not be inserted

Why is it?
If the first read 12 did not read to not lock 10 back, the thread inserted 12 this record, the second time to read will read 12, there is a phantom read

Again
Session1:

([email protected]) [test]> begin;Query OK, 0 rows affected (0.00 sec)([email protected]) [test]> select * from l where b = 7 for update;Empty set (0.00 sec)([email protected]) [test]> show engine innodb status\G···---TRANSACTION 31220601, ACTIVE 51 sec2 lock struct(s), heap size 1136, 1 row lock(s)MySQL thread id 1104, OS thread handle 139830452774656, query id 7387 localhost root startingshow engine innodb statusTABLE LOCK table `test`.`l` trx id 31220601 lock mode IXRECORD LOCKS space id 1358 page no 5 n bits 72 index b of table `test`.`l` trx id 31220601 lock_mode X locks gap before recRecord lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000008; asc     ;; 1: len 4; hex 80000006; asc     ;;···在8上面加了一个gap锁,8本身是不锁的

Session2:

([email protected]) [test]> begin;Query OK, 0 rows affected (0.00 sec)([email protected]) [test]> select * from l where b = 8 for update;+---+------+------+------+| a | b    | c    | d    |+---+------+------+------+| 6 |    8 |   10 |   12 |+---+------+------+------+1 row in set (0.00 sec)这时候8这条记录上又有了Next-key Lock锁,锁住6到8,8本身也被锁住,8上面两把锁是不抵触的

2.2 RC Transaction ISOLATION LEVEL

([email protected]) [test]> Show variables like ' tx_isolation '; +---------------+----------------+| variable_name | Value |+---------------+----------------+| tx_isolation | read-committed |+---------------+----------------+1 row in Set (0.00 sec) ([email protected]) [test]> begin; Query OK, 0 rows Affected (0.00 sec) ([email protected]) [test]> select * from l where B = n for update; Empty Set (0.00 sec) ([email protected]) [test]> show engine InnoDB status\g ...------------ Transactions------------Trx ID counter 31220604Purge done for Trx ' s N:o < 31220593 undo N:o < 0 state:running but I Dlehistory list length 35LIST of transactions for each SESSION:---TRANSACTION 421305875783280, not started0 lock struct (s) , heap size 1136, 0 row lock (s)---TRANSACTION 421305875781456, not started0 lock struct (s), heap size 1136, 0 row lock (s)- --transaction 31220603, ACTIVE 6 sec1 lock struct (s), heap size 1136, 0 row lock (s) MySQL thread ID 1106, OS thread HanDle 139830446065408, query id 7436 localhost root startingshow engine innodb statustable LOCK table ' Test '. ' l ' Trx ID 3122 0603 Lock Mode IX ...

Will find that there is no lock, read No 12 This record, directly released, RC does not solve the phantom read, so see RC Insert concurrency performance will be better

Ⅲ, do not go index situation how to lock? 3.1 RR Transaction ISOLATION LEVEL

([email protected]) [test]> Show variables like ' tx_isolation '; +---------------+----------------+| variable_name | Value |+---------------+----------------+| tx_isolation | read-committed |+---------------+----------------+1 row in Set (0.01 sec) ([email protected]) [test]> begin; Query OK, 0 rows Affected (0.00 sec) ([email protected]) [test]> select * from l where d = Ten for update;+---+----- -+------+------+| A | B | C | D |+---+------+------+------+|    4 |    6 |   8 | |+---+------+------+------+1 row in Set (0.00 sec) ([email protected]) [test]> show engine InnoDB status\g ... LIST of transactions for each SESSION:---TRANSACTION 421305875783280, not started0 lock struct (s), heap size 1136, 0 row L Ock (s)---TRANSACTION 421305875781456, not started0 lock struct (s), heap size 1136, 0 row lock (s)---TRANSACTION 31220604, A ctive SEC2 lock struct (s), heap size 1136, 1 row lock (s) MySQL thread ID 1106, OS thread handle 139830446065408, QuerY id 7446 localhost root startingshow engine InnoDB statustable lock table ' test '. ' l ' Trx ID 31220604 LOCK mode ixrecord l Ocks Space ID 1358 page No 3 n bits index PRIMARY of table ' Test '. ' l ' Trx ID 31220604 lock_mode X locks Rec but gap Record Lock, Heap No 3 physical record:n_fields 6; Compact format; Info bits 0 0:len 4; Hex 80000004; ASC;; 1:len 6; Hex 000001c1b93a; ASC:;; 2:len 7; Hex e1000001a90110; ASC;; 3:len 4; Hex 80000006; ASC;; 4:len 4; Hex 80000008; ASC;; 5:len 4; Hex 8000000a; ASC;; ...

A record lock is generated, and the primary key for the d=10 record is locked

3.2 RR Transaction ISOLATION LEVEL

([email protected]) [test]> Show variables like ' tx_isolation '; +---------------+-----------------+| variable_name | Value |+---------------+-----------------+| tx_isolation | Repeatable-read |+---------------+-----------------+1 row in Set (0.00 sec) ([email protected]) [test]> begin; Query OK, 0 rows Affected (0.00 sec) ([email protected]) [test]> select * from l where d = Ten for update;+---+----- -+------+------+| A | B | C | D |+---+------+------+------+|    4 |    6 |   8 | |+---+------+------+------+1 row in Set (0.00 sec) ([email protected]) [test]> show engine InnoDB status\g ... LIST of transactions for each SESSION:---TRANSACTION 421305875783280, not started0 lock struct (s), heap size 1136, 0 row L Ock (s)---TRANSACTION 421305875781456, not started0 lock struct (s), heap size 1136, 0 row lock (s)---TRANSACTION 31220606, A ctive SEC2 lock struct (s), Heap size 1136, 5 row lock (s) MySQL thread ID 1106, OS thread handle 139830446065408, Query ID 7459 localhost root startingshow engine InnoDB statustable lock table ' test '. ' l ' Trx ID 31220606 LOCK Mode ixrec  ORD LOCKS Space ID 1358 page No 3 n bits index PRIMARY of table ' Test '. ' l ' Trx ID 31220606 lock_mode xrecord lock, Heap No 1 physical record:n_fields 1; Compact format; Info bits 0 0:len 8; Hex 73757072656d756d; ASC supremum;; Record Lock, Heap No 2 physical record:n_fields 6; Compact format; Info bits 0 0:len 4; Hex 80000002; ASC;; 1:len 6; Hex 000001c1b939; ASC 9;; 2:len 7; Hex e0000001a80110; ASC;; 3:len 4; Hex 80000004; ASC;; 4:len 4; Hex 80000006; ASC;; 5:len 4; Hex 80000008; ASC;; Record Lock, Heap No 3 physical record:n_fields 6; Compact format; Info bits 0 0:len 4; Hex 80000004; ASC;; 1:len 6; Hex 000001c1b93a; ASC:;; 2:len 7; Hex e1000001a90110; ASC;; 3:len 4; Hex 80000006; ASC;; 4:len 4; Hex 80000008; ASC;; 5:len 4; Hex 8000000a; ASC;; Record lock, Heap No 4 physical RECORD: N_fields 6; Compact format; Info bits 0 0:len 4; Hex 80000006; ASC;; 1:len 6; Hex 000001c1b93f; ASC;; 2:len 7; Hex e40000015d0110; ASC];; 3:len 4; Hex 80000008; ASC;; 4:len 4; Hex 8000000a; ASC;; 5:len 4; Hex 8000000c; ASC;; Record Lock, Heap No 5 physical record:n_fields 6; Compact format; Info bits 0 0:len 4; Hex 80000008; ASC;; 1:len 6; Hex 000001c1b940; ASC @;; 2:len 7; Hex e50000015f0110; ASC _;; 3:len 4; Hex 8000000a; ASC;; 4:len 4; Hex 8000000c; ASC;; 5:len 4; Hex 8000000e; ASC;; ...

Next-key Lock Locks The 2,4,6,8 of the primary key

(负无穷,2],(2,4],(4,6],(6,8],(8,正无穷)

This is not a table lock (no table upgrade), but the expression is similar to the entire lock, if the table has a 100w record, will produce 100w lock, lock mode is Next-key Locking, any record of the insertion and update is not possible, the cost of the lock is very high

Because the subsequent insertion of any record can be d=10, so any record is to be locked, just plug in a d=10 record to plug that is chaos, and Phantom Read, then do not allow any record to insert it

Tips
The RR itself does not resolve the Phantom read, only the Phantom read is resolved under the serialized transaction isolation level, but the MySQL lock is special, and the magic read is resolved under the RR isolation level

Algorithms for Locking in InnoDB (2)