Exemplo de bloqueio offline otimizado em SQL/PL?

Normalmente, acho que os sistemas selecionariam o valor ora_rowscn ao ler os dados e, ao escrevê-los, especificariam esse valor como parte da instrução de atualização. Se nenhuma linha for atualizada, o registro foi excluído ou o registro foi alterado.

Infelizmente, a abordagem ora_rowscn não funcionará muito bem. Ainda permite "atualizações perdidas".

Eu tenho minhas informações do AskTom: ORA_ROWSCN para bloqueio otimista

Exemplo copiado aqui para antecipar links mortos...

set echo on

drop table t;
create table t ( x int primary key, name varchar2(30) ) rowdependencies;

insert into t values ( 1, 'john' );
insert into t values ( 2, 'mary' );
commit;

variable ora_rowscn number
set autoprint on
exec select ora_rowscn into :ora_rowscn from t where x = 2;

set echo off
prompt in another session issue:
prompt variable ora_rowscn number
prompt set autoprint on
prompt exec select ora_rowscn into :ora_rowscn from t where x = 2;;
prompt and come back here and hit enter...
pause

set echo on
update t set name = 'beth' where x = 2 and ora_rowscn = :ora_rowscn;

set echo off
prompt in another session issue:
prompt update t set name = 'sally' where x = 2 and ora_rowscn = :ora_rowscn;;
prompt and come back here and hit enter...
pause

commit;

set echo off
prompt now commit in the other session and notice your lost update :(

O que você deve fazer, IMHO, é adicionar uma coluna extra (por exemplo: Version_Number) à sua tabela onde você define o próximo valor de uma sequência na atualização de um registro (usando um gatilho ou faça isso no código do pacote).

WorkFlow: O cliente consulta algumas linhas e obtém os dados com o version_Number. Ao atualizar esses dados, ele precisa verificar o version_number com o version_number atual no registro. Se forem diferentes, nenhuma linha será atualizada e você deverá retornar um erro ao cliente (No_Records_Found).

create table t ( x int primary key, name varchar2(30), version_Number number);
create sequence s_t start with 1 increment by 1;

insert into t values ( 1, 'john', s_t.nextval);
insert into t values ( 2, 'mary', s_t.nextval);
commit;

select * from t;

Você vê algo assim:

X   NAME   Version_Number

1   john   1

2   mary   2

.

-- first session
update t set name = 'beth', version_number = s_t.nextval where x = 2 and version_number = 2;

-- second session
update t set name = 'sally', version_number = s_t.nextval where x = 2 and version_number = 2; 

-- First session
commit;

-- Second session returns with no updated records...

Como eu disse, você deve avisar o cliente que não houve atualização de cadastro, mas isso pode ter 2 motivos:

• registro desatualizado
• registro deletado

Em ambos os casos, o cliente precisa atualizar esse registro novamente e determinar o que fazer a seguir.