事物隔离级别
事务隔离级别,主要保障关系数据库ACID特性的I(Isolation),既针对存在冲突的并发事务,提供一定程度的安全保证。ANSI(American National Standards Institute) SQL 92标准( http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt ) 首先定义了3种并发事务可能导致的不一致异象:Dirty read: SQL-transaction T1 modifies a row. SQL- transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed. Non-repeatable read: SQL-transaction T1 reads a row. SQL- transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted. Phantom: SQL-transaction T1 reads the set of rows N that satisfy some . SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same , it obtains a different collection of rows.嫌弃以上定义冗长,可以直接看以下形式化描述:
A1 Dirty Read:w1[x] ... r2[x] ... (a1 and c2 in any order) A2 Fuzzy Read: r1[x] ... w2[x] ... c2 ... r1[x] ... c1 A3 Phantom Read: r1[P] ... w2[y in P] ... c2 ... r1[P] ... c1其中w1[x]表示事务1写入记录x,r1表示事务1读取记录x,c1表示事务1提交,a1表示事务1回滚,r1[P]表示事务1按照谓词P的条件读取若干条记录,w1[y in P]表示事务1写入记录y满足谓词P的条件。 据此,ANSI定义了四种隔离级别,分别解决以上三种异常:
![da1b0cfa5e8dd463924b296376aa7db7.png](https://i-blog.csdnimg.cn/blog_migrate/b538e284e963de892c23c94197db6c80.png)
![a21b014528ea1f8e693785ea309a0c0c.png](https://i-blog.csdnimg.cn/blog_migrate/5208beedd1cb6873420b605a36c1f1bb.png)
P1 Dirty Read: w1[x]...r2[x]...(c1 or a1)不严谨之三 :三种异象仅针对S(ingle) V(alue)系统,不足以定义M(ulti)V(ersion)系统的隔离性。很多商业数据库所实现的SI,未违反P1、P2和P3,但又可能出现Constraint violation,不可串行化。除了P1/P2/P3,还可能出现哪些异常呢?
P2 Fuzzy Read: r1[x]...w2[x]...(c1 or a1)
P3 Phantom: r1[P]...w2[y in P]...(c1 or a1)
P4 Lost Update:r1[x]...w2[x]...w1[x]...c1 A5A Read Skew:r1[x]…w2[x]... w2[y]…c2…r1[y] …(c1 or a1) A5B Write Skew:r1[x]…r2[y]…w1[y]…w2[x]…(c1 and c2 occur) A5B2 Write Skew2:r1[P]... r2[P]…w1[y in P]…w2[x in P]...(c1 and c2 occur)针 对这四种情况,分别举一个例子:
r1[x=50] r2[x=50] w2[x=60] c2 w1[x=70] c1Lost Update: 事务1和事务2同时向同一个账户x分别充20和10块,事务1后提交,将70块写入数据库,事务2提交结果60块被覆盖。正确的情况下,事务1和2提交成功,账户里应该有80块。
(x+y=100) r1[x=50] w2[x=10] w2[y=90] c2 r1[y=90] c1Read Skew: x和y账户分别有50块钱,加起来共100块。事务1读x(50块)后,事务2将x账户的40块转到y账户,事务2提交后,事务1读y(90块)。在事务1看来,x+y=140,出现了不一致。
(x+y>=60) r1[x=50] r2[y=50] w1[y=10] c1 w2[x=10] c2Write Skew: x和y账户分别有50块钱,加起来共100块。假设存在某种约束,x和y账户的钱加起来不得少于60块。事务1和事务2在自认为不破坏约束的情况下(分别读了x账户和y账户),再分别从y账户和x账户取走40。但事实上,这两个事务完成后,x+y=20,约束条件被破坏。
(count(P)<=4):r1[count(P)=3],r2[count(P)=3],insert1[x in P],insert2[y in P],c1,c2,Write Skew2: 将Write Skew的条件改为范围。0 2
隔离级别实现
上一节介绍了ANSI定义的3种异象,及根据禁止异象的个数而定义的事务隔离级别。因为不存在严格、严谨的“官方”定义,各主流数据库隔离级别的表现也略有不同,一些现象甚至让用户感到困惑。 我认为相较于纠结隔离级别的准确定义,认识各数据库隔离级别的表现和实现,在生产环境中正确的使用它们才是更应该关注的事情。本节将以大篇幅具体的例子为切入点,介绍几种主流数据库隔离级别的表现,及内部对应的实现。2.1 Lock-based 隔离级别实现
在展示Lock-based隔离级别实现前,先介绍几个与锁相关的概念:Item Lock:对访问行加锁,可以防止dirty/fuzzy read。 Predicate Lock(gap lock):对search的范围加锁,全表扫描直接对整张表加锁,可防止phantom read。 Short duration:语句结束后释放锁。 Long duration:事务提交或回滚后释放锁。上述锁操作组合,便可实现不同级别的事务隔离标准,如下表所示。
![8b4c71de35336c302a8113399ef6666e.png](https://i-blog.csdnimg.cn/blog_migrate/a1977c6b77f485f9b30731ed97d25af6.png)
![0ddfda18c58fd89c210462295eb49850.png](https://i-blog.csdnimg.cn/blog_migrate/b9efbbf873278bc661274fbc52e0744b.png)
2.2 Oracle隔离级别的实现
Oracle仅支持两种隔离级别:Read Committed与Serializable。尽管官方这样描述,Oracle的Serializable实际是基于MVCC+Lock based的SI(Snapshot Isolation)隔离级别。 为实现快照读,内部维护了全局变量SCN(System Commit/Change Number),在事务提交时递增。读请求获取Snapshot便是获取当前最新的SCN。Oracle实现MVCC的方式是将block分为两类: (1)Current blocks为当前最新的页面,与持久化态数据保持一致。 (2)Consistent Read blocks,根据snapshot SCN生成相应的一致性版本页面。 以下两个具体的例子展示了:不同隔离级别下,读写语句在数据库内部发生了什么。![811a3bee56be4d6e7d52a0c83ef2489e.png](https://i-blog.csdnimg.cn/blog_migrate/ba40b911b5081b39fef22bd38ebbaf7a.png)
![84b62fed094a38cd834e87b19000dfe5.png](https://i-blog.csdnimg.cn/blog_migrate/2a0478151849fc7ffb8aceeda071b2ea.png)
![21c56c5e51eb7d0c208f78e6950f45e3.png](https://i-blog.csdnimg.cn/blog_migrate/894844a3556474e9c146dc57a775ce12.png)
2.3 MySQL(InnoDB)隔离级别实现
InnoDB同样以MVCC+Lock的方式实现隔离级别。其中普通select语句均是snapshot read。而delete/update/select for update等语句是加锁实现的current read,如下表所示(注:该表为Pecona 5.6版本的代码实现)。![4f0715c4fdbc9af675854ea070a7c8ad.png](https://i-blog.csdnimg.cn/blog_migrate/07a430f12980481c324ef8492b3cb309.png)
![fcce6953d9c27487bd7796872a94ff14.png](https://i-blog.csdnimg.cn/blog_migrate/70756422f8dd1f4318ef5da475e46747.png)
![636ee46630b95326a2f63a21741c138a.png](https://i-blog.csdnimg.cn/blog_migrate/d8fa0b94bc2c052393fbb6b911dec32d.png)
![5ea2a7406559b01afaaaa700c71aa7c5.png](https://i-blog.csdnimg.cn/blog_migrate/1aff9d5674322194560c8c1d4c4ec8cb.png)
![554dfe47234c4968a90cb7222b8ba4cc.png](https://i-blog.csdnimg.cn/blog_migrate/deb07c3cd859cd8a7b2c2a4062421486.png)
![39ee15a8e086b06774f6a332506e6092.png](https://i-blog.csdnimg.cn/blog_migrate/613419dfe391fa59d14420c220d729b9.png)
总结
![1cae02a8ac909b6415aa7e4fb4d05928.png](https://i-blog.csdnimg.cn/blog_migrate/17e35743641eab809fef00c6f6a16ffb.png)
![71255f69a631adca647f4939a50cde51.gif](https://i-blog.csdnimg.cn/blog_migrate/1bcc53027afda52bd403badcea471f25.gif)
![3e6af9d0998223a1230252892a701c19.gif](https://i-blog.csdnimg.cn/blog_migrate/f6bc519bd8ebd8869902956c592053f8.gif)