[20160719]主外鍵與延遲限制2.txt

[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);

SCOTT@book(245,5)> commit ;

Commit complete.

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

SCOTT@book(46,7)> commit ;

ORA-02291: integrity constraint (SCOTT.FK_C) violated - parent key not found

--說明僅僅在一個事務裡面實作完整性。但是如果我再次執行：

--但是如果你反過來操作不送出：

SCOTT@book(245,5)> insert into p values (4);

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;

--總之，這種情況真是不得以而為之，問題還在于開發不合理的程式設計方式。