變與不變: 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;

在會話3上執行查詢，這時會看到會話2修改送出後的結果：

SQL> SET SQLP 'SQL3> '

SQL3> SELECT NAME FROM T_UNDO WHERE ID = 1119;

UPDATED

回到會話1，對CURSOR變量執行PRINT，檢查得到的結果：

SQL1> PRINT :C

到目前為止，所有都是預期之内的結果，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;

EXTERNAL_LOCATION$

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

會話2修改ID為1118的記錄：

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

會話3檢查确認修改結果：

SQL3> SELECT NAME FROM T_UNDO WHERE ID = 1118;

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

UPDATED WITH PK

再次回到會話1，PRINT遊标變量：

SQL1> PRINT :C

可以看到例外産生了，一緻性讀的結果被破壞了，居然可以查詢到發生在遊标打開之後送出的修改。

導緻這個例外的原因來自于一個隐含函數_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;

I_EXTERNAL_TAB1$

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

會話2進行修改：

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

檢查結果：

SQL3> SELECT NAME FROM T_UNDO WHERE ID = 1117;

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

UPDATED NO ROW CR

回到會話1檢查結果：

Oracle恢複預設的讀一緻性隔離級别。

雖然Oracle認為這種優化隻是針對主鍵或唯一索引等行級通路生效，造成資料一緻性破壞的可能性很小，但是建議對于一緻性要求較高的行業尤其是金融相關行業還是将該特性關閉，避免是以造成的一緻性問題。

原文釋出時間為：2018-11-20

本文作者：xxx

本文來自雲栖社群合作夥伴“

資料和雲

”，了解相關資訊可以關注“

”。