MySQL选用可重复读之前一定要想到的事情(ICP验证和勘误)

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

前文关于ICP优化的实验貌似有误
http://blog.itpub.net/29254281/viewspace-1398273/

实验环境

select @@optimizer_switch\G
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,matery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

实验数据
CREATE TABLE t1 (
id int(11) NOT NULL,
userid varchar(10) DEFAULT NULL,
blogid varchar(10) DEFAULT NULL,
pubtime int(11) DEFAULT NULL,
comment varchar(10) DEFAULT NULL,
PRIMARY KEY (id),
KEY inx_t1_pu (pubtime,userid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO t1 VALUES
(1,‘hdc‘,‘a‘,10,NULL),
(4,‘yyy‘,‘b‘,3,NULL),
(6,‘hdc‘,‘c‘,100,NULL),
(8,‘hdc‘,‘d‘,5,‘good‘),
(10,‘hdc‘,‘e‘,1,NULL),
(100,‘bbb‘,‘f‘,20,NULL);
commit;

下图标识的情况实际上有错误.

按照前文的说法,启用ICP二级索引pubtime=3,userid=yyy 和主键索引id=4的节点不会被锁.

首先确定这个SQL会应用ICP

可以看到由Delete改写的SQL应用了ICP优化

执行文中的SQL
delete from t1 where pubtime>1 and pubtime<20 and userid=‘hdc‘ and comment is not null;

然后show engine innodb status\G

解析上锁节点
辅助索引
select conv(‘000000a‘,16,10); 10
select conv(‘0000003‘,16,10); 3
select conv(‘0000005‘,16,10); 5
select conv(‘0000014‘,16,10); 20

主键索引
select conv(‘0000001‘,16,10); 1
select conv(‘0000004‘,16,10); 4
select conv(‘0000008‘,16,10); 8
select conv(‘0000064‘,16,10); 100

实际的情况应该如下图所示

也就是说,被ICP过滤的数据也会被上锁.
并且根据MySQL Next-Key Locking,会对间隙锁右侧的数据上锁,所以辅助索引pubtime=20,userid=bbb的节点和主键索引id=100的节点也会被上锁.

Next Key Locking参考
http://blog.itpub.net/29254281/viewspace-1090160/

纠正错误如下
1.ICP不会释放不符合条件记录的锁
按文中的例子,
辅助索引Index key(pubtime > 1 and puptime < 20)选中的节点 3,5,10 会被上锁.
并且3,5,10产生的4个间隙会被上锁.
根据Next Key Locking,右侧间隙的值会被上锁.(10,20],也就是说辅助索引pubtime=20,userid=bbb的节点会被上锁.
最后辅助索引对应的主键索引都会被上锁(id 1,4,8,100)

2.前文图中间隙锁的右侧边界节点应该被上锁,但是图中没有标识出来.

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

MySQL选用可重复读之前一定要想到的事情(ICP验证和勘误)