大約一年前發現MySQL在可重複讀隔離級別下,可能出現全表的排他鎖.
當時並沒有找到一個合理的解釋.
http://blog.itpub.net/29254281/viewspace-1081634/

實驗資料和環境

CREATE TABLE t (
a int(11) NOT NULL,
b int(11) DEFAULT NULL,
PRIMARY KEY (a),
KEY b (b)
) ENGINE=InnoDB;

INSERT INTO t VALUES
(10,2),
(20,2),
(30,4),
(40,4),
(50,6),
(60,6);

環境如下:

根據前文的實驗,可以看到MySQL加鎖和
隔離級別,是否主鍵索引,是否唯一索引,是否無索引相關
http://blog.itpub.net/29254281/viewspace-1398273/

除了上面的條件,加鎖和執行計劃還是有關係的。

比如下面的語句會導致所有的主鍵索引,二級索引和間隙上鎖.
select * from t where b<=2 for update;
(3 lock struct(s), heap size 376, 13 row lock(s))
是因為他的執行計劃

他採用了全掃描二級索引,所以對全部的二級索引以及對應的主鍵索引上鎖,並且鎖定了所有的間隙.可是非常可怕的。

------------------------------------------------------------------------------------------------
但是如果是下面的SQL語句,就不會有鎖定全部索引和間隙的問題.
select * from t where b<=4 for update;

開啟另外一個終端輸入Insert語句,沒有受到任何影響.
mysql> insert into t select 70,6;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

這兩個SQL語句在同樣的環境下(同樣的隔離級別,主鍵索引和二級索引),僅僅因為查詢的值不一樣,卻造成了不同的加鎖情況,主要是因為執行計劃不同,
第二個SQL的執行計劃是索引範圍掃描,所以不會鎖定全部的二級索引和關聯的主鍵索引.

MySQL選用可重複讀之前一定要想到的事情(執行計劃影響)

相關文章