MySQL選用可重複讀隔離級別之前一定要想到的事情.
間隙鎖

MySQL在使用之前有三個務必要知道..(隨著瞭解的深入這個極有可能再增加..)
1.DDL會引起metadata lock,導致請求連環阻塞,甚至是查詢請求.
http://blog.itpub.net/29254281/viewspace-1383193/

2.MySQLDump和XtraBackup的flush table命令會引起waiting for table連環阻塞,同樣也會阻塞查詢請求.
http://blog.itpub.net/29254281/viewspace-1392757/

3.選用可重複讀事務隔離級別,會開啟間隙鎖.他鎖定的內容比實際需要的要多,並且很可能導致死鎖.

本文是何登成大神文章的讀後感和總結.
連結如下:

(關於這方面最好的文章,沒有之一)

Oracle和MySQL(讀提交和可重複讀)都實現了MVCC多版本併發控制.
這意味著普通的Select（一致性讀或者說快照讀）可以以非鎖定的形式讀取資料.

而當前讀(oracle的db block gets)都需要加鎖.
比如:
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

除了第一個SQL對記錄上共享鎖,其餘都是排他鎖.

MySQL雖然在可重複讀事務隔離級別實現了避免幻讀.
但是可重複讀隔離級別的當前讀,會啟動間隙鎖.

當前讀(鎖定讀)加鎖情況.

	讀提交	可重複讀
主鍵索引	鎖定主鍵索引	鎖定主鍵索引
唯一索引	鎖定唯一索引的值和主鍵索引的值	鎖定唯一索引的值和主鍵索引的值
普通索引	鎖定普通索引的值和主鍵索引的值	鎖定普通索引的值和主鍵索引的值,普通索引增加間隙鎖
無索引	鎖定所有的主鍵索引,在伺服器層對不符合條件的記錄解鎖	鎖定所有的主鍵索引和主鍵索引的間隙

根據何老師的例子（另記一份,現在的網站也是說沒就沒）
檢視同樣的SQL
(delete from t1 where id = 10;)
在不同的情況下,鎖定的情況.

首先是讀提交事務隔離級別,
如果id是主鍵索引,鎖定主鍵索引的鍵值

如果id是唯一索引,鎖定唯一索引和主鍵索引的相關鍵值.

如果id是普通索引,則鎖定普通索引和主鍵索引的相關鍵值.

最後,如果id沒有索引,在InnoDB層,會對所有主鍵索引上排他鎖,到MySQL伺服器層對不符合條件的記錄進行解鎖.

而在可重複讀事務隔離級別,
如果id是主鍵索引,和讀提交一樣,會鎖定主鍵索引的相關鍵值.

如果id是唯一索引,MySQL會將間隙鎖退化為行級鎖,僅僅鎖定唯一索引和主鍵索引的相關鍵值.同讀提交隔離級別一樣.
例外:
如果是組合的唯一索引 create unique index inx_1 on test(a,b,c);
但是查詢只用到了a,b select * from test where a=? and b=? for update
MySQL這時不會退化為行級鎖,他的處理方式會等同於下面說到的普通索引.（行級鎖+間隙鎖）

如果id是普通索引,MySQL會鎖定普通索引和主鍵索引的相關鍵值，並且鎖定相關普通索引之間的間隙.

以上圖為例,select * from t1 where id=100 for update;
這個SQL沒有查到任何的記錄,但是他同樣會上間隙鎖.
（在這種情況下,即使id是主鍵索引或者唯一索引,也會產生間隙鎖）

在可重複讀的隔離級別下,如果id沒有索引
他會鎖定主鍵索引的所有記錄和所有間隙.
和讀提交不一樣,在MySQL伺服器層,並不會對不符合條件的記錄解鎖.並且它會鎖定主鍵索引的所有間隙.

參考何老師文章中的一個例子

在可重複讀隔離級別下,
Index key：pubtime > 1 and puptime < 20。此條件，用於確定SQL在idx_t1_pu索引上的查詢範圍。
Index Filter：userid = ‘hdc’ 。此條件，可以在idx_t1_pu索引上進行過濾，但不屬於Index Key。
Table Filter：comment is not NULL。此條件，在idx_t1_pu索引上無法過濾，只能在聚簇索引上過濾。

他的加鎖情況如下

從圖中可以看出，在Repeatable Read隔離級別下，由Index Key所確定的範圍，被加上了GAP鎖；Index Filter鎖給定的條件 (userid = ‘hdc’)何時過濾，視MySQL的版本而定，在MySQL 5.6版本之前，不支援Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server層過濾，在5.6後支援了Index Condition Pushdown，則在index上過濾。若不支援ICP，不滿足Index Filter的記錄，也需要加上記錄X鎖，若支援ICP，則不滿足Index Filter的記錄，無需加記錄X鎖 (圖中，用紅色箭頭標出的X鎖，是否要加，視是否支援ICP而定)；而Table Filter對應的過濾條件，則在聚簇索引中讀取後，在MySQL Server層面過濾，因此聚簇索引上也需要X鎖。最後，選取出了一條滿足條件的記錄[8,hdc,d,5,good]，但是加鎖的數量，要遠遠大於滿足條件的記錄數量。

結論：在Repeatable Read隔離級別下，針對一個複雜的SQL，首先需要提取其where條件。Index Key確定的範圍，需要加上GAP鎖；Index Filter過濾條件，視MySQL版本是否支援ICP，若支援ICP，則不滿足Index Filter的記錄，不加X鎖，否則需要X鎖；Table Filter過濾條件，無論是否滿足，都需要加X鎖。

以上是對何老師文章的總結.

這個文章看了很長時間,但是有一個問題一直很疑惑.
實驗環境

實驗資料
create table t
(
  a int primary key,
  b int,
  c int,
  key (b,c)
) engine=innodb;

insert into t
values
(1,10,10),
(3,10,20),
(5,20,30),
(7,20,40),
(9,20,50);
commit;

終端一:
select * from t where b=10 and c=10 for update;
理論上，他會鎖定 (b=10 c=10)的輔助索引,(a=1)的主鍵索引並且會鎖定（10,負無窮)至(10,10),(10,10)至(10,20)的範圍,也就是間隙鎖.

但是終端二:
select * from t where b=10 and c=15 for update;
查詢並沒有阻塞.這不對啊.因為在這個隔離級別下,即使沒有資料,也會產生間隙鎖.

終端三:
回滾終端二,在終端三輸入
insert into t values(50,10,15);
我發現這個SQL被阻塞了,也就是說明終端一的間隙鎖是存在的.

這個問題查閱了官方文件,
http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html
但其實沒有看懂..
在缺乏理論依據的基礎上，
我猜測雖然當前讀的行鎖是排他的(除了那個lock in share mode).
但是間隙鎖有共享鎖的性質，一旦出現間隙鎖,在這個間隙的範圍內不能新出現任何記錄
無論是update修改過來的，還是insert新增過來的
只要間隙內不出現新的記錄，間隙鎖之間就可以共存.

參考:
開啟鎖監控,可以在show engine innodb status\G看到更多鎖資訊,並且定時將資訊寫入錯誤日誌.
create table innodb_lock_monitor(a int) engine=innodb;

檢視ICP是否開啟
select @@optimizer_switch\G

MySQL選用可重複讀之前一定要想到的事情

相關文章