变与不变: Undo构造一致性读的例外情况

简介:

其中介绍了Oracle如何使用UNDO来实现多版本一致性读,使用了OPEN CURSOR的方式非常巧妙地在很少量数据的情况下构造出可重现的案例。不过这个案例存在一点小的瑕疵,因为如果一不小心,很可能会导致结果与预期不符,这是因为这里有一个例外存在。

我们先来模拟一下UNDO构造一致性读的情况,对于Oracle而言,默认的隔离级别是READ COMMIT,也就是说一个会话只能看到其他会话已经提交的修改,未提交的修改或者在当前会话查询发起之后提交的修改都是不可见的。

再介绍一下OPEN CURSOR,Oracle中当一个游标被打开,其结果集就已经确定了,也就是说这个游标会根据OPEN CURSOR这个时间点对应的SCN来构造一致性查询。但是OPEN CURSOR时,对应的SQL并不会被执行,在后续FETCH的时候(对于SQLPLUS而言PRINT命令会触发FETCH),SQL才真正被执行。使用这种办法可以模拟一个大的查询,OPEN CURSOR相当于大的查询的开始时间,其早于其他会话的修改提交时间,而FETCH的时间相当于大查询读取到这条记录的时间,而该时间晚于其他会话提交的时间:

SQL> SET SQLP 'SQL1> '

SQL1> CREATE TABLE T_UNDO (ID NUMBER, NAME VARCHAR2(30));


Table created.

SQL1> INSERT INTO T_UNDO SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

96920 rows created.

SQL1> COMMIT;

Commit complete.

SQL1> CREATE INDEX IND_UNDO_ID ON T_UNDO(ID);

Index created.

SQL1> SELECT NAME FROM T_UNDO WHERE ID = 1119;

NAME

------------------------------------------------------------

I_EXTERNAL_LOCATION1$

SQL1> VAR C REFCURSOR

SQL1> EXEC OPEN :C FOR SELECT NAME FROM T_UNDO WHERE ID = 1119;

PL/SQL procedure successfully completed.

在第一个会话已经构造了一个查询,下面在会话2对这条ID为1119的记录进行修改并提交:

SQL> SET SQLP 'SQL2> '

SQL2> UPDATE T_UNDO SET NAME = 'UPDATED' WHERE ID = 1119;

1 row updated.

SQL2> COMMIT;

Commit complete.

在会话3上执行查询,这时会看到会话2修改提交后的结果:

SQL> SET SQLP 'SQL3> '

SQL3> SELECT NAME FROM T_UNDO WHERE ID = 1119;

NAME

------------------------------------------------------------

UPDATED

回到会话1,对CURSOR变量执行PRINT,检查得到的结果:

SQL1> PRINT :C

NAME

------------------------------------------------------------

I_EXTERNAL_LOCATION1$

到目前为止,所有都是预期之内的结果,Oracle会利用UNDO来存储UPDATE的前镜像,当查询发现需要访问的数据块SCN大于会话发起的SCN,而需要通过UNDO中存储的前镜像来构造一致性读,找到会话需要读取的修改前的数据。

那么例外来自哪里呢,在这个例子中,我们给ID列上创建了一个索引,如果这不是一个普通的索引,而是一个主键,那么效果如何呢:

SQL1> DROP INDEX IND_UNDO_ID;

Index dropped.

SQL1> ALTER TABLE T_UNDO ADD PRIMARY KEY (ID);

Table altered.

SQL1> SELECT NAME FROM T_UNDO WHERE ID = 1118;

NAME

------------------------------------------------------------

EXTERNAL_LOCATION$

SQL1> EXEC OPEN :C FOR SELECT NAME FROM T_UNDO WHERE ID = 1118;

PL/SQL procedure successfully completed.

会话2修改ID为1118的记录:

SQL2> UPDATE T_UNDO SET NAME = 'UPDATED WITH PK' WHERE ID = 1118;

1 row updated.

SQL2> COMMIT;

Commit complete.

会话3检查确认修改结果:

SQL3> SELECT NAME FROM T_UNDO WHERE ID = 1118;

NAME

---------------

UPDATED WITH PK

再次回到会话1,PRINT游标变量:

SQL1> PRINT :C

NAME

------------------------------------------------------------

UPDATED WITH PK

可以看到例外产生了,一致性读的结果被破坏了,居然可以查询到发生在游标打开之后提交的修改。

导致这个例外的原因来自于一个隐含函数_row_cr:

04aeb6986ef36504666090713fc6865222342c2b

Oracle11g以后,这个隐含参数默认值修改为TRUE,这使得Oracle对于基于主键的访问不再采用默认的一致性读方案。当然Oracle做出这种修改的目的是为了提高性能,而且仅对于单行访问生效,而大部分情况下单行访问的效率非常高,因此对于一致性破坏的影响并不明显。到18C为止,该参数仍然为TRUE。

如果关闭该参数:

SQL1> ALTER SYSTEM SET "_row_cr" = FALSE;

System altered.

SQL1> SELECT NAME FROM T_UNDO WHERE ID = 1117;

NAME

------------------------------------------------------------

I_EXTERNAL_TAB1$

SQL1> EXEC OPEN :C FOR SELECT NAME FROM T_UNDO WHERE ID = 1117;

PL/SQL procedure successfully completed.

会话2进行修改:

SQL2> UPDATE T_UNDO SET NAME = 'UPDATED NO ROW CR' WHERE ID = 1117;

1 row updated.

SQL2> COMMIT;

Commit complete.

检查结果:

SQL3> SELECT NAME FROM T_UNDO WHERE ID = 1117;

NAME

------------------

UPDATED NO ROW CR

回到会话1检查结果:

SQL1> PRINT :C

NAME

------------------------------------------------------------

I_EXTERNAL_TAB1$

Oracle恢复默认的读一致性隔离级别。

虽然Oracle认为这种优化只是针对主键或唯一索引等行级访问生效,造成数据一致性破坏的可能性很小,但是建议对于一致性要求较高的行业尤其是金融相关行业还是将该特性关闭,避免因此造成的一致性问题。


原文发布时间为:2018-11-20

本文作者:xxx

本文来自云栖社区合作伙伴“数据和云”,了解相关信息可以关注“数据和云”。

相关文章
|
3月前
|
存储 关系型数据库 MySQL
binlog、redolog、undo log底层原理及ACID特性实现分享
在数据库管理系统中,日志机制是确保数据一致性、完整性和可靠性的关键组件。MySQL数据库中的binlog、redolog和undolog作为其核心日志系统,各自扮演着不同但同样重要的角色。本文将深入探讨这三种日志的底层原理以及它们如何分别实现ACID(原子性、一致性、隔离性、持久性)特性的不同方面。
65 0
|
5月前
|
关系型数据库 数据库 存储
顺序读和InnoDB的数据组织
【7月更文挑战第7天】自增主键优化顺序读:保证数据物理排序,提升范围查询效率。InnoDB引擎中,主键决定数据在页的存储。当插入的数据引起页分裂,如从1、2、3、5、6、7插入4,会导致相邻逻辑页在磁盘上可能分散,影响性能。了解页结构深化数据库知识,面试时可根据情况深入讨论。
38 2
|
6月前
|
存储 SQL 关系型数据库
binlog、redolog和undolog三者有何区别?
MySQL中的binlog、redo log和undo log是日志文件,各有特定作用。binlog用于数据备份、恢复和复制,适用于所有存储引擎。redo log记录事务修改,用于崩溃恢复和数据持久性,仅InnoDB存储引擎支持。undo log保存事务修改前的状态,用于事务回滚和MVCC,也仅InnoDB支持。它们在功能和记录内容上有明显区别,有助于事务管理和数据库一致性。
143 0
|
7月前
|
SQL 数据库 数据库管理
事务管理,事务的概念(原子性、一致性、隔离性和持久性(ACID特性))、事务的控制(BEGIN、COMMIT和ROLLBACK)
事务管理,事务的概念(原子性、一致性、隔离性和持久性(ACID特性))、事务的控制(BEGIN、COMMIT和ROLLBACK)
|
SQL 存储 缓存
【ACID底层实现原理、一致性非锁定读(MVCC的原理)、BufferPool缓存机制、重做日志刷盘策略、隔离级别】
【ACID底层实现原理、一致性非锁定读(MVCC的原理)、BufferPool缓存机制、重做日志刷盘策略、隔离级别】
51538 1
【ACID底层实现原理、一致性非锁定读(MVCC的原理)、BufferPool缓存机制、重做日志刷盘策略、隔离级别】
|
SQL Java 数据库
【事务与并发】- 不同事务读取相同数据问题
在加了事务的接口中,不同的业务或者是出现并发的时候,发现了一些SQL读取问题,两个都被事务包裹的方法,各自是隔离的,如果一方的事务延时提交,就会导致另一方读取出来的数据相同,并不是修改后的数据。
132 0
|
SQL 存储 Oracle
精通Java事务编程(3)-弱隔离级别之快照隔离和可重复读
表面看,RC已满足事务所需的一切特征:支持中止(原子性),防止读取不完整的事务结果,并防止并发写的混乱。这点很关键!为我们的开发省去一大堆麻烦。
126 0
|
消息中间件 JavaScript 小程序
MySQL 底层之 MVCC、回滚段、一致性读、锁定读
MySQL 底层之 MVCC、回滚段、一致性读、锁定读
|
关系型数据库 MySQL
幻读“异常”引出的快照读创建点问题
幻读“异常”引出的快照读创建点问题