[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.

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