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"