Oracle悲观封锁和乐观封锁在比较[@more@]在Php格式中,冒号被转义了,应该在的有的old和new前加冒号,以示更加清晰.

--oracle悲观封锁应用示例(以下包是研究Oracle的11i后模拟的)：
--ttx_tmp.sql


create or replace package ttx_temp_pkg is
g_ok varchar2(2) := 'OK'; 
g_changed varchar2(21) :='CHANGED BY OTHER USER';
g_no_row varchar2(14) := 'NO ROW UPDATED';

procedure lock_row
(
x_message out nocopy varchar2,
p_id in number,
p_owner in varchar2,
p_object_name in varchar2,
p_subobject_name in varchar2,
p_object_id in number,
p_data_object_id in number,
p_object_type in varchar2,
p_created in date,
p_last_ddl_time in date,
p_timestamp in varchar2,
p_status in varchar2,
p_temporary in varchar2,
p_generated in varchar2,
p_secondary in varchar2
);

procedure update_row
(
x_message out nocopy varchar2,
p_id in number,
p_owner in varchar2,
p_object_name in varchar2,
p_subobject_name in varchar2,
p_object_id in number,
p_data_object_id in number,
p_object_type in varchar2,
p_created in date,
p_last_ddl_time in date,
p_timestamp in varchar2,
p_status in varchar2,
p_temporary in varchar2,
p_generated in varchar2,
p_secondary in varchar2 
);

procedure delete_row
( 
p_id in number,
x_message out nocopy varchar2
);

end ttx_temp_pkg;
/

create or replace package body ttx_temp_pkg is

procedure lock_row
(
x_message out nocopy varchar2,
p_id in number,
p_owner in varchar2,
p_object_name in varchar2,
p_subobject_name in varchar2,
p_object_id in number,
p_data_object_id in number,
p_object_type in varchar2,
p_created in date,
p_last_ddl_time in date,
p_timestamp in varchar2,
p_status in varchar2,
p_temporary in varchar2,
p_generated in varchar2,
p_secondary in varchar2
) is
cursor cur_ttx_temp is
select 
id,
owner,
object_name,
subobject_name,
object_id,
data_object_id,
object_type,
created,
last_ddl_time,
timestamp,
status,
temporary,
generated,
secondary
from ttx_temp
where id = p_id
for update nowait; 
ctt cur_ttx_temp%rowtype; 

begin
open cur_ttx_temp;
fetch cur_ttx_temp into ctt; 
if ((ctt.owner=p_owner) or
(ctt.owner is null and p_owner is null))
and ((ctt.object_name=p_object_name) or
(ctt.object_name is null and p_object_name is null))
and ((ctt.subobject_name=p_subobject_name) or
(ctt.subobject_name is null and p_subobject_name is null))
and ((ctt.object_id=p_object_id) or
(ctt.object_id is null and p_object_id is null))
and ((ctt.data_object_id=p_data_object_id) or
(ctt.data_object_id is null and p_data_object_id is null))
and ((ctt.object_type=p_object_type) or
(ctt.object_type is null and p_object_type is null))
and ((ctt.created=p_created) or
(ctt.created is null and p_created is null))
and ((ctt.last_ddl_time=p_last_ddl_time) or
(ctt.last_ddl_time is null and p_last_ddl_time is null))
and ((ctt.timestamp=p_timestamp) or
(ctt.timestamp is null and p_timestamp is null))
and ((ctt.status=p_status) or
(ctt.status is null and p_status is null))
and ((ctt.temporary=p_temporary) or
(ctt.temporary is null and p_temporary is null))
and ((ctt.generated=p_generated) or
(ctt.generated is null and p_generated is null))
and ((ctt.secondary=p_secondary) or
(ctt.secondary is null and p_secondary is null)) then 
x_message := g_ok; 
else
x_message := g_changed;
end if; 
close cur_ttx_temp;

exception
when others then
x_message := substrb(sqlcode||'/'||sqlerrm,1,200); 
if cur_ttx_temp%isopen then
close cur_ttx_temp;
end if; 
end;

procedure update_row
(
x_message out nocopy varchar2,
p_id in number,
p_owner in varchar2,
p_object_name in varchar2,
p_subobject_name in varchar2,
p_object_id in number,
p_data_object_id in number,
p_object_type in varchar2,
p_created in date,
p_last_ddl_time in date,
p_timestamp in varchar2,
p_status in varchar2,
p_temporary in varchar2,
p_generated in varchar2,
p_secondary in varchar2 
) is
begin
update ttx_temp
set 
owner = p_owner, 
object_name = p_object_name,
subobject_name = p_subobject_name,
object_id = p_object_id,
data_object_id = p_data_object_id,
object_type = p_object_type,
created = p_created,
last_ddl_time = p_last_ddl_time,
timestamp = p_timestamp,
status = p_status,
temporary = p_temporary,
generated = p_generated,
secondary = p_secondary 
where id=p_id;

if sql%rowcount > 0 then
x_message := g_ok;
else
x_message := g_no_row;
end if; 
exception
when others then
x_message := substrb(sqlcode||'/'||sqlerrm,1,200);
end;

procedure delete_row
( 
p_id in number,
x_message out nocopy varchar2
) is
begin
delete ttx_temp
where id=p_id;
if sql%rowcount > 0 then
x_message := g_ok;
else
raise no_data_found;
end if;

exception
when others then
x_message := substrb(sqlcode||'/'||sqlerrm,1,200); 
end; 

end ttx_temp_pkg;
/

--环境准备

ttx@TTX>create table ttx_temp as select *
2 from dba_objects where rownum < 100;

Table created.

ttx@TTX>alter table ttx_temp add id number;

Table altered.

ttx@TTX>create sequence ttx_temp_s;

Sequence created.

ttx@TTX>update ttx_temp set id = ttx_temp_s.nextval;

99 rows updated.

ttx@TTX>commit;

Commit complete.

ttx@TTX>alter table ttx_temp modify id not null;

Table altered.

ttx@TTX>alter table ttx_temp add constraint ttx_temp_pk primary key(id);

Table altered.

ttx@TTX>show errros;
SP2-0158: unknown SHOW option "errros"
ttx@TTX>desc ttx_temp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
ID NOT NULL NUMBER

ttx@TTX>column object_name format a30
ttx@TTX>column owner format a15
ttx@TTX>select owner,object_name from ttx_temp where id < 10;

OWNER OBJECT_NAME
--------------- ------------------------------
SYS ICOL$
SYS I_USER1
SYS CON$
SYS UNDO$
SYS C_COBJ#
SYS I_OBJ#
SYS PROXY_ROLE_DATA$
SYS I_IND1
SYS I_CDEF2

9 rows selected.

ttx@TTX>desc ttx_temp_pkg
PROCEDURE DELETE_ROW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID NUMBER IN
X_MESSAGE VARCHAR2 OUT
PROCEDURE LOCK_ROW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_MESSAGE VARCHAR2 OUT
P_ID NUMBER IN
P_OWNER VARCHAR2 IN
P_OBJECT_NAME VARCHAR2 IN
P_SUBOBJECT_NAME VARCHAR2 IN
P_OBJECT_ID NUMBER IN
P_DATA_OBJECT_ID NUMBER IN
P_OBJECT_TYPE VARCHAR2 IN
P_CREATED DATE IN
P_LAST_DDL_TIME DATE IN
P_TIMESTAMP VARCHAR2 IN
P_STATUS VARCHAR2 IN
P_TEMPORARY VARCHAR2 IN
P_GENERATED VARCHAR2 IN
P_SECONDARY VARCHAR2 IN
PROCEDURE UPDATE_ROW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_MESSAGE VARCHAR2 OUT
P_ID NUMBER IN
P_OWNER VARCHAR2 IN
P_OBJECT_NAME VARCHAR2 IN
P_SUBOBJECT_NAME VARCHAR2 IN
P_OBJECT_ID NUMBER IN
P_DATA_OBJECT_ID NUMBER IN
P_OBJECT_TYPE VARCHAR2 IN
P_CREATED DATE IN
P_LAST_DDL_TIME DATE IN
P_TIMESTAMP VARCHAR2 IN
P_STATUS VARCHAR2 IN
P_TEMPORARY VARCHAR2 IN
P_GENERATED VARCHAR2 IN
P_SECONDARY VARCHAR2 IN

ttx@TTX>

--前台操作用户通过前台界面查出需要的数据(select * from ttx_temp)，
--然后对对ID=1的这行数据的SUBOBJECT_NAME进行修改后提交更新时，
--程序应该类似于下面的方式调用，就可以保证数据更新不被丢失


begin
--old.xxx 在调用时用具体的初始值替代,在Oracle Form和Delphi中都支持Old和New的模式,
--在JAVA中我不太清楚,应该是有办法的
ttx_temp_pkg.lock_row
(
x_message => v_message, 
p_id => id, 
p_owner => old.owner,
p_object_name => old.object_name,
p_subobject_name => old.subobject_name,
p_object_id => old.object_id,
p_data_object_id => old.data_object_id,
p_object_type => old.object_type,
p_created => old.created,
p_last_ddl_time => old.last_ddl_time,
p_timestamp => old.timestamp,
p_status => old.status,
p_temporary => old.temporary,
p_generated => old.generated,
p_secondary => old.secondary
);

if v_message = 'OK' then
ttx_temp_pkg.update_row 
(
x_message => v_message,
p_id => id,--用具体的值代替
p_owner => new.owner, 
p_object_name => new.object_name, 
p_subobject_name => new.subobject_name,
p_object_id => new.object_id, 
p_data_object_id => new.data_object_id,
p_object_type => new.object_type, 
p_created => new.created, 
p_last_ddl_time => new.last_ddl_time, 
p_timestamp => new.timestamp, 
p_status => new.status, 
p_temporary => new.temporary, 
p_generated => new.generated, 
p_secondary => new.secondary 
);

if v_message = 'OK' then
--数据更新成功
else
--数据更新失败 
end if;

else
--锁定行出错，具体信息为:v_message 
end if; 

end;

--Oracle乐观封锁示例：
--前台操作用户通过前台界面查出需要的数据(select t.rowid,t.* from ttx_temp t)，
--然后对对ID=1的这行数据的SUBOBJECT_NAME进行修改提交更新，
--应该使用的程序代码类似为(Delphi可以自已产生，其余的不太清楚)：


update ttx_temp t
set t.subobject_name=new.subobject_name
where t.rowid=_rowid--用具体的值代替
and t.id=old.id
and nvl(t.owner,'"$!')=nvl(old.owner,'"$!')
and nvl(t.object_name,'"$!')=nvl(old.object_name,'"$!')
and nvl(t.object_id,'"$!')=nvl(old.object_id,'"$!')
and nvl(t.data_object_id,'"$!')=nvl(old.data_object_id,'"$!')
and nvl(t.object_type,'"$!')=nvl(old.object_type,'"$!')
and nvl(t.created,'"$!')=nvl(old.created,'"$!')
and nvl(t.last_ddl_time,'"$!')=nvl(old.last_ddl_time,'"$!')
and nvl(t.timestamp,'"$!')=nvl(old.timestamp,'"$!')
and nvl(t.status,'"$!')=nvl(old.status,'"$!')
and nvl(t.temporary,'"$!')=nvl(old.temporary,'"$!')
and nvl(t.generated,'"$!')=nvl(old.generated,'"$!')
and nvl(t.secondary,'"$!')=nvl(old.secondary,'"$!');

--乐观封锁的代码量相对来说少很多，但增大了丢失更新的风险。在实际应用中
--到底是使用悲观封锁还是乐观封锁，由开发人员来定。不过Oracle 11i版的ERP
--如此庞大复杂的系统都使用悲观封锁，没有理由说明乐观封锁优于悲观封锁。

ITPub博客

Oracle悲观封锁和乐观封锁在比较

最新文章