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根本無法對數據做任何修改，那么這也就解決了幻讀問題。