Download
1 / 21
210 likes | 368 Views
数据库隔离级别. y ubai.lk@taobao.com. 什么是事务隔离. Wikipedia: In database systems, isolation is a property that defines how/when the changes made by one operation become visible to other concurrent operations. 我对事务隔离性的定义 ： 以事务为最小单位，事务在有 一致性约束的数据集合 上进行读写操作，这些读写操作结果对其他事务的可见性问题。 全 库的隔离性设置
E N D
数据库隔离级别 yubai.lk@taobao.com
什么是事务隔离 • Wikipedia: In database systems, isolation is a property that defines how/when the changes made by one operation become visible to other concurrent operations. • 我对事务隔离性的定义：以事务为最小单位，事务在有一致性约束的数据集合上进行读写操作，这些读写操作结果对其他事务的可见性问题。 • 全库的隔离性设置 • 表级别/单次事务的隔离性设置
Agenda • ANSI SQL隔离级别 • 基于锁的SQL隔离级别 • Snapshot Isolation • Oceanbase的隔离级别
ANSI SQL Isolation Level • Dirty Read • Read uncommited • Non-repeatable Read • Read commited • Phantom Read • Repeatable Read • Serializable • Commonly known as fully serializable execution
ANSL SQL Isolation Levels • Dirty Read • 单行 • Begin[x=50] w1[x=10] r2[x=10] abort1 • 事务2读取到事务1正在修改的数据，之后事务1回滚 • 多行 • Begin[x=50,y=50] r1[x=50]w1[x=10] r2[x=10]r2[y=50] r1[y=50]w1[y=90] commit1 • 事务2读取到事务1修改到一半的数据，违反一致性约束 • 脏读隔离性下，允许出现的读写模式 • w1[x]...r2[x]...(c1 or a1)
ANSL SQL Isolation Levels • Non-repeatable Read • 单行 • Begin[x=50] r1[x=50] w2[x=10] commit2 r1[x=10] commit1 • 事务1在事务2提交前后读取到同一行数据的不同值 • 多行 • Begin[x=50,y=50] r1[x=50] r2[x=50]w2[x=10]r2[y=50]w2[y=90] commit2 r1[y=90] commit1 • 事务1在事务2提交前后读取到的两行数据违反一致性约束 • 不可重复读隔离性下，允许出现的读写模式 • r1[x]...w2[x]...(c1 or a1)
ANSL SQL Isolation Levels • Phantom Read • 单个数据范围 • r1[Set P] w2[insert x into Set P] commit2 r1[Set P] commit1 • 事务1在事务2提交后在同一个数据范围读取到新插入的行 • 数据范围与其他行 • r1[Set P] w2[insert x to P] r2[z] w2[z] c2 r1[z] commit1 • 设z为数据范围P内的行数，事务1在事务2提交后读到z值与在事务2提交之前扫描到的行数违反一致性约束 • 幻读隔离性下，允许出现的读写模式 • r1[P]...w2[y in P]...(c1 or a1)
Lock Based Isolation • Read Lock • Maybe upgrade to write lock • Write Lock • Lock on Row • Lock on Predicate • Short Duration Lock • Cursor Stable Lock • 2 Phase Lock • A transaction has 2 phase writes (reads) if it does not set a new Write (Read) lock on a data item after releasing a Write (Read) lock
Lock Based Isolation • Dirty Write • Locking Read Uncommited • Locking Read commited • Cursor Stability • Locking Repeatable Read • Locking Serializable
Lock Based Isolation • Dirty Write • No Write Lock; No Read Lock; 仅保证对单个数据修改的原子性 • w1[x=10] w2[x=20] w2[y=20] c2 w1[y=10] commit1 • 违反x=y的一致性约束
Lock Based Isolation • Locking Read Uncommited • 2 Phase Write Lock on Row; No Read Lock • Non-Dirty Write Case: • wlock1[x]w1[x=10] wlock2[x][blocked] w2[x=20] w2[y=20] commit2w1[y=10]commit1 • Read Uncommited Case: • Begin[x=50] wlock1[x] w1[x=10] NoRLock2[x] r2[x=10] abort1 • Begin[x=50,y=50] r1[x=50] wlock1[x] w1[x=10] NoRLock2[x]r2[x=10]r2[y=50] r1[y=50] w1[y=90] commit1
Lock Based Isolation • Locking Read commited • 2 Phase Write Lock on Row; Short duration Read Lock on Row • Read Commited Case: • Begin[x=50] wlock1[x] w1[x=10] RLock2[x][blocked] r2[x=10] abort1 • Begin[x=50,y=50] r1[x=50] wlock1[x] w1[x=10] RLock2[x][blocked]r2[x=10]r2[y=50] r1[y=50] w1[y=90] commit1 • Non-repeatable Read Case: • Begin[x=50] RLock1[x]r1[x=50]unlock1[x]wlock2[x][non-blocked] w2[x=10] commit2 r1[x=10] commit1 • Begin[x=50,y=50] RLock1[x]r1[x=50]unlock1[x] r2[x=50] wlock2[x] [non-blocked] w2[x=10] r2[y=50] w2[y=90] commit2 r1[y=90]commit1
Lock Based Isolation • Cursor Stability • 2 Phase Write Lock on Row; Cursor Stability Read Lock on Row • Cursor Stability Case: • Begin[x=50,y=50] RLock1[x]r1[x=50] r2[x=50] wlock2[x][blocked]w2[x=10] r2[y=50] w2[y=90] commit2r1[y=50] commit1 • Non-repeatable Read Case: • Begin[x=50,y=50,z=100] RLock1[x]r1[x=50]unlock1[x]RLock1[z]r1[z=100] r2[x=50] wlock2[x][non-blocked]w2[x=10]r2[y=50] w2[y=90] commit2 r1[y=90] commit1
Lock Based Isolation • Locking Repeatable Read • 2 Phase Write Lock on Row; 2 Phase Read Lock on Row • Repeatable Read Case: • Begin[x=50,y=50,z=100] RLock1[x]r1[x=50]RLock1[z]r1[z=100] r2[x=50] wlock2[x][blocked]w2[x=10]r2[y=50] w2[y=90] commit2 r1[y=50] commit1 • Phantom Read Case: • r1[Set P] w2[insert x to P] r2[z] w2[z] commit2 r1[z] commit1
Lock Based Isolation • Locking Serializable • 2 Phase Write Lock on Row and Predicate; • 2 Phase Read Lock on Row and Predicate • RLock1[Set P]r1[Set P] wlock2[Set P][blocked] w2[insert x to P] r2[z] w2[z] commit2 r1[z] commit1
Snapshot Isolation • 只读事务与读写事务互不阻塞 • 每次更新的数据都成为一个历史快照，可以对指定快照版本读取 • 只读事务从历史快照读取，满足serializable • 乐观锁处理写事务冲突: • First-commiter-wins: The transaction T1 successfully commits only if no other transaction T2 with a Commit-Timestamp in T1’s execution interval [Start-Timestamp, Commit-Timestamp] wrote data that T1 also wrote. Otherwise, T1 will abort.
Snapshot Isolation • 隔离性问题 • r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur) • T1: A = B + 1 • T2: B = A + 1 • Begin[A=0,B=0] r1[A=0] r2[B=0] w1[B=1] w2[A=1] commit1 commit2 • 规避方法 • 在用户层对事务T1和T2构造冲突，使得其中一个事务回滚 • T1: A = B + 1; C = 1; • T2: B = A + 1; C = 2; • 提供Select … for update语义，对读取的行也看作更新，与其他更新事务冲突时回滚
OceanBase Isolation • 基于Snapshot读写隔离 • 只读事务从历史快照读取，满足Serializable隔离级别 • 基于锁的写事务冲突处理 • 行级锁 • 2 Phase Write Lock • 全库可以设置两种隔离级别 • Read Commited • 2 Phase Write Lock on Row; Short duration Read Lock on Row • Serializable • 2 Phase Write Lock on Row; 2 Phase Read Lock on Row • 遇到Predicate Lock的情况转化为单线程处理
OceanBase Isolation • 支持在Read Commited级别下提高事务的隔离级别 • Select … for update where rowkey=*** • 对指定的行加TwoPhase Read Lock • 对Predicate执行select … for update的情况转化为单线程处理 • 要更新这行之前将Read Lock升级为Write Lock • Read Lock升级为Write Lock需要注意处理死锁
Reference • A Critique of ANSI SQL Isolation Levels • http://en.wikipedia.org/wiki/Isolation_(database_systems) • http://en.wikipedia.org/wiki/Snapshot_isolation
