Are you using 9i/10G and still implement optimistic locking with your own column rather than through Oracle’s pseudo column? So am I, but I couldn’t really explain why. My main (defensive) argument would be that the system was built long before 9i. Still, it would make sense for us to change it. Let’s look at a, hopefully, quick example.

Let’s first create a user with two tables and add the same data to both tables.

conn system
create user rowscn identified by rowscn;
alter user rowscn default tablespace users;
alter user rowscn quota unlimited on users;
grant resource, create session to rowscn;

conn rowscn/rowscn
create table t1 (id number, total number);
insert into t1 (id, total) values(1,100);
insert into t1  (id, total) values(2,200);
insert into t1  (id, total) values(3,300);

create table t2 (id number, total number) rowdependencies;
insert into t2 (id, total) values(1,100);
insert into t2  (id, total) values(2,200);
insert into t2  (id, total) values(3,300);
commit;

Two tables with just one “small” difference. We’ll soon see see the difference it makes. To see how this works, we’ll use a simple update of these three rows.

update t1 set total = total + 1 where id = 1;
commit;
update t1 set total = total + 1 where id = 2;
commit;
update t1 set total = total + 1 where id = 3;
commit;


Each row was updated in a different transaction as we committed between each update. If we now look at the pseudo column, ORA_ROWSCN, we will see something like:

select id, total, ora_rowscn from t1;

Hmmmm. Apparently this old blog post has been truncated in a move many years ago. The comments that clarified my thinking at the time seems to have been lost too.