ORA_ROWSCN的并发控制和10g的乐观锁

问题描述
=============
从ORA_ROWSCN的伪列来看，数据库并发控制和乐观锁似乎并没有按照想象的工作。

 

变化
=============
所有表都有一个ORA_ROWSCN伪劣，它既不固定，也不存在于外部。它表示某行最近的SCN，也就是说是该行最后的COMMIT操作。例如：

 

SELECT ora_rowscn, last_name, salary
  FROM employees
  WHERE employee_id = 7788;

ORA_ROWSCN    NAME    SALARY
----------    ----    ------
    202553    Fudd      3000

 

取得该行最后的COMMIT操作，大约在SCN 202553。使用SCN_TO_TIMESTAMP函数可以将SCN转换成相应的TIMESTAMP值。
ORA_ROWSCN是一个保守的最后提交时间的上限值，实际commit的SCN可能更早。ORA_ROWSCN对于行依赖表(使用CREATE TABLE的ROWDEPENDENCIES子句)更精确(接近实际commit的SCN)。

应用程序检查到记录行的响应ORA_ROWSCN为202553，一会儿后，应用程序需要更新该行，但只有该行没有改变过，操作使用条件仍然让ORA_ROWSCN为202553。如下的语句：

UPDATE employees
  SET salary = salary + 100
  WHERE employee_id = 7788
  AND ora_rowscn = 202553;

0 rows updated.

 

可见，在该条件下，更新语句失败，因为ORA_ROWSCN已经不再是202553了。因此，用户或另一个应用想要改变行以及进行COMMIT操作，需要比记录的ORA_ROWSCN更新。
应用程序再次查询新数据行和ORA_ROWSCN，假设ORA_ROWSCN现在为415639。应用尝试再次带条件更新行，使用新的ORA_ROWSCN。这次更行成功了，并且commit，过程如下：
SQL> UPDATE employees SET salary = salary + 100
     WHERE empno = 7788 AND ora_rowscn = 415639;

 

1 row updated.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> SELECT ora_rowscn, name, salary FROM employees WHERE empno = 7788;

 

ORA_ROWSCN    NAME    SALARY
----------    ----    ------
    465461    Fudd      3100

 

新的COMMIT响应的SCN为465461。

除了在UPDATE中使用ORA_ROWSCN之外，还可以在DELETE的WHERE子句中或者闪回查询的AS OF子句中使用。

原因
==================
如果有2个session基于ORA_ROWSCN更新同一行，可能会造成该问题。第2个hang住的session可能无法完成，因为行的scn在第1个session提交之后改变了。

下面这个例子说明这一行为：

Session 1:
**********
-- 在本例中ROWDEPENDENCIES子句存在与否对下面的过程没有影响，因为表只有1条记录。
.
SQL> create table test (a number,b number)  ROWDEPENDENCIES ;
Table created.
.
SQL>  insert into test values(1,1);
1 row created.
.
SQL> commit;
Commit complete.
.
SQL> select a,b,ora_rowscn from test where a=1;
         A          B ORA_ROWSCN
---------- ---------- ----------
         1          1     535526
.
SQL> update test set a=9,b=9 where ORA_ROWSCN=535526;
1 row updated.
.
SQL> select a,b,ora_rowscn from test ;
         A          B ORA_ROWSCN
---------- ---------- ----------
         9          9
.
Session 2:
**********
SQL> update test set a=8,b=8 where ORA_ROWSCN=535526;
.
该语句被hang住，因为session 1还没有提交。
.
Session 1:
**********
SQL> commit;
Commit complete.
.
SQL> select a,b,ora_rowscn from test ;
         A          B ORA_ROWSCN
---------- ---------- ----------
         9          9     535547
.
Session 2:
**********
刚才被hang住的session 2的update语句现在执行了，更新了如下一条语句：
.
--(hang住的语句执行成功了)
.
SQL> update test set a=8,b=8 where ORA_ROWSCN=535526;
1 row updated.
.
SQL> select a,b,ora_rowscn from test ;
         A          B ORA_ROWSCN
---------- ---------- ----------
         8          8
.
SQL> commit;
Commit complete.
.
SQL> select a,b,ora_rowscn from test ;
         A          B ORA_ROWSCN
---------- ---------- ----------
         8          8     535556

 

解决方案
==================
如果想要该表的指定行只在第1个session中有效，之后的尝试都视为NOOP(无效操作)，那么最好通过select-for-update + update的方式进行，例如：
session-1: select * from t where rowid = and ora_rowscn = for update;
session-1: update t set i = where rowid = and ora_rowscn = ;
session-2: select * from t where rowid = and ora_rowscn = for update;
session-1: commit;
session-2: update t set i = where rowid = and ora_rowscn = ;

session-2的最后一句update将会失败，找不到想要的行。"rowid = "子句最好是能替换为其他定位行的条件(如其他列的值或主键等)。

例子：
SESSION 1 :
***********
SQL> create table test00 (id number, txt varchar2(30)) rowdependencies;
SQL> insert into test00 values (1, 'testing ora_rowscn');
SQL> insert into test00 values (2, 'testing ora_rowscn');
SQL> commit;

SQL> select ora_rowscn from test00 where id = 1;
SQL> select id,txt from test00 where ora_rowscn = < SCN1 > and id = 1 for update;
或
SQL> select * from test00 where ora_rowscn = < SCN1 > and id = 1 for update;

SQL> update test00 set txt = 'changed' where id = 1;

SESSION 2:
***********
SQL> select id,txt from test00 where ora_rowscn = and id = 1 for update;
或
SQL> select * from test00 where ora_rowscn = and id = 1 for update;
这里会hang住。

SESSION 1:
***********
SQL> commit;

SESSION 2:
***********
现在显示："no rows selected"