oracle 悲观型,Oracle悲观封锁和乐观封锁在比较

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

--如此庞大复杂的系统都使用悲观封锁，没有理由说明乐观封锁优于悲观封锁。