Ⅰ、上节回顾
session1：
(root@localhost) [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)
(root@localhost) [test]> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [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 8
key 4 6 8 10
二级索引锁住的是(4,6]&&(6,8)
主键锁住的是4
session2：
(root@localhost) [(test)]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> insert into l values (3,4,14,20);
hang~~~
session1：
(root@localhost) [(none)]> show engine innodb status\G
...
MySQL thread id 1087, OS thread handle 139830446065408, query id 7300 localhost root update
insert 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 waiting
Record 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在被锁住的范围之内,所以插不进去
那(1,4,14,20)能插入吗？先公布答案,是可以插入的
为什么？
这里有一个隐晦的问题
二级索引排序的时候包含主键值排序,锁的时候范围也是包含主键值,如下：
((4,2),(6,4)],((6,4),(8,6))
(4,3)在这个范围内,所以不能插,而(4,1)不在这范围内,可以插
insert (5,4,14,20)也会阻塞,(4,5)在范围中
Ⅱ、走索引查询的记录不存在
2.1 rr事务隔离级别
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> select * from l where b = 12 for update;
Empty set (0.00 sec)
(root@localhost) [test]> show engine innodb status\G
...
---TRANSACTION 31220600, ACTIVE 7 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1104, OS thread handle 139830452774656, query id 7383 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`l` trx id 31220600 lock mode IX
RECORD LOCKS space id 1358 page no 5 n bits 72 index b of table `test`.`l` trx id 31220600 lock_mode X
Record 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
在max上加了锁,范围为(10,正无穷),意味着插入任意大于10的记录都是不可以的
rr级别时,如果搜索一条记录搜不到,就会在max上加锁,意味着这条没查到的记录之后的范围都插不了
为什么呢?
如果第一次读12没有读到不把10往后的都锁住,那一个线程插入了12这条记录,第二次再去读就会读到12,就发生了幻读
再来
session1：
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> select * from l where b = 7 for update;
Empty set (0.00 sec)
(root@localhost) [test]> show engine innodb status\G
···
---TRANSACTION 31220601, ACTIVE 51 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1104, OS thread handle 139830452774656, query id 7387 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`l` trx id 31220601 lock mode IX
RECORD 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 rec
Record 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：
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [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事务隔离级别
(root@localhost) [test]> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> select * from l where b = 12 for update;
Empty set (0.00 sec)
(root@localhost) [test]> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 31220604
Purge done for trx's n:o < 31220593 undo n:o < 0 state: running but idle
History list length 35
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421305875783280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421305875781456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 31220603, ACTIVE 6 sec
1 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 1106, OS thread handle 139830446065408, query id 7436 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`l` trx id 31220603 lock mode IX
...
会发现并没有锁,读到没有12这条记录,直接就释放了,rc不解决幻读,这么看rc的insert并发性能会更好
Ⅲ、不走索引的情况怎么锁？
3.1 rc事务隔离级别
(root@localhost) [test]> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.01 sec)
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> select * from l where d = 10 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 4 | 6 | 8 | 10 |
+---+------+------+------+
1 row in set (0.00 sec)
(root@localhost) [test]> show engine innodb status\G
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421305875783280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421305875781456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 31220604, ACTIVE 11 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1106, OS thread handle 139830446065408, query id 7446 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`l` trx id 31220604 lock mode IX
RECORD LOCKS space id 1358 page no 3 n bits 72 index PRIMARY of table `test`.`l` trx id 31220604 lock_mode X locks rec but not 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 ;;
...
会产生一个记录锁,对d=10这条记录对应的主键加锁
3.2 rr事务隔离级别
(root@localhost) [test]> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> select * from l where d = 10 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 4 | 6 | 8 | 10 |
+---+------+------+------+
1 row in set (0.00 sec)
(root@localhost) [test]> show engine innodb status\G
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421305875783280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421305875781456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 31220606, ACTIVE 22 sec
2 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 1106, OS thread handle 139830446065408, query id 7459 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`l` trx id 31220606 lock mode IX
RECORD LOCKS space id 1358 page no 3 n bits 72 index PRIMARY of table `test`.`l` trx id 31220606 lock_mode X
Record 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锁住了主键的2,4,6,8
(负无穷,2],(2,4],(4,6],(6,8],(8,正无穷)
这并不是表锁(没有表升级),只是表现形式类似整个锁住,如果表有100w记录,会产生100w个lock,锁模式是Next-key Locking,任何记录的插入和更新都是不可以的,锁的代价很大
因为后面插入任何一条记录都可以是d=10,所以任何一条记录都要被锁住,随便插一个d=10的记录都给插那就乱套了,又幻读了,那就不允许任何记录插入吧
tips：
rr本身不解决幻读,只有在序列化的事务隔离级别之下才解决幻读,但是MySQL的锁比较特殊,在rr隔离级别下就解决幻读