oracle中先查询，如果没有记录再插入，如何用事务保证正确性

[infinite]

一个表中有一个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;

[mantisXF]

Merge

[mantisXF]

http://blog.chinaunix.net/u3/107027/showart_2107186.html

[crazylaa]

严重同意楼上用merge into语句。

[huangdh12]

我们公司采用的方法是建一张表 把name字段设置成主键，在插入记录的时候同时往这张表插入主键信息，如果会报错，那么不插入。

[infinite]

谢谢楼上的关于merge的回复，但merge看来是一个高级功能了吧，我只需要一个基本功能。

[lixinbill]

http://blog.chinaunix.net/u3/107027/showart_2107186.html

[crazylaa]

其实merge也就是一个sql语句而已了，帮你加锁而已，从效率上来讲，应该比拆成两条sql用事务要快。。。

呵呵。

[碧水幽幽泉]

是啊！同意使用merge into 效率要高很多！

[zhangwonderful]

merge效率高，同感！

[maitianhust]

使用MERGE INTO语法，它不高级，很基本的，只是用的比较少而已。

[infinite]

首先谢谢各位努力推荐merge 。
不过我其实很需要一种使用事务的解决方案。我前面提到的情况是对于一个表的操作，但是如果是在A表中查询，没有某条记录，则在B表中做某种操作，这时候用merge还行吗？

[didiaodezhu]

友情up

[evilmars]

首先谢谢各位努力推荐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);

[infinite]

evilmars ，你好，你给的代码还是在dest表上做操作吧，这相当于我说的A表，但是我现在想在B表上做操作，如src表，还能办到吗？

[evilmars]

在对表COPY_EMP使用merge语句，根据指定的条件从表
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);

[infinite]

我似乎找到了我要的答案：
在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.

[infinite]

最后发现还是lock好使。
不过不能加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;