Optimistic lock

  We are optimistic that the data will not bechanged by some other user; hence ,we wait until the very last moment to findout if we are right.

 

There are many methods of implmentingoptimistic concurrency control. We’ve discussed one whereby the applicationwill store all of the before images of the row in the application itself.

 

 1.   Using a special column that ismaintained by a database trigger or application code to tell us the ‘version ’of the record.

 2.   Using the new oracle 10g feather ORA_ROWSCN

 3 .  Usinh a checksum or hash thatwas computed using the original data

Optimistic locking using a version column

SQL> create table dept
  2  ( deptno   number(2),
  3  dname      varchar2(14),
  4  loc        varchar2(13),
  5  last_mod   timestamp with time zone
  6             default systimestamp
  7             not null,
  8  constraint dept_pk primary key(deptno)
  9  )
 10  /

表已创建。

SQL> insert into dept(deptno,dname,loc)
  2  select deptno,dname,loc
  3  from scott.dept;

已创建4行。

SQL> commit;

提交完成。


SQL> variable deptno number
SQL> viriable dname varchar2(14)
SP2-0734: 未知的命令开头 "viriable d..." - 忽略了剩余的行。
SQL> variable dname varchar2(14)
SQL> variable loc varchar2(13)
SQL> variable last_mod varchar2(50)
SQL>
SQL> begin
  2  :deptno := 10;
  3  select dname,loc, last_mod
  4  into :dname,:loc,:last_mod
  5  from dept
  6  where deptno = :deptno;
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> select :deptno dno, :dname dname, :loc loc, :last_mod lm
  2  from dual;

       DNO DNAME                            LOC
---------- -------------------------------- --------------------------------
LM
-------------------------------------------------------------------------------

        10 ACCOUNTING                       NEW YORK
09-4月 -11 08.54.13.125000 下午 +08:00



SQL> update dept
  2  set dname = initcap(:dname),
  3     last_mod = systimestamp
  4  where deptno = :deptno
  5  and last_mod = to_timestamp_tz(:last_mod);

已更新 1 行。

再执行一次

SQL> update dept
  2  set dname = initcap(:dname),
  3  last_mod = systimestamp
  4  where deptno = :deptno
  5  and last_mod = to_timestamp_tz(:last_mod);

已更新0行。

So, in this case, I suggest encapsulating the update logic in the procedure and not allowing the application to update table directly at all. If it cannot bee trusted to maintain the value in this field, then it cannot be trusted to check it properly either. So the stored procedure would take as inputs the bind variables we used in the previous updates and do exactly the same update. Upon detecting that zero rows were updated. The stored procedure could raise an exception back to the client know the update had, in effect,faild.

另附itpub上的帖子，讲解的很不错

http://www.itpub.net/viewthread.php?tid=466512&highlight=Optimistic%2Blocking

Optimistic locking using a checksum

Starting with oracle 10g release 1, you have the option to use thebuild-in ORA_ROWSCN function.

Optimistic locking using ORA_ROWSCN

ORA_SCN是基于oracle的SYSTEM CLOCK的，即SCN。

This is, by default many rows on singleblock will share the same ORA_ROWSCN value. So you must create the table tosupport the maintenance of ORA_ROWSCN at the row level,the default is at theblock level.

实验

SQL> create table dept
  2  (deptno,dname,loc,data,
  3  constraint dept_pk primary key(deptno)
  4  )
  5  as
  6  select deptno,dname,loc,rpad('*',3500,'*')
  7  from scott.dept;

SQL> select deptno,dname,
  2     dbms_rowid.rowid_block_number(rowid) blockno,
  3     ora_rowscn
  4  from dept;

    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 ACCOUNTING            308    4880552
        20 RESEARCH              308    4880552
        30 SALES                 309    4880552
        40 OPERATIONS            309    4880552

 

 

对DEPT表进行更新

What we will observe next shows theconsequence of ORA_ROWSCN being tracked at the block level. We modified andcommited the changes to single row, but the ORA_ROWSCN values of both of therows on block 308 have been advanceed.

SQL> R
  1  select deptno,dname,
  2  dbms_rowid.rowid_block_number(rowid) blockno,
  3  ora_rowscn
  4* from dept

    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 accounting            308    4881400
        20 RESEARCH              308    4881400
        30 SALES                 309    4880552
        40 OPERATIONS            309    4880552

在更新DEPTNO=10的时候，DEPTNO=20的也被更新了。

So the question becomes howto modify this default behavior. Well,unfortunately, we have to re-create thesegment with ROWDEPENDENCIES enabled.

SQL> drop table dept;

Table dropped.

SQL> create table dept
  2  (deptno,dname,loc,data,
  3  constraint dept_pk primary key(deptno)
  4  )
  5  ROWDEPENDENCIES
  6  as
  7  select deptno,dname,loc,rpad('*',3500,'*')
  8  from scott.dept; 

Table created.

SQL>

SQL> select deptno,dname,
  2  dbms_rowid.rowid_block_number(rowid) blockno,
  3  ora_rowscn
  4  from dept;

    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 ACCOUNTING            324    4888258
        20 RESEARCH              324    4888258
        30 SALES                 325    4888258
        40 OPERATIONS            325    4888258

SQL> update dept
  2  set dname = lower(dname)
  3  where deptno = 10;

1 row updated.

SQL> commit;

Commit complete.

SQL> select deptno,dname,
  2  dbms_rowid.rowid_block_number(rowid) blockno,
  3  ora_rowscn 
  4  from dept;

    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 accounting            324    4890100
        20 RESEARCH              324    4888258
        30 SALES                 325    4888258
        40 OPERATIONS            325    4888258

The only modified ORA_ROWSCN at this pointbelongs to DEPTNO = 10, exactly what we wanted. We can now rely on ORA_ROWSCNto detect row-level changes for us.