前置知识
主键索引
等值条件,命中,加记录锁
等值条件,未命中,加间隙锁
范围条件,命中,包含where条件的临键区间,加临键锁
范围条件,没有命中,加间隙锁
辅助索引
等值条件,命中,命中记录的辅助索引项 + 主键索引项加记录锁,辅助索引项两侧加间隙锁
等值条件,未命中,加间隙锁
范围条件,命中,包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁
范围条件,没有命中,加间隙锁
简单案例
-- SQL1:
select * from t1 where id = 10;
-- SQL2:
delete from t1 where id = 10;
这两条语句分别加了什么锁呢?
这里不能笼统的回答需要分场景来考虑比如有下面这些情况:
- id是否为主键
- 当前系统的隔离级别是什么?
- 如果id不为主键,id是否为索引?
- 如果id有索引是否为唯一值索引?
- 需要查看SQL的具体执行计划来确定是索引还是全表扫描
RU
SQL1和SQL2都不加锁,完全不管冲突,所以在这种隔离级别下没有太多关于锁情况的分析。
RC
在RC隔离级别下SQL1 id是否有索引读取都是不加锁的,因为通过MVCC进行了控制,从而实现了读不加锁。
SQL2
- 如果ID是主键索引,这是时候加写锁(记录锁);如果这个时候没有匹配到数据加间隙锁。
- ID不是主键,但是唯一性的辅助索引,这个时候会将辅助索引的这个记录锁住,并且回表锁住聚簇索引的主键。为什么要回表加锁呢?举个例子,比如你正在使用辅助索引,这个时候如果聚簇没有记录锁,那你把ID更新了,下一个时刻如果辅助索引需要回表,但是这条记录确不存在了。
- ID不为主键,同时也是非唯一索引。那么辅助索引里面所有id为10的记录均要加锁。同时也要回表,将聚簇索引的记录锁住。这个时候其实还是可以插入数据的(产生幻读的问题),因为主键插入没有加锁,是新的ID,同事辅助索引也是锁的记录,所以可以插入。
- ID无索引那么这个时候会将所有聚簇索引的记录都加上锁。但是这个时候是可以新增数据的,也存在幻读的问题。 因为没有索引所以只能走全表扫描进行过滤(Mysql会有优化,不满足条件的锁会提前释放)。
RR
同样在RR隔离级别下SQL1 id是否有索引读取都是不加锁的,因为通过MVCC进行了控制,从而实现了读不加锁。
- 如果ID是主键索引,这是时候加写锁(记录锁);如果这个时候没有匹配到数据加间隙锁。
- ID不是主键,但是唯一性的辅助索引,这个时候会将辅助索引的这个记录锁住,并且回表锁住聚簇索引的主键。为什么要回表加锁呢?举个例子,比如你正在使用辅助索引,这个时候如果聚簇没有记录锁,那你把ID更新了,下一个时刻如果辅助索引需要回表,但是这条记录确不存在了。
- ID不是主键,是非唯一索引。在RR隔离级别下,先通过辅助索引ID定位到满足条件的索引项加上记录锁,然后录在索引项的GAP上加上间隙锁,同时在聚簇索引上的记录上加记录锁。
- 如果没有索引,会全表扫描读当前读,然后锁上表中的所有记录,同时会锁上聚簇索引的所有间隙,防止幻读。
- 杜绝所有并发update/delete/insert操作(Mysql会有优化,不满足条件的锁会提前释放)。
串行化
在RC,RR隔离级别下,都是快照读,不加锁。但是在Serializable隔离级别,SQL1会加读锁,也就是说快照读不复存在,MVCC并发控制降级为LBCC。
复杂SQL的分析
delete from t1 where pubtime > 1 and pubtime < 20 and userid='hero' and commit is not null;
先确定使用了什么索引,然后看是否用了ICP如果用了就有Index Filter 然后就是普通条需要再服务层来过滤
可以分为下面三个部分:
Index key(使用了什么索引):pubtime > 1 and puptime < 20。此条件,用于确定SQL在idx_t1_pu索引上的查询范围。
Index Filter(是否在索引上进行了进一步过滤):userid = ‘hero’ 。此条件,可以在idx_t1_pu索引上进行过滤,但不属于IndexKey。
Table Filter(表筛选):comment is not NULL。此条件,在idx_t1_pu索引上无法过滤,只能在SQL-Layer上过滤。
在RR级别下:
由Index Key所确定的范围,被加上了间隙锁;Index Filter锁给定的条件视MySQL的版本而定
不支持ICP,因此Index Filter在MySQL Server层过滤,不满足Index Filter的记录,也需要加上记录写锁;
支持了ICP,则在index上过滤,则不满足Index Filter的记录,无需加记录写锁;
而Table Filter对应的过滤条件,则在聚簇索引中读取后,在MySQL Server层面过滤,因此聚簇索引上也需要写锁。