http://blog.51cto.com/74567456/1887690
類似的文章：https://www.cnblogs.com/zhoujinyi/p/3435982.html
總結：
1. 通過實踐闡述了gap lock 的開啟與關閉：
A. 將事務隔離級別設置為RC
B. 將參數innodb_locks_unsafe_for_binlog設置為1
C. 確保where索引唯一 ，從而避讓 gap lock
2. 闡述了一些概念：
認識鎖的算法
nnoDB存儲引擎的鎖的算法有三種：
Record lock：單個行記錄上的鎖
Gap lock：間隙鎖，鎖定一個范圍，不包括記錄本身
Next-key lock：record+gap 鎖定一個范圍，包含記錄本身
Lock的精度（type）分為 行鎖、表鎖、意向鎖
Lock的模式（mode）分為:
鎖的類型 ——【讀鎖和寫鎖】或者【共享鎖和排他鎖】即 【X or S】
鎖的范圍 ——【record lock、gap lock、Next-key lock】
知識點
innodb對于行的查詢（rr級別的當前讀）使用next-key lock
Next-locking keying為了解決Phantom Problem幻讀問題
當查詢的索引含有唯一屬性時，將next-key lock降級為record key
Gap鎖設計的目的是為了阻止多個事務將記錄插入到同一范圍內，而這會導致幻讀問題的產生
有兩種方式顯式關閉gap鎖：（除了外鍵約束和唯一性檢查外，其余情況僅使用record lock） A. 將事務隔離級別設置為RC B. 將參數innodb_locks_unsafe_for_binlog設置為1
Next-key locking是如何解決幻讀問題的
首先什么是幻讀呢？
舉個例子，兩個男孩同時在追求一個女生的故事
A問：你有男朋友嗎？女孩對他說沒有。A追求女孩的事件還沒有提交，就是繼續追求哈。
就在A追求的同時，B也在追求，并且直接讓女孩做他的女朋友，女孩答應了，B的追求事件結束。
A又問：你有男朋友嗎？ 女孩對他說我已經有男朋友了！ 嗚嗚嗚 ！剛才你還沒有的，怎么現在就有了呢？
女孩說，你也沒說過你追我的時候不讓別人追我啊！... ... A哭著走了。
幻讀 Phantom Problem 是指在同一事務下，連續執行兩次相同的sql語句可能導致不同的結果，第二次的sql語句可能會返回之前不存在的行。
在剛才我舉的例子里，A雖然問了女孩有沒有男朋友，但是沒有告訴女孩，在他追求時，不可以接受別人的追求，所以悲催的結局。
那么A怎么才能在他追求事件結束前讓女孩不答應別人的追求呢？
innodb中的RR隔離級別是通過next-key locking是如何解決幻讀問題的，就是鎖住一個范圍。
那么如果你是A你怎么做呢？你肯定要跟女孩說，只要我開始追求你，問了你有沒有男朋友，在我結束追求你之前，你不可以答應別人的追求！我要把你腦子里記錄男朋友的區域全部鎖起來，啊哈啊！
下面我們來做一個測試，分別在RR和RC隔離級別中來實現：
測試使用表db1.t1 (a int primary key) ,記錄有1,3,5
| T1 RC |
T2 RR |
| begin; |
begin; |
| set session transaction isolation level READ COMMITTED; |
|
| select * from db1.t1 where a>3 for update; |
|
| 查詢結果為5 |
|
|
insert into db1.t1 values (4); |
|
commit; |
| select * from db1.t1 where a>3; |
|
| 查詢結果為4 5 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
MariaDB[db1]>createtablet1(aintprimarykey);
QueryOK,0rowsaffected(0.22sec)
MariaDB[db1]>insertintot1values(1),(3),(5);
QueryOK,3rowsaffected(0.02sec)
Records:3Duplicates:0Warnings:0
MariaDB[db1]>begin;
QueryOK,0rowsaffected(0.00sec)
MariaDB[db1]>setsessiontransactionisolationlevelreadco
QueryOK,0rowsaffected(0.01sec)
MariaDB[db1]>select*fromdb1.t1wherea>3forupdate;
+---+
|a|
+---+
|5|
+---+
1rowinset(0.01sec)
MariaDB[db1]>begin;
QueryOK,0rowsaffected(0.00sec)
MariaDB[db1]>insertintodb1.t1values(4);
QueryOK,1rowaffected(0.00sec)
MariaDB[db1]>commit;
QueryOK,0rowsaffected(0.03sec)
MariaDB[db1]>select*fromdb1.t1wherea>3forupdate;
+---+
|a|
+---+
|4|
|5|
+---+
2rowsinset(0.00sec)
|
將會話中的隔離界別改為RR，并刪除a=4記錄。
1
2
3
4
5
|
MariaDB[db1]>setsessiontransactionisolationlevelrepeatableread;
QueryOK,0rowsaffected(0.00sec)
MariaDB[db1]>deletefromdb1.t1wherea=4;
QueryOK,1rowaffected(0.00sec)
|
| T1 RR |
T2 RR |
| begin; |
begin; |
| select * from db1.t1 where a>3 for update; |
|
| 查詢結果為5 |
|
|
insert into db1.t1 values (4); |
|
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
|
commit; |
| select * from db1.t1 where a>3; |
|
| 查詢結果為5 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
MariaDB[(none)]>begin;
QueryOK,0rowsaffected(0.00sec)
MariaDB[(none)]>select*fromdb1.t1wherea>3forupdate;
+---+
|a|
+---+
|5|
+---+
1rowinset(0.02sec)
MariaDB[(none)]>begin;
QueryOK,0rowsaffected(0.00sec)
MariaDB[(none)]>insertintodb1.t1values(4);
ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
MariaDB[(none)]>commit;
QueryOK,0rowsaffected(0.00sec)
MariaDB[(none)]>select*fromdb1.t1wherea>3forupdate;
+---+
|a|
+---+
|5|
+---+
1rowinset(0.02sec)
|
認識鎖的算法
nnoDB存儲引擎的鎖的算法有三種：
Record lock：單個行記錄上的鎖
Gap lock：間隙鎖，鎖定一個范圍，不包括記錄本身
Next-key lock：record+gap 鎖定一個范圍，包含記錄本身
Lock的精度（type）分為 行鎖、表鎖、意向鎖
Lock的模式（mode）分為:
鎖的類型 ——【讀鎖和寫鎖】或者【共享鎖和排他鎖】即 【X or S】
鎖的范圍 ——【record lock、gap lock、Next-key lock】
知識點
innodb對于行的查詢使用next-key lock
Next-locking keying為了解決Phantom Problem幻讀問題
當查詢的索引含有唯一屬性時，將next-key lock降級為record key
Gap鎖設計的目的是為了阻止多個事務將記錄插入到同一范圍內，而這會導致幻讀問題的產生
有兩種方式顯式關閉gap鎖：（除了外鍵約束和唯一性檢查外，其余情況僅使用record lock） A. 將事務隔離級別設置為RC B. 將參數innodb_locks_unsafe_for_binlog設置為1
實踐1： 驗證next-key lock降級為record key
創建db1.t1表，有列a和b，分別為char(10)和int型，并且b為key，注意b列為索引列，但并不是主鍵，因此不是唯一的。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
MariaDB[db1]>createtabledb1.t1(achar(10),bint,key(b));
QueryOK,0rowsaffected(0.03sec)
MariaDB[db1]>insertintodb1.t1values('batman',1),('superman',3),('leo',5);
QueryOK,3rowsaffected(0.15sec)
Records:3Duplicates:0Warnings:0
MariaDB[db1]>select*fromdb1.t1;
+----------+------+
|a|b|
+----------+------+
|batman|1|
|superman|3|
|leo|5|
+----------+------+
3rowsinset(0.02sec)
|
接下來開啟兩個事務T1和T2，T1中查看b=3的行，顯式加排他鎖；T1未提交事務時，T2事務開啟并嘗試插入新行a='batman',b=2和a='batman',b=4；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
MariaDB[db1]>begin;
QueryOK,0rowsaffected(0.00sec)
MariaDB[db1]>select*fromdb1.t1whereb=3forupdate;
+----------+------+
|a|b|
+----------+------+
|superman|3|
+----------+------+
1rowinset(0.12sec)
MariaDB[db1]>begin;
QueryOK,0rowsaffected(0.00sec)
MariaDB[db1]>insertintodb1.t1values('batman',2);
ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
MariaDB[db1]>insertintodb1.t1values('batman',4);
ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
|
發現T2事務中不能插入新行a='batman',b=2和a='batman',b=4；可以查看當前innodb鎖的信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
|
MariaDB[db1]>select*frominformation_schema.innodb_locksG;
***************************1.row***************************
lock_id:111B:0:334:3
lock_trx_id:111B
lock_mode:X,GAP
lock_type:RECORD
lock_table:`db1`.`t1`
lock_index:`b`
lock_space:0
lock_page:334
lock_rec:3
lock_data:3,0x00000000020E
***************************2.row***************************
lock_id:111A:0:334:3
lock_trx_id:111A
lock_mode:X
lock_type:RECORD
lock_table:`db1`.`t1`
lock_index:`b`
lock_space:0
lock_page:334
lock_rec:3
lock_data:3,0x00000000020E
2rowsinset(0.01sec)
ERROR:Noqueryspecified
MariaDB[db1]>select*frominformation_schema.innodb_lock_waitsG;
***************************1.row***************************
requesting_trx_id:111B
requested_lock_id:111B:0:334:3
blocking_trx_id:111A
blocking_lock_id:111A:0:334:3
1rowinset(0.09sec)
MariaDB[db1]>select*frominformation_schema.innodb_lock_waitsG;
***************************1.row***************************
requesting_trx_id:111B
requested_lock_id:111B:0:334:4
blocking_trx_id:111A
blocking_lock_id:111A:0:334:4
1rowinset(0.00sec)
ERROR:Noqueryspecified
MariaDB[db1]>select*frominformation_schema.innodb_locksG;
***************************1.row***************************
lock_id:111B:0:334:4
lock_trx_id:111B
lock_mode:X,GAP
lock_type:RECORD
lock_table:`db1`.`t1`
lock_index:`b`
lock_space:0
lock_page:334
lock_rec:4
lock_data:5,0x00000000020F
***************************2.row***************************
lock_id:111A:0:334:4
lock_trx_id:111A
lock_mode:X,GAP
lock_type:RECORD
lock_table:`db1`.`t1`
lock_index:`b`
lock_space:0
lock_page:334
lock_rec:4
lock_data:5,0x00000000020F
2rowsinset(0.11sec)
ERROR:Noqueryspecified
|
我們看到T2事務的兩次插入動作都在請求排他鎖，但是此時T1事務已經在加了next-key lock(record + gap)，表現范圍為b的(1,5)，包括記錄3，所以T2事務在T1事務解鎖之間，不能插入到b的(1,5)范圍內
* lock_mode: X,GAPlock_mode 可以理解為讀鎖還是寫鎖？；是在什么范圍上鎖？;此處加的寫鎖即排他鎖；范圍是(1,5)
* lock_type: RECORD表示鎖的精度，根據存儲引擎不同，innodb是行鎖，MYISAM是表鎖
刪除db1.t1表，重新創建db1.t1表，有列a和b，分別為char(10)和int型，并且b為primay key，因此b列是唯一的。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
MariaDB[db1]>droptablest1;
QueryOK,0rowsaffected(0.12sec)
MariaDB[db1]>createtabledb1.t1(achar(10),bint,primarykey(b));
QueryOK,0rowsaffected(0.02sec)
MariaDB[db1]>insertintodb1.t1values('batman',1),('superman',3),('leo',5);
QueryOK,3rowsaffected(0.12sec)
Records:3Duplicates:0Warnings:0
MariaDB[db1]>select*fromdb1.t1;
+----------+---+
|a|b|
+----------+---+
|batman|1|
|superman|3|
|leo|5|
+----------+---+
3rowsinset(0.08sec)
|
接下來開啟兩個事務T1和T2，T1中查看b=3的行，顯式加排他鎖；T1未提交事務時，T2事務開啟并嘗試插入新行a='batman',b=2和a='batman',b=4；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
MariaDB[db1]>begin;
QueryOK,0rowsaffected(0.00sec)
MariaDB[db1]>select*fromdb1.t1whereb=3forupdate;
+----------+---+
|a|b|
+----------+---+
|superman|3|
+----------+---+
1rowinset(0.14sec)
MariaDB[db1]>begin;
QueryOK,0rowsaffected(0.00sec)
MariaDB[db1]>insertintodb1.t1values('batman',2);
QueryOK,1rowaffected(0.00sec)
MariaDB[db1]>insertintodb1.t1values('batman',4);
QueryOK,1rowaffected(0.00sec)
|
繼續在T2事務中嘗試查看b=3的行，顯式加共享鎖。
1
2
3
|
MariaDB[db1]>select*fromdb1.t1whereb=3lockinsharemode;
ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
|
發現T2事務中可以插入新行a='batman',b=2和a='batman',b=4；但是不能查看b=3的行，接下來我們查看當前innodb鎖的信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
MariaDB[db1]>select*frominformation_schema.innodb_locksG;
***************************1.row***************************
lock_id:1122:0:337:3
lock_trx_id:1122
lock_mode:S
lock_type:RECORD
lock_table:`db1`.`t1`
lock_index:`PRIMARY`
lock_space:0
lock_page:337
lock_rec:3
lock_data:3
***************************2.row***************************
lock_id:1121:0:337:3
lock_trx_id:1121
lock_mode:X
lock_type:RECORD
lock_table:`db1`.`t1`
lock_index:`PRIMARY`
lock_space:0
lock_page:337
lock_rec:3
lock_data:3
2rowsinset(0.02sec)
ERROR:Noqueryspecified
MariaDB[db1]>select*frominformation_schema.innodb_lock_waitsG;
***************************1.row***************************
requesting_trx_id:1122
requested_lock_id:1122:0:337:3
blocking_trx_id:1121
blocking_lock_id:1121:0:337:3
1rowinset(0.00sec)
ERROR:Noqueryspecified
|
從以上信息可以看到，T1事務當前只在b=3所在的行上加了寫鎖，排他鎖，并沒有同時使用gap鎖來組成next-key lock。
到此，已經證明了，當查詢的索引含有唯一屬性時，將next-key lock降級為record key
我們第二次創建的t1表的列b是主鍵，而主鍵必須是唯一的。
實踐2： 關閉GAP鎖_RC
有兩種方式顯式關閉gap鎖：（除了外鍵約束和唯一性檢查外，其余情況僅使用record lock）
A. 將事務隔離級別設置為RC B. 將參數innodb_locks_unsafe_for_binlog設置為1
| T1 RR |
T2 RR |
| begin; |
begin; |
| select * from db1.t1 where b=3 for update; |
|
|
insert into db1.t1 values ('batman',2) |
|
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
|
set session transaction isolation level READ COMMITTED; |
| commit; |
commit; |
注意，將T1事務設置為RC后，需要將二進制日志的格式改為row格式，否則執行顯式加鎖時會報錯
1
2
|
MariaDB[db1]>insertintot1values('batman',2);
ERROR1665(HY000):Cannotexecutestatement:impossibletowritetobinarylogsinceBINLOG_FORMAT=STATEMENTandatleastonetableusesastorageenginelimitedtorow-basedlogging.InnoDBislimitedtorow-loggingwhentransactionisolationlevelisREADCOMMITTEDorREADUNCOMMITTED.
|
| T1 RC |
T2 RR |
| begin; |
begin; |
| set session transaction isolation level READ COMMITTED; |
|
| select * from db1.t1 where b=3 for update; |
|
|
insert into db1.t1 values ('batman',2) |
|
insert into db1.t1 values ('batman',4) |
| commit; |
commit; |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
MariaDB[db1]>setsessiontransactionisolationlevelREADCOMMITTED;
QueryOK,0rowsaffected(0.00sec)
MariaDB[db1]>select@@tx_isolation;
+----------------+
|@@tx_isolation|
+----------------+
|READ-COMMITTED|
+----------------+
1rowinset(0.00sec)
MariaDB[db1]>begin;
QueryOK,0rowsaffected(0.09sec)
MariaDB[db1]>select*fromt1whereb=3forupdate;
+----------+------+
|a|b|
+----------+------+
|superman|3|
+----------+------+
1rowinset(0.00sec)
MariaDB[db1]>begin;
QueryOK,0rowsaffected(0.16sec)
MariaDB[db1]>select@@tx_isolation;
+----------------+
|@@tx_isolation|
+----------------+
|READ-COMMITTED|
+----------------+
1rowinset(0.00sec)
MariaDB[db1]>insertintodb1.t1values('batman',2);
QueryOK,1rowaffected(0.00sec)
MariaDB[db1]>commit;
QueryOK,0rowsaffected(0.01sec)
MariaDB[db1]>setsessiontransactionisolationlevelREPEATABLEREAD;
QueryOK,0rowsaffected(0.00sec)
MariaDB[db1]>select@@tx_isolation;
+-----------------+
|@@tx_isolation|
+-----------------+
|REPEATABLE-READ|
+-----------------+
1rowinset(0.00sec)
MariaDB[db1]>begin;
QueryOK,0rowsaffected(0.00sec)
MariaDB[db1]>insertintodb1.t1values('batman',4);
QueryOK,1rowaffected(0.00sec)
MariaDB[db1]>commit;
QueryOK,0rowsaffected(0.00sec)
MariaDB[db1]>commit;
QueryOK,0rowsaffected(0.00sec)
|
我在做測試的時候，T1事務隔離界別為RC，T2事務的隔離界別分別用RC和RR做了測試，都是可以的
實踐3： 關閉GAP鎖_innodb_locks_unsafe_for_binlog
查看當前innodb_locks_unsafe_for_binlog參數的值
1
2
3
4
5
6
7
|
MariaDB[(none)]>select@@innodb_locks_unsafe_for_binlog;
+----------------------------------+
|@@innodb_locks_unsafe_for_binlog|
+----------------------------------+
|0|
+----------------------------------+
1rowinset(0.00sec)
|
修改參數，并重新啟動服務
1
2
3
4
5
6
7
8
9
10
|
[root@localhost~]
innodb_locks_unsafe_for_binlog=1
[root@localhost~]
[root@localhost~]
+----------------------------------+
|@@innodb_locks_unsafe_for_binlog|
+----------------------------------+
|1|
+----------------------------------+
|
還是去創建db1.t1表，如果已有就先drop；有列a和b，分別為char(10)和int型，并且b為key，注意b列為索引列，但并不是主鍵，因此不是唯一的。
| T1 RR |
T2 RR |
| begin; |
begin; |
| select * from db1.t1 where b=3 for update; |
|
|
insert into db1.t1 values ('batman',2) |
|
insert into db1.t1 values ('batman',4) |
| commit; |
commit; |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
MariaDB[db1]>createtabledb1.t1(achar(10),bint,key(b));
QueryOK,0rowsaffected(0.03sec)
MariaDB[db1]>insertintodb1.t1values('batman',1),('superman',3),('leo',5);
QueryOK,3rowsaffected(0.15sec)
Records:3Duplicates:0Warnings:0
MariaDB[db1]>select*fromdb1.t1;
+----------+------+
|a|b|
+----------+------+
|batman|1|
|superman|3|
|leo|5|
+----------+------+
3rowsinset(0.02sec)
|
接下來開啟兩個事務T1和T2，T1中查看b=3的行，顯式加排他鎖；T1未提交事務時，T2事務開啟并嘗試插入新行a='batman',b=2和a='batman',b=4；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
MariaDB[(none)]>begin;
QueryOK,0rowsaffected(0.00sec)
MariaDB[(none)]>select*fromdb1.t1whereb=3forupdate;
+----------+------+
|a|b|
+----------+------+
|superman|3|
+----------+------+
1rowinset(0.01sec)
MariaDB[(none)]>begin;
QueryOK,0rowsaffected(0.00sec)
MariaDB[(none)]>insertintodb1.t1values('batman',4);
QueryOK,1rowaffected(0.01sec)
MariaDB[(none)]>insertintodb1.t1values('batman',2);
QueryOK,1rowaffected(0.00sec)
MariaDB[(none)]>commit;
QueryOK,0rowsaffected(0.00sec)
MariaDB[(none)]>commit;
QueryOK,0rowsaffected(0.00sec)
|
不轉行的女工程師https://booboowei.github.io/
總結
以上是生活随笔為你收集整理的Next-key locking是如何解决幻读（当前读）问题的的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
