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

From: 杨廷琨数据和云 2019-12-14

嘉年华听了恩墨学院的一个主题：《重现ORA-01555 细说Oracle 12c Undo数据管理》，吕星昊老师介绍了UNDO的概念以及ORA-1555的产生，并介绍了12c以来Oracle的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：

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.