一个表中有一个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_namecreate 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;
(先不考虑用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_namecreate 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;
不过我其实很需要一种使用事务的解决方案。我前面提到的情况是对于一个表的操作,但是如果是在A表中查询,没有某条记录,则在B表中做某种操作,这时候用merge还行吗?
不过我其实很需要一种使用事务的解决方案。我前面提到的情况是对于一个表的操作,但是如果是在A表中查询,没有某条记录,则在B表中做某种操作,这时候用merge还行吗?
=====================================================================================
我看行!MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
EMPLOYEES中插入或更新数据。
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
...
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);
在oracle的文档中的 Application Developer's Guide - Fundamentals (Oracle® Database Application Developer's Guide - Fundamentals 10g Release 2 (10.2))中的 2 SQL Processing for Application Developers 有关于 Referential Integrity and Serializable Transactions 的说明。
Because Oracle Database does not use read locks, even in SERIALIZABLE transactions, data read by one transaction can be overwritten by another. Transactions that perform database consistency checks at the application level should not assume that the data they read will not change during the execution of the transaction (even though such changes are not visible to the transaction). Database inconsistencies can result unless such application-level consistency checks are coded carefully, even when using SERIALIZABLE transactions. Note, however, that the examples shown in this section are applicable for both READ COMMITTED and SERIALIZABLE transactions.Figure 2-2 shows two different transactions that perform application-level checks to maintain the referential integrity parent/child relationship between two tables. One transaction checks that a row with a specific primary key value exists in the parent table before inserting corresponding child rows. The other transaction checks to see that no corresponding detail rows exist before deleting a parent row. In this case, both transactions assume (but do not ensure) that data they read will not change before the transaction completes.Figure 2-2 Referential Integrity Check
The read issued by transaction A does not prevent transaction B from deleting the parent row, and transaction B's query for child rows does not prevent transaction A from inserting child rows. This scenario leaves a child row in the database with no corresponding parent row. This result occurs even if both A and B are SERIALIZABLE transactions, because neither transaction prevents the other from making changes in the data it reads to check consistency.As this example shows, sometimes you must take steps to ensure that the data read by one transaction is not concurrently written by another. This requires a greater degree of transaction isolation than defined by SQL92 SERIALIZABLE mode.
不过不能加EXECUTE IMMEDIATE 'set transaction isolation level serializable';否则不灵。 procedure tabl1Insert5_lock(id1 number,name1 varchar2) is
id2 number;
begin
begin
--EXECUTE IMMEDIATE 'LOCK TABLE tblTran1 IN EXCLUSIVE MODE '; --ok
EXECUTE IMMEDIATE 'LOCK TABLE tblTran1 IN SHARE ROW EXCLUSIVE MODE'; -- ok
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;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
end tabl1Insert5_lock;