数据库隔离级别
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
4
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提交前后读取到的两行数据违反一致性约束
多行 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)
Phantom Read
Repeatable Read
Serializable
Commonly known as fully serializable execution
3
ANSL SQL Isolation Levels
Dirty Read
单行 Begin[x=50] w1[x=10] r2[x=10] abort1 事务2读取到事务1正在修改的数据，之后事务1回滚
什么是事务隔离
Wikipedia: In database systems, isolation is a property that defines
how/when the changes made by one operation become visible to other concurrent operations. 我对事务隔离性的定义：以事务为最小单位，事务在有一致性约束的数 据集合上进行读写操作，这些读写操作结果对其他事务的可见性问题。 全库的隔离性设置 表级别/单次事务的隔离性设置
Dirty Write Locking Read Uncommited Locking Read commited Cursor Stability Locking Repeatable Read Locking Serializable
8
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的一致性约束
1
Agenda
ANSI SQL隔离级别 基于锁的SQL隔离级别 Snapshot Isolation Oceanbase的隔离级别
2
ANSI SQL Isolation Level
Dirty Read
Read uncommited
Non-repeatable Read
Read commited
不可重复读隔离性下，允许出现的读写模式 r1[x]...w2[x]...(c1 or a1)
5
ANSL SQL Isolation Levels
Phantom Read
单个数据范围 r1[Set P] w2[insert x into Set P] commit2 r1[Set P] commit1 事务1在事务2提交后在同一个数据范围读取到新插入的行
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 Isolation
6
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
9
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] commit2 w1[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
数据范围与其他行 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)
10ຫໍສະໝຸດ Baidu
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]
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提交前后读取到的两行数据违反一致性约束
多行 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)
Phantom Read
Repeatable Read
Serializable
Commonly known as fully serializable execution
3
ANSL SQL Isolation Levels
Dirty Read
单行 Begin[x=50] w1[x=10] r2[x=10] abort1 事务2读取到事务1正在修改的数据，之后事务1回滚
什么是事务隔离
Wikipedia: In database systems, isolation is a property that defines
how/when the changes made by one operation become visible to other concurrent operations. 我对事务隔离性的定义：以事务为最小单位，事务在有一致性约束的数 据集合上进行读写操作，这些读写操作结果对其他事务的可见性问题。 全库的隔离性设置 表级别/单次事务的隔离性设置
Dirty Write Locking Read Uncommited Locking Read commited Cursor Stability Locking Repeatable Read Locking Serializable
8
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的一致性约束
1
Agenda
ANSI SQL隔离级别 基于锁的SQL隔离级别 Snapshot Isolation Oceanbase的隔离级别
2
ANSI SQL Isolation Level
Dirty Read
Read uncommited
Non-repeatable Read
Read commited
不可重复读隔离性下，允许出现的读写模式 r1[x]...w2[x]...(c1 or a1)
5
ANSL SQL Isolation Levels
Phantom Read
单个数据范围 r1[Set P] w2[insert x into Set P] commit2 r1[Set P] commit1 事务1在事务2提交后在同一个数据范围读取到新插入的行
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 Isolation
6
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
9
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] commit2 w1[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
数据范围与其他行 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)
10ຫໍສະໝຸດ Baidu
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]