[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.
--总之，这种情况真是不得以而为之，问题还在于开发不合理的编程方式。