Ⅰ、InnoDB锁算法的介绍
首先明确一点,锁锁住的是什么？锁锁住的是索引
- Record Lock
单个行记录上的锁 - Gap Lock
锁定一个范围，但不包含记录本身 - Next-key Lock
Gap Lock + Record Lock 锁定一个范围，并且锁定记录本身
Ⅱ、模拟加锁场景
(root@localhost) [test]> desc l;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | NO | PRI | NULL | |
| b | int(11) | YES | MUL | NULL | |
| c | int(11) | YES | UNI | NULL | |
| d | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
(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.02 sec)
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> select * from l where a = 2 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 2 | 4 | 6 | 8 |
+---+------+------+------+
1 row in set (0.03 sec)
对主键为2的这条记录加锁,这里可以表示三个意思
①record lock：对2加X锁
②gap lock：对(负无穷,2)加X锁
thd1：hold 2 x gap
thd2：hold 2 x record
上面两个是兼容的,也就是说,thd2直接操作2这条记录是可以操作的,不需要等待
thd3：insert 1,这个线程就要wait,因为1在这个范围内
③next-key lock 锁住(负无穷,2]
oralce中只有record lock，没有别的意思
一般来说,此处我们根据不同事务隔离级别来分析这个加锁情况如下：
- rc
所有某条记录的加锁都是record锁,所有insert不用等待,并发度更好
--->lock_mode X locks rec but not gap - rr
所有对某条记录加锁都用的next-key locking,insert 并行性能或许有点差
--->lock_mode X
特殊情况：
会把加锁模式优化为record lock,前提是锁住的那个index是unique的,并且只返回(锁住)一条记录
(a,b)复合索引,查a=? 用的还是next-key locking,查a=?,b=?就会用record lock
Ⅲ、正儿八经的分析几个场景看看
3.1 对主键加锁
(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 a <=2 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 2 | 4 | 6 | 8 |
+---+------+------+------+
1 row in set (0.01 sec)
(root@localhost) [test]> show engine innodb statusG
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31220336, ACTIVE 16 sec
2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 416, OS thread handle 139830453040896, query id 5627 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`l` trx id 31220336 lock mode IX
RECORD LOCKS space id 1358 page no 3 n bits 72 index PRIMARY of table `test`.`l` trx id 31220336 lock_mode X
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 ;;
...
按道理我们锁住的应该是(负无穷,2],但实际上锁住的范围已经到了4这条记录,此时插入3是插不进去的,为什么？
为了保证解决幻读,要把2到它后面这条记录4这段范围锁住,这时候如果新插入一个2,在原来的2后面是插不进来的,如果4不锁住,新开一个线程可以删除4,又可以新插入一个4
rc的话就是只锁住记录本身,如下：
(root@localhost) [(none)]> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
(root@localhost) [(none)]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> select * from l where a <=2 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 2 | 4 | 6 | 8 |
+---+------+------+------+
1 row in set (0.00 sec)
(root@localhost) [test]> show engine innodb statusG
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31220337, ACTIVE 6 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 443, OS thread handle 139830452774656, query id 5649 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`l` trx id 31220337 lock mode IX
RECORD LOCKS space id 1358 page no 3 n bits 72 index PRIMARY of table `test`.`l` trx id 31220337 lock_mode X locks rec but not gap
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 ;;
...
唯一索引和主键情况一样
3.2 对二级索引加锁
先看rc事务隔离级别
(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.02 sec
(root@localhost) [test]> show engine innodb statusG
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31220338, ACTIVE 35 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 443, OS thread handle 139830452774656, query id 5653 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`l` trx id 31220338 lock mode IX
RECORD LOCKS space id 1358 page no 5 n bits 72 index b of table `test`.`l` trx id 31220338 lock_mode X locks rec but not gap
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 ;;
RECORD LOCKS space id 1358 page no 3 n bits 72 index PRIMARY of table `test`.`l` trx id 31220338 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 ;;
...
先对二级索引b加record锁：lock_mode X locks rec but not gap锁住了(6,4),6是二级索引,4是主键值
再对聚集索引加锁也是record locks,锁聚集索引index primary,锁住了a=4
再分析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 b = 6 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 4 | 6 | 8 | 10 |
+---+------+------+------+
1 row in set (0.01 sec)
(root@localhost) [test]> show engine innodb statusG
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31220340, ACTIVE 5 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 444, OS thread handle 139830446065408, query id 5673 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`l` trx id 31220340 lock mode IX
RECORD LOCKS space id 1358 page no 5 n bits 72 index b of table `test`.`l` trx id 31220340 lock_mode X
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 ;;
RECORD LOCKS space id 1358 page no 3 n bits 72 index PRIMARY of table `test`.`l` trx id 31220340 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 ;;
RECORD LOCKS space id 1358 page no 5 n bits 72 index b of table `test`.`l` trx id 31220340 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 ;;
...
这个就稍微有点复杂了,依稀可以看到是加了三个锁,我们挨个分析一波
- 第一个锁锁住索引b(4,6],next-key lock锁 lock_mode X
- 第二个锁是对主键a=4这条唯一记录的主键上加一个记录锁（因为唯一）,lock_mode X locks rec but not gap
- 第三个锁是gap before rec 锁住了b(6,8),也就是对8加了gap
为什么要锁住(6,8)?
假设不锁住这块,一个线程插入了(3,6),只锁住(4,6]那就可以插入了,那原来的线程第一次返回的只有一条b=6的记录,那第二次执行就出现了两条b=6,就幻读了
tips：
新插入的6是在(6,8)这个范围里的，新插入的相同的记录，都在已存在的记录后面 4 6 6(新插) 8