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写入但还未提交的值。

Dirty Write occurs when one transaction overwrites a value that has previously been written by another still in-flight transaction.

会造成的两个影响:

  1. violates the integrity constraint
  2. the system can’t automatically rollback to a before image on transaction abort.

 

解法:long-duration write lock

P1:Dirty Read

事务T1读取到了事务T2已写入但还未commit的值。

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)

在一个事务中已经被读取的值,被另一个事务给改写了。

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以为自己读到的是正确条件下的值,但其实不是。

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事务给覆盖了)

Tech News/Blog Notebook [22.2.6]

P4C:Cursor Lost Update

Cursor Lost Update是Lost Update的一个变体,效果是一样的。

Tech News/Blog Notebook [22.2.6]

解法:holding a lock on the current item of the cursor until the cursor moves or is closed.

A5A:Read Skew(读偏序)

同一事务的两个数据读取之间,被其他事务更改了部分数据的值。

Tech News/Blog Notebook [22.2.6]

A5B:Write Skew(写偏序)

事务中更新某个值的谓语,在用于校验后被其他事务修改了。

Tech News/Blog Notebook [22.2.6]

 

六种隔离级别(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

上一篇:解决IIS7.5及以后中URL文件名有加号或空格导致显示404错误的问题


下一篇:Pytorch训练可视化(TensorboardX)