oracle中,在串行化事务中对于一个cluster的表插入一条数据再立即select,经常第一次出错,第二次正常,为何?
在下面的语句中,存储过程 tbl3Insert 与 tbl3Insert0cmp_tranlvl 的区别在于一条set transaction isolation level serializable语句。但是执行效果就不一样。在tbl3Insert0cmp_tranlvl的第二个select语句经常是第一次出错,第二次正常,不知何故。望高人解答。
另外,如果是普通表,则没有问题。如下是cluster表结构和代码:
CREATE SEQUENCE tblTran3_SEQ START WITH 1 INCREMENT BY 1 nocache order;
CREATE CLUSTER CL_tblTran3 (idcol number(10)) ;
CREATE INDEX CI_tblTran3 on CLUSTER CL_tblTran3 ;
CREATE TABLE tblTran3(idcol number(10) not null,namecol varchar2(100) ) CLUSTER CL_tblTran3 (idcol) ;
create or replace package tryTran3 is
procedure tbl3Insert(name1 varchar2,o_id out number) ;
procedure tbl3Insert0cmp_tranlvl(name1 varchar2,o_id out number) ;
end tryTran3;/create or replace package body tryTran3 is
procedure tbl3Insert(name1 varchar2,o_id out number) is
begin
begin
SELECT idcol INTO o_id FROM tblTran3 WHERE namecol=name1 for update;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
INSERT INTO tblTran3( idcol,namecol ) VALUES( tblTran3_SEQ.NEXTVAL,name1 );
SELECT idcol INTO o_id FROM tblTran3 WHERE namecol=name1;
end;
WHEN OTHERS THEN
RAISE;
end;
COMMIT;
end tbl3Insert;
/*
compare with tbl3Insert0cmp_tranlvl, only add 'set transaction isolation level serializable', exception as below would occur at the second select sql sentence.
ORA-06502: PL/SQL: numeric or value error
(ORA-01403: no data found)
*/
procedure tbl3Insert0cmp_tranlvl(name1 varchar2,o_id out number) is
begin
begin
EXECUTE IMMEDIATE 'set transaction isolation level serializable';
SELECT idcol INTO o_id FROM tblTran3 WHERE namecol=name1 for update;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
INSERT INTO tblTran3( idcol,namecol ) VALUES( tblTran3_SEQ.NEXTVAL,name1 );
SELECT idcol INTO o_id FROM tblTran3 WHERE namecol=name1;
end;
WHEN OTHERS THEN
RAISE;
end;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
end tbl3Insert0cmp_tranlvl;
end tryTran3;另外,普通表结构和代码也放在这里以作参考
CREATE SEQUENCE tblTran2_SEQ START WITH 1 INCREMENT BY 1 ;
CREATE TABLE tblTran2(idcol number(10) not null,namecol varchar2(100) );
CREATE INDEX idx_tblTran2_id ON tblTran2 ( idcol );create or replace package tryTran2 is
procedure tbl2Insert(name1 varchar2,o_id out number) ;
procedure tbl2Insert0cmp_tranlvl(name1 varchar2,o_id out number) ;
end tryTran2;/create or replace package body tryTran2 is
procedure tbl2Insert(name1 varchar2,o_id out number) is
begin
begin
SELECT idcol INTO o_id FROM tblTran2 WHERE namecol=name1 for update;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
INSERT INTO tblTran2( idcol,namecol ) VALUES( tblTran2_SEQ.NEXTVAL,name1 );
SELECT idcol INTO o_id FROM tblTran2 WHERE namecol=name1;
end;
WHEN OTHERS THEN
RAISE;
end;
COMMIT;
end tbl2Insert;
procedure tbl2Insert0cmp_tranlvl(name1 varchar2,o_id out number) is
begin
begin
EXECUTE IMMEDIATE 'set transaction isolation level serializable';
SELECT idcol INTO o_id FROM tblTran2 WHERE namecol=name1 for update;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
INSERT INTO tblTran2( idcol,namecol ) VALUES( tblTran2_SEQ.NEXTVAL,name1 );
SELECT idcol INTO o_id FROM tblTran2 WHERE namecol=name1;
end;
WHEN OTHERS THEN
RAISE;
end;
COMMIT;
end tbl2Insert0cmp_tranlvl;end tryTran2;
在下面的语句中,存储过程 tbl3Insert 与 tbl3Insert0cmp_tranlvl 的区别在于一条set transaction isolation level serializable语句。但是执行效果就不一样。在tbl3Insert0cmp_tranlvl的第二个select语句经常是第一次出错,第二次正常,不知何故。望高人解答。
另外,如果是普通表,则没有问题。如下是cluster表结构和代码:
CREATE SEQUENCE tblTran3_SEQ START WITH 1 INCREMENT BY 1 nocache order;
CREATE CLUSTER CL_tblTran3 (idcol number(10)) ;
CREATE INDEX CI_tblTran3 on CLUSTER CL_tblTran3 ;
CREATE TABLE tblTran3(idcol number(10) not null,namecol varchar2(100) ) CLUSTER CL_tblTran3 (idcol) ;
create or replace package tryTran3 is
procedure tbl3Insert(name1 varchar2,o_id out number) ;
procedure tbl3Insert0cmp_tranlvl(name1 varchar2,o_id out number) ;
end tryTran3;/create or replace package body tryTran3 is
procedure tbl3Insert(name1 varchar2,o_id out number) is
begin
begin
SELECT idcol INTO o_id FROM tblTran3 WHERE namecol=name1 for update;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
INSERT INTO tblTran3( idcol,namecol ) VALUES( tblTran3_SEQ.NEXTVAL,name1 );
SELECT idcol INTO o_id FROM tblTran3 WHERE namecol=name1;
end;
WHEN OTHERS THEN
RAISE;
end;
COMMIT;
end tbl3Insert;
/*
compare with tbl3Insert0cmp_tranlvl, only add 'set transaction isolation level serializable', exception as below would occur at the second select sql sentence.
ORA-06502: PL/SQL: numeric or value error
(ORA-01403: no data found)
*/
procedure tbl3Insert0cmp_tranlvl(name1 varchar2,o_id out number) is
begin
begin
EXECUTE IMMEDIATE 'set transaction isolation level serializable';
SELECT idcol INTO o_id FROM tblTran3 WHERE namecol=name1 for update;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
INSERT INTO tblTran3( idcol,namecol ) VALUES( tblTran3_SEQ.NEXTVAL,name1 );
SELECT idcol INTO o_id FROM tblTran3 WHERE namecol=name1;
end;
WHEN OTHERS THEN
RAISE;
end;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
end tbl3Insert0cmp_tranlvl;
end tryTran3;另外,普通表结构和代码也放在这里以作参考
CREATE SEQUENCE tblTran2_SEQ START WITH 1 INCREMENT BY 1 ;
CREATE TABLE tblTran2(idcol number(10) not null,namecol varchar2(100) );
CREATE INDEX idx_tblTran2_id ON tblTran2 ( idcol );create or replace package tryTran2 is
procedure tbl2Insert(name1 varchar2,o_id out number) ;
procedure tbl2Insert0cmp_tranlvl(name1 varchar2,o_id out number) ;
end tryTran2;/create or replace package body tryTran2 is
procedure tbl2Insert(name1 varchar2,o_id out number) is
begin
begin
SELECT idcol INTO o_id FROM tblTran2 WHERE namecol=name1 for update;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
INSERT INTO tblTran2( idcol,namecol ) VALUES( tblTran2_SEQ.NEXTVAL,name1 );
SELECT idcol INTO o_id FROM tblTran2 WHERE namecol=name1;
end;
WHEN OTHERS THEN
RAISE;
end;
COMMIT;
end tbl2Insert;
procedure tbl2Insert0cmp_tranlvl(name1 varchar2,o_id out number) is
begin
begin
EXECUTE IMMEDIATE 'set transaction isolation level serializable';
SELECT idcol INTO o_id FROM tblTran2 WHERE namecol=name1 for update;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
INSERT INTO tblTran2( idcol,namecol ) VALUES( tblTran2_SEQ.NEXTVAL,name1 );
SELECT idcol INTO o_id FROM tblTran2 WHERE namecol=name1;
end;
WHEN OTHERS THEN
RAISE;
end;
COMMIT;
end tbl2Insert0cmp_tranlvl;end tryTran2;
这个select into select出来的是个number,into的那个也是个number,应该不会出错
要不你在存储过程前面,as后面显式定义一个变量 var_no TA_CATEGORIES.CATEGORY_ID%TYPE;
保证它的类型和长度与select 里面的一致,
然后再 select CATEGORY_ID into var_no .....
下面再 out参数=var_no ,看看这样子会不会出错?