思考一个问题:下面一条简单的SQL,它加什么锁?
- delete from t1 where id = 10;
带着你的答案,我们继续往下看。
如果要分析上述SQL的加锁情况,必须了解这个SQL的执行前提,MySQL的隔离级别是什么?id列是不是主键?id列有没有索引?前提不同加锁处理的方式也不同。
可能的情况:
- id列是不是主键?
- MySQL的隔离级别是什么?
- id列如果不是主键,那么id列上有索引吗?
- id列上如果有二级索引,那么这个索引是唯一索引吗?
- SQL的执行计划是什么?索引扫描?全表扫描?
根据上述情况,有以下几种组合:
- id列是主键,RC隔离级别
- id列是二级唯一索引,RC隔离级别
- id列是二级非唯一索引,RC隔离级别
- id列上没有索引,RC隔离级别
- id列是主键,RR隔离级别
- id列是二级唯一索引,RR隔离级别
- id列是二级非唯一索引,RR隔离级别
- id列上没有索引,RR隔离级别
问题看起来变得复杂了,事实上,要分析加锁,就得考虑这么多情况,不过只要你选定了一种组合,SQL需要加哪些锁,其实也就确定了。
聊一聊几个比较经典的组合:
组合一:id主键+RC
大部分系统都是Read Committed隔离级别,id列是主键,这种情况只需要将主键上,id = 10的记录加上X锁即可。见下图:
结论:id是主键时,此SQL只需要在id=10这条记录上加X锁即可。
组合二:id唯一索引+RC
id是一个Unique的二级索引键值。那么在RC隔离级别下,delete from t1 where id = 10; 需要加什么锁呢?
见下图:
name列是主键,id列是唯一索引。此时,加锁的情况由于组合一有所不同。由于id是唯一索引,因此delete语句会选择走id列的索引进行where条件的过滤,在找到id=10的记录后,首先会将唯一索引上的id=10索引记录加上X锁,同时,会根据读取到的name列,回主键索引,然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。
结论:若id列上有唯一索引。那么SQL需要加两个X锁,一个对应于id唯一索引上的id = 10的记录,另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录
组合三:id非唯一索引+RC
id列只有一个普通的索引,那么此时会持有哪些锁?
见下图:
首先,id列索引上,满足id = 10查询条件的记录,均已加锁。同时,这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于,组合二最多只有一个满足等值查询的记录,而组合三会将所有满足查询条件的记录都加锁。
结论:若id列上有非唯一索引,那么对应的所有满足SQL查询条件的记录,都会被加锁。同时,这些记录在主键索引上的记录,也会被加锁。
组合四:id无索引+RC
id列上没有索引,where id = 10;这个过滤条件,没法通过索引进行过滤,那么只能走全表扫描做过滤。对应于这个组合,SQL会加什么锁?
见下图:
由于id列上没有索引,因此只能走聚簇索引,进行全部扫描。从图中可以看到,满足删除条件的记录有两条,但是,聚簇索引上所有的记录,都被加上了X锁。无论记录是否满足条件,全部被加上X锁。既不是加表锁,也不是在满足条件的记录上加行锁。有人可能会问?为什么不是只在满足条件的记录上加锁呢?这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由MySQL Server层进行过滤。因此也就把所有的记录,都锁上了。
结论:若id列上没有索引,SQL会走聚簇索引的全扫描进行过滤,由于过滤是由MySQL Server层面进行的。因此每条记录,无论是否满足条件,都会被加上X锁。但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省略。同时,优化也违背了2PL的约束。上
面的四个组合,都是在Read Committed隔离级别下的加锁行为,接下来的四个组合,是在Repeatable Read隔离级别下的加锁行为。
组合五:id主键+RR
id列是主键列,Repeatable Read隔离级别,针对delete from t1 where id = 10; 这条SQL,加锁与组合一:[id主键,Read Committed]一致。
组合六:id唯一索引+RR
组合六的加锁,与组合二:[id唯一索引,Read Committed]一致。两个X锁,id唯一索引满足条件的记录上一个,对应的聚簇索引上的记录一个。
组合七:id非唯一索引+RR
在RR隔离级别下,为了防止幻读的发生,会使用Gap锁。这里,你可以把Gap锁理解为,不允许在数据记录前面插入数据。对应于这个组合,SQL会加什么锁?
见下图:
结论:Repeatable Read隔离级别下,id列上有一个非唯一索引,对应SQL:delete from t1 where id = 10; 首先,通过id索引定位到第一条满足查询条件的记录,加记录上的X锁,加GAP上的GAP锁,然后加主键聚簇索引上的记录X锁,然后返回;然后读取下一条,重复进行。直至进行到第一条不满足条件的记录[11,f],此时,不需要加记录X锁,但是仍旧需要加GAP锁,最后返回结束。什么时候会取得gap lock或nextkey lock 这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。
组合八:id无索引+RR
Repeatable Read隔离级别下的最后一种情况,id列上没有索引。此时SQL:delete from t1 where id = 10; 没有其他的路径可以选择,只能进行全表扫描。最终的加锁情况,见下图:
结论:在Repeatable Read隔离级别下,如果进行全表扫描的当前读,那么会锁上表中的所有记录,同时会锁上聚簇索引内的所有GAP,杜绝所有的并发 更新/删除/插入 操作。当然,也可以通过触发semi-consistent read,来缓解加锁开销与并发影响,但是semi-consistent read本身也会带来其他问题,不建议使用。
总结
本文对MySQL一些基础性的知识点进行了详细的总结,参考了网上和书上比较多的资料和实例。希望能对各位的学习有所帮助。
更多精彩内容,关注我们▼▼