Mysql事务隔离级别加锁机制 - RC\RR\Serializable 学习总结

Yhzhtk commented 8 years ago

业务处理高并发时经常会遇到死锁问题，要想了解并解决死锁问题，首先得明白 Mysql 不同隔离级别的加锁原理。

在阅读 [MySQL 加锁处理分析] http://hedengcheng.com/?p=771 后有很大收获，摘取主要内容，总结记录一下。

快照读

简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

select * from table where ?;

当前读

特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

隔离级别（MySQL/InnoDB）

Read Uncommited 可以读取未提交记录。此隔离级别，不会使用，忽略。

Read Committed (RC) 快照读忽略，本文不考虑。针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。

Repeatable Read (RR) 快照读忽略，本文不考虑。针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。

Serializable 从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。 Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

加锁机制

下面两条简单的SQL，他们加什么锁？

SQL1：select * from t1 where id = 10; SQL2：delete from t1 where id = 10;

针对这个问题，不同的隔离级别和索引情况不一样。下面直接给出各种情况的结论：

组合一：id列是主键，RC隔离级别

id是主键时，此SQL只需要在id=10这条记录上加X锁即可。

组合二：id列是二级唯一索引，RC隔离级别

若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的记录，另一把锁对应于聚簇索引（主键索引）上的对应的记录。

组合三：id列是二级非唯一索引，RC隔离级别

若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

组合四：id列上没有索引，RC隔离级别

若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

组合五：id列是主键，RR隔离级别

与_组合一_一致

组合六：id列是二级唯一索引，RR隔离级别

与_组合二_一致

组合七：id列是二级非唯一索引，RR隔离级别

Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

组合八：id列上没有索引，RR隔离级别

在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发更新/删除/插入操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

组合九：Serializable隔离级别

所有的写操作与_组合八_一致，但是所有的读操作，都是当前读。

总结

针对于InnoDB引擎（支持 MVCC和事务）

加锁机制与事务的隔离级别有关
如果有索引，锁可以加到索引限制的范围内
如果不是主键索引，除了加到二级索引上，还会对相应的主键加锁
如果是唯一索引，则只加一条锁，且"一般"不会有 GAP 锁
如果不是唯一索引，对于RR隔离级别，还会对其间（含两边）的空隙加上 GAP 锁
如果是Serializable级别，所有的读都是当前读，且读写冲突

haohangan commented 7 years ago

路过

zhangzui commented 5 years ago

这里容易误导新手，写清楚了

mcace commented 4 years ago

何大师原文是：

注：在前面八种组合下，也就是RC，RR隔离级别下，SQL1：select操作均不加锁，采用的是快照读，因此在下面的讨论中就忽略了，主要讨论SQL2：delete操作的加锁。

这里要写清楚

Yhzhtk / note

Mysql事务隔离级别加锁机制 - RC\RR\Serializable 学习总结 #38

快照读