oracle中先查询,如果没有记录再插入,如何用事务保证正确性
一个表中有一个name列,需要保证唯一性,一种做法是先根据name查询,没有记录就插入一条记录,有了就不插记录,这串动作想用事务来保证。
(先不考虑用unique约束,因为这是一串很典型的动作。)
目前oracle中有select ... for update ,但对于这个insert的情景似乎不太好使。(我在 oracle10g 上,用PL/SQL Developer单步跟踪过,不好使,会插入2条同名记录)
另外使用 set transaction isolation level serializable ,似乎也要在这个name列有index下的情况下才好使,没有index的话,同样会插入2条同名记录。
我想问问在oracle中如何来支持上面步骤所达到的效果,一般通行的做法是什么?
具体情况,参见下面的PL/SQL代码
--CREATE TABLE tblTran1(id number(10) not null,name varchar2(100) )
--CREATE INDEX idx_tblTran1_name ON tblTran1 ( name );
--drop INDEX idx_tblTran1_name
create or replace package tryTran is
procedure tabl1Insert1(id1 number,name1 varchar2) ;
procedure tabl1Insert2forupdate(id1 number,name1 varchar2) ;
procedure tabl1Insert3settranlvl(id1 number,name1 varchar2) ;
procedure tabl1Update1(name1 varchar2,name2 varchar2);
procedure tabl1Update2selupd(name1 varchar2,name2 varchar2);
procedure tabl1Update3settranlvl(name1 varchar2,name2 varchar2);
end tryTran;
/
create or replace package body tryTran is
procedure tabl1Insert1(id1 number,name1 varchar2) is
id2 number;
begin
begin
SELECT id INTO id2 FROM tblTran1 WHERE name=name1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
INSERT INTO tblTran1( id,name ) VALUES( id1,name1 );
end;
WHEN OTHERS THEN
RAISE;
end;
COMMIT;
end tabl1Insert1;
/*
没用,并行事务会插入两条同name数据,(不管给table的name列加不加index)。
*/
procedure tabl1Insert2forupdate(id1 number,name1 varchar2) is
id2 number;
begin
begin
SELECT id INTO id2 FROM tblTran1 WHERE name=name1 for update;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
INSERT INTO tblTran1( id,name ) VALUES( id1,name1 );
end;
WHEN OTHERS THEN
RAISE;
end;
COMMIT;
end tabl1Insert2forupdate;
/*
给table的name列加index和不加index有区别。不加index,会插入两条同name数据;加了index,一方会报错(ora-08177:can't serialize access for this transaction),也算是有事务保证了。
*/
procedure tabl1Insert3settranlvl(id1 number,name1 varchar2) is
id2 number;
begin
begin
EXECUTE IMMEDIATE 'set transaction isolation level serializable';
SELECT id INTO id2 FROM tblTran1 WHERE name=name1 for update;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
INSERT INTO tblTran1( id,name ) VALUES( id1,name1 );
end;
WHEN OTHERS THEN
RAISE;
end;
COMMIT;
end tabl1Insert3settranlvl;
procedure tabl1Update1(name1 varchar2,name2 varchar2) is
id1 number;
begin
begin
SELECT id INTO id1 FROM tblTran1 WHERE name=name1;
update tblTran1 set name=name2 where id=id1;
commit;
end;
end tabl1Update1;
/*
有效,后面事务被阻塞,出正常结果(不管给table的name列加不加index)。前面事务执行完,改了name之后,后面事务自然是找不到这行记录了。
*/
procedure tabl1Update2selupd(name1 varchar2,name2 varchar2) is
id1 number;
begin
begin
SELECT id INTO id1 FROM tblTran1 WHERE name=name1 for update;
update tblTran1 set name=name2 where id=id1;
commit;
end;
end tabl1Update2selupd;
procedure tabl1Update3settranlvl(name1 varchar2,name2 varchar2) is
id1 number;
begin
begin
EXECUTE IMMEDIATE 'set transaction isolation level serializable';
SELECT id INTO id1 FROM tblTran1 WHERE name=name1 for update;
update tblTran1 set name=name2 where id=id1;
commit;
end;
end tabl1Update3settranlvl;
end tryTran;