本文主要介绍
1.mysql三种行锁介绍
2.RR模式下,next-key lock为什么可以解决幻读问题
首先,创建一张表:
mysql> show create table test01\G
*************************** 1. row ***************************
Table: test01
Create Table: CREATE TABLE `test01` (
`c1` bigint(20) NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
`c3` varchar(20) DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
初始化数据:
mysql> select * from test01;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 1 | 1 | a |
| 2 | 2 | b |
| 3 | 3 | c |
| 4 | 4 | d |
| 5 | 5 | e |
+----+------+------+
5 rows in set (0.00 sec)
record lock:
在数据行本身加的锁。
会话A
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> select c1 from test01 where c1=1 for update;
+----+
| c1 |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
会话B
mysql> update test01 set c1=6 where c1=1; --hang
c1=1上有record lock,所以会话B无法修改。
mysql> SHOW ENGINE INNODB STATUS\G
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 72 index PRIMARY of table `ming`.`test01` trx id 1807 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000000000708; asc ;;
2: len 7; hex a80000011c0110; asc ;;
3: len 4; hex 80000001; asc ;;
4: len 1; hex 61; asc a;;
gap lock:
在gap上加的锁,不包括记录本身。
需要建立一个非唯一性的索引,不能是主键或者唯一性索引,否则只有record lock。
mysql> create index idx_test01_c2 on test01(c2);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
初始化数据:
mysql> select * from test01;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 1 | 1 | a |
| 3 | 3 | c |
| 5 | 5 | e |
| 7 | 7 | e |
+----+------+------+
4 rows in set (0.00 sec)
会话A:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select c2 from test01 where c2>3 and c2<5 for update;
Empty set (0.00 sec)
会话B
mysql> insert into test01 select 4,4,'e'; --hang
(3,3)与(5,5)之间的gap被锁住了,所以无法插入(4,4)。
------- TRX HAS BEEN WAITING 32 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 4 n bits 72 index idx_test01_c2 of table `ming`.`test01` trx id 1925 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 8; hex 8000000000000005; asc ;;
此时c2=3和c2=5两行数据是可以更改的,但是改变后的值不能在现在的gap中。关于数据行的gap具体的可以参考下面的例子:、
next-key lock
对数据记录本身和gap都加锁,相当于record lock + gap lock。
c3列无用,删除
mysql> alter table test01 drop column c3;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
初始化数据:
mysql> select * from test01;
+----+------+
| c1 | c2 |
+----+------+
| 2 | 2 |
| 5 | 5 |
| 7 | 7 |
+----+------+
3 rows in set (0.00 sec)
会话A:
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test01 where c2=5 for update;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
+----+------+
1 row in set (0.00 sec)
会话B:
insert into test01 select 1,2; --ok
insert into test01 select 3,2; --hang
insert into test01 select 4,5; --hang
insert into test01 select 6,5; --hang
insert into test01 select 8,7; --ok
update test01 set c2=3 where c2=2; --hang
update test01 set c2=1 where c2=2; --ok
update test01 set c2=6 where c2=7; --hang
update test01 set c2=8 where c2=7; --ok
会话A在c2=5及gap上加了next-key lock,在数据行本身加了record lock,在c2=5数据行与其相邻的上下两行数据(c2=2,c2=7)之间的gap加了gap lock。
注意到(1,2)可以插入,但是(3,2)却无法插入,有人或许会有疑问,c2都是2,为什么一个可以插入一个不能插入?注意,next-key lock是在索引上加锁,索引是有顺序的,如下:
(1,2)--> (2,2) --> (5,5)
|-gap-|
注意到(1,2)在gap之外,所以是可以插入的。假设(3,2)可以插入,那么它们在索引中的顺序将是
(2,2) --> (3,2) --> (5,5)
|----gap----|
(3,2)将会插入到gap中,所以(3,2)是无法插入的。
会话B其它的sql也是这个道理。
经常看到一个说法是,next-key lock解决了RR隔离级别下的幻读问题,那么是如何做到的呢?
什么叫幻读?
一个事务里面,多次查询出的结果集不一致,这种情况就叫做幻读。
下面是幻读现象的一个例子:
会话A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test01;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 5 | 5 |
| 7 | 7 |
+----+------+
3 rows in set (0.00 sec)
会话B
mysql> insert into test01 select 8,8 ;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
会话A
mysql> select * from test01;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 5 | 5 |
| 7 | 7 |
+----+------+
3 rows in set (0.00 sec)
mysql> insert into test01 select 8,8 ;
ERROR 1062 (23000): Duplicate entry '8' for key 'PRIMARY'
会话A明明没有读到c1=8的记录,但就是无法插入,这就出现了幻读现象。
如果会话A在开始事务后,
select * from test01 for update;
或者
select * from test01 lock in share mode;
那么会话B根本无法对数据做任何修改,那么这也就解决了幻读问题。