变与不变: 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.

SQL> SET SQLP 'SQL2> '

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

1 row updated.

SQL2> COMMIT;

Commit complete.

SQL> SET SQLP 'SQL3> '

SQL3> SELECT NAME FROM T_UNDO WHERE ID = 1119;

NAME

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

UPDATED

SQL1> PRINT :C  

NAME

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

I_EXTERNAL_LOCATION1$

那么例外来自哪里呢，在这个例子中，我们给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：

如果关闭该参数：

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

亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>