Tech News/Blog Notebook [22.2.6]
数据库ACID
- atomic 原子性
- consistency 一致性
- isolation 隔离性
- durability 持久性
Isolation 持久性:数据库在并发事务下的表现。
参考:《A Critique of ANSI SQL Isolation Levels》,简要版本
事务并发时,会出现的8类问题:
P0:Dirty Write
事务T1改写了事务T2写入但还未提交的值。
A Dirty Write occurs when one transaction overwrites a value that has previously been written by another still in-flight transaction.
会造成的两个影响:
- violates the integrity constraint
- the system can’t automatically rollback to a before image on transaction abort.
解法:long-duration write lock
P1:Dirty Read
事务T1读取到了事务T2已写入但还未commit的值。
A Dirty Read occurs when one transaction reads a value that has been written by another still in-flight transaction.
解法:short-duration read locks + long-duration write locks
P2:Non-Repeatable Read(Fussy Read)
在一个事务中已经被读取的值,被另一个事务给改写了。
A Fuzzy or Non-Repeatable Read occurs when a value that has been read by a still in-flight transaction is overwritten by another transaction.
解法:long-duration read and write locks,or 基于谓词的short-duration lock读取(Select... where P)
P3:Phantom(幻读)
当事务T1使用Select... Where P读取数据时,事务T2同时在执行且刚好将值更新为P的状态,导致T1以为自己读到的是正确条件下的值,但其实不是。
A Phantom occurs when a transaction does a predicate-based read (e.g. SELECT… WHERE P) and another transaction writes a data item matched by that predicate while the first transaction is still in flight.
解法:long-duration read and write locks
P4:Lost Update
一图胜千言,T2事务的更新丢失了(因为被T1事务给覆盖了)
P4C:Cursor Lost Update
Cursor Lost Update是Lost Update的一个变体,效果是一样的。
解法:holding a lock on the current item of the cursor until the cursor moves or is closed.
A5A:Read Skew(读偏序)
同一事务的两个数据读取之间,被其他事务更改了部分数据的值。
A5B:Write Skew(写偏序)
事务中更新某个值的谓语,在用于校验后被其他事务修改了。
六种隔离级别(Isolation Level):
- Read Uncommitted(Degree 1)
- Read Committed(Degree 2)
- Cursor Stability
- Repeatable Read
- Snapshot
- ANSI SQL(Serializble 可串行化,Degree 3)
Snapshot Isolation的核心思想是事务的读操作从已提交的数据中读取,此时数据的版本时间戳为StartTimestamp,然后进行一些列事务内部的操作,提交时生成一个CommitTimestamp。当StartTimestamp和CommitTimestamp之间没有任何WriteSets Rows被其他事务写入时,本次提交成功,从而可以避免Dirty Write、DIrty Read、Fuzzy Read、Lost Update、Cursor Lost Update以及Read Skew,但无法避免Phantom和Write Skew。
因为Snapshot Isolation只关注WriteSet Rows有没有被其他事务更新,所以在依赖谓语的更新语句中,若谓语的数据不在WriteSet Rows中而被其他并行地事务更新时,Snapshot Isolation是感知不到的,因此SI无法避免Write Skew。
待扩展阅读:https://zhuanlan.zhihu.com/p/54979396