数据库隔离级别课件

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,什么是事务隔离,Wikipedia:,In database systems,isolation is a property that defines how/when the changes made by one operation become visible to other concurrent operations.,我对事务隔离性的定义：以事务为最小单位，事务在有,一致性约束的数据集合,上进行读写操作，这些读写操作结果对其他事务的可见性问题。,全库的隔离性设置,表级别,/,单次事务的隔离性设置,1,什么是事务隔离Wikipedia:In database,Agenda,ANSI SQL,隔离级别,基于锁的,SQL,隔离级别,Snapshot Isolation,Oceanbase,的隔离级别,2,AgendaANSI SQL隔离级别2,ANSI SQL Isolation Level,Dirty Read,Read uncommited,Non-repeatable Read,Read commited,Phantom Read,Repeatable Read,Serializable,Commonly known as fully serializable execution,3,ANSI SQL Isolation LevelDirty,ANSL SQL Isolation Levels,Dirty Read,单行,Beginx=50 w1x=10 r2x=10 abort1,事务,2,读取到事务,1,正在修改的数据，之后事务,1,回滚,多行,Beginx=50,y=50 r1x=50w1x=10,r2x=10r2y=50,r1y=50w1y=90 commit1,事务,2,读取到事务,1,修改到一半的数据，违反一致性约束,脏读隔离性下，允许出现的读写模式,w1x.r2x.(c1 or a1),4,ANSL SQL Isolation LevelsDirty,ANSL SQL Isolation Levels,Non-repeatable Read,单行,Beginx=50 r1x=50 w2x=10 commit2 r1x=10 commit1,事务,1,在事务,2,提交前后读取到同一行数据的不同值,多行,Beginx=50,y=50,r1x=50,r2x=50w2x=10r2y=50w2y=90 commit2,r1y=90,commit1,事务,1,在事务,2,提交前后读取到的两行数据违反一致性约束,不可重复读隔离性下，允许出现的读写模式,r1x.w2x.(c1 or a1),5,ANSL SQL Isolation LevelsNon-r,ANSL SQL Isolation Levels,Phantom Read,单个数据范围,r1Set P w2insert x into Set P commit2 r1Set P commit1,事务,1,在事务,2,提交后在同一个数据范围读取到新插入的行,数据范围与其他行,r1,Set,P,w2insert,x,to P r2z w2z c2,r1z,c,ommit,1,设,z,为数据范围,P,内的行数，事务,1,在事务,2,提交后读到,z,值与在事务,2,提交之前扫描到的行数违反一致性约束,幻读隔离性下，允许出现的读写模式,r1P.w2y in P.(c1 or a1),6,ANSL SQL Isolation LevelsPhant,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,7,Lock Based IsolationRead Lock7,Lock Based Isolation,Dirty Write,Locking Read Uncommited,Locking Read commited,Cursor Stability,Locking Repeatable Read,Locking Serializable,8,Lock Based IsolationDirty Writ,Lock Based Isolation,Dirty Write,No Write Lock;No Read Lock;,仅保证对单个数据修改的原子性,w1x,=10,w2x,=20,w2y,=20,c2,w1y,=10,c,ommit,1,违反,x=y,的一致性约束,9,Lock Based IsolationDirty Writ,Lock Based Isolation,Locking Read Uncommited,2 Phase Write Lock on Row;No Read Lock,Non-Dirty Write Case:,wlock1x,w1x,=10,wlock2xblocked,w2x,=20,w2y,=20,c,ommit,2,w1y,=10,c,ommit,1,Read Uncommited Case:,Beginx=50,wlock1x,w1x=10,NoRLock2x,r2x=10 abort1,Beginx=50,y=50 r1x=50,wlock1x,w1x=10,NoRLock2x,r2x=10r2y=50,r1y=50 w1y=90 commit1,10,Lock Based IsolationLocking Re,Lock Based Isolation,Locking Read commited,2 Phase Write Lock on Row;Short duration Read Lock on Row,Read Commited Case:,Beginx=50,wlock1x,w1x=10,RLock2xblocked,r2x=10,abort1,Beginx=50,y=50 r1x=50,wlock1x,w1x=10,RLock2xblocked,r2x=10r2y=50,r1y=50 w1y=90 commit1,Non-repeatable Read Case:,Beginx=50,RLock1x,r1x=50,unlock1x,wlock2xnon-blocked,w2x=10 commit2,r1x=10,commit1,Beginx=50,y=50,RLock1x,r1x=50,unlock1x,r2x=50,wlock2x non-blocked,w2x=10 r2y=50 w2y=90 commit2,r1y=90,commit1,11,Lock Based IsolationLocking Re,Lock Based Isolation,Cursor Stability,2 Phase Write Lock on Row;Cursor Stability Read Lock on Row,Cursor Stability Case:,Beginx=50,y=50,RLock1x,r1x=50,r2x=50,wlock2xblocked,w2x=10 r2y=50 w2y=90 commit2,r1y=50,commit1,Non-repeatable Read Case:,Beginx=50,y=50,z=100,RLock1x,r1x=50,unlock1x,RLock1z,r1z=100,r2x=50,wlock2xnon-blocked,w2x=10,r2y=50 w2y=90 commit2,r1y=90,commit1,12,Lock Based IsolationCursor Sta,Lock Based Isolation,Locking Repeatable Read,2 Phase Write Lock on Row;2 Phase Read Lock on Row,Repeatable Read Case:,Beginx=50,y=50,z=100,RLock1x,r1x=50,RLock1z,r1z=100,r2x=50,wlock2xblocked,w2x=10,r2y=50 w2y=90 commit2,r1y=50,commit1,Phantom Read Case:,r1,Set,P,w2insert,x,to P r2z w2z c,ommit,2,r1z,c,ommit,1,13,Lock Based IsolationLocking Re,Lock Based Isolation,Locking Serializable,2 Phase Write Lock on Row and Predicate;,2 Phase Read Lock on Row and Predicate,RLock1Set P,r1,Set,P,wlock2Set Pblocked,w2insert,x,to P r2z w2z c,ommit,2,r1z,c,ommit,1,14,Lock Based IsolationLocking Se,Snapshot Isolation,只读事务与读写事务互不阻塞,每次更新的数据都成为一个历史快照，可以对指定快照版本读取,只读事务从历史快照读取，满足,serializable,乐观锁处理写事务冲突,:,First-commiter-wins:,The transaction T1 successfully commits only if no other transaction T2 with a Commit-Timestamp in T1s execution interval Start-Timestamp,Commit-Timestamp wrote data that T1 also wrote.Otherwise,T1 will abort.,15,Snapshot Isolation只读事务与读写事务互不阻,Snapshot Isolation,隔离性问题,r1x.r2y.w1y.w2x.(c1 and c2 occur),T1:A=B+1,T2:B=A+1,BeginA=0,B=0 r1A=0 r2B=0,w1B=1 w2A=1,commit1 commit2,规避方法,在用户层对事务,T1,和,T2,构造冲突，使得其中一个事务回滚,T1:A=B+1;C=1;,T2:B=A+1;C=2;,提供,Select for update,语义，对读取的行也看作更新，与其他更新事务冲突时回滚,16,Snapshot Isolation隔离性问题16,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,的情况转化为单线程处理,17,OceanBase Isola