MySQL选用可重复读之前一定要想到的事情(执行计划影响)

浏览数：44 / 时间：2015年06月12日

大约一年前发现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的执行计划是索引范围扫描,所以不会锁定全部的二级索引和关联的主键索引.