[20160719]主外键与延迟约束2.txt

--前几天遇到的问题,因为开发没有在2个存在主外键上的表上建立约束,导致主表记录删除了,而外表数据还在.

--主要开发有需求要删除主表的记录,由于条件写错,导致以上情况出现.实际上oracle支持延迟约束,只有提交的时候才会检查。 --自己通过例子说明:

1.环境:

SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立测试环境:

create table p ( x int primary key );

create table c ( x int ); insert into p values ( 1 ); insert into p values ( 2 ); insert into c values ( 1 ); commit; create index i_c_x on scott.c(X);

3.建立延迟约束：

-- alter table c drop  constraint fk_c ;

SCOTT@book> alter table c add ( constraint fk_c foreign key (x) references p (x) deferrable initially deferred enable validate);

Table altered.

SCOTT@book> delete from p where x=1;

1 row deleted.

SCOTT@book> commit ;

commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-02292: integrity constraint (SCOTT.FK_C) violated - child record found

--这样在提交时才报错,而不是在执行时.

4.继续测试:

--session 1:

SCOTT@book(46,7)> insert into c values ( 3 ); 1 row created.

--session 2:

SCOTT@book(245,5)> insert into p values (3); 1 row created.

SCOTT@book(245,5)> commit ;

Commit complete.

--//ok,一切正常。回到session 1：

--session 1: SCOTT@book(46,7)> commit ; commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-02291: integrity constraint (SCOTT.FK_C) violated - parent key not found

--说明仅仅在一个事务里面实现完整性。但是如果我再次执行：

--session 1: SCOTT@book(46,7)> insert into c values ( 3 ); 1 row created.

SCOTT@book(46,7)> commit ;

Commit complete.

--但是如果你反过来操作不提交： --session 2: SCOTT@book(245,5)> insert into p values (4); 1 row created.

--session 1:

SCOTT@book(46,7)> insert into c values ( 4 );

--//会话1挂起.

SCOTT@book> @ &r/viewlock

   SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT

------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- ----------- ----- --------------------     46          7 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  None       Share      655363     51533                                    No    00000000851E7868     46          7 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  Exclusive  None       589832     13834                                    No    00000000851E7868     46          7 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       95292      0          SCOTT  TABLE      C           No    00000000851E7868     46          7 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       95290      0          SCOTT  TABLE      P           No    00000000851E7868    245          5 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  Exclusive  None       655363     51533                                    Yes    245          5 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       95290      0          SCOTT  TABLE      P           No    245          5 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       95292      0          SCOTT  TABLE      C           No 7 rows selected.

--只有session 2提交，阻塞才消失。

SCOTT@book(245,5)> commit;

Commit complete.

--总之，这种情况真是不得以而为之，问题还在于开发不合理的编程方式。