mysql锁之三种行级锁介绍

本文主要介绍 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根本无法对数据做任何修改，那么这也就解决了幻读问题。