[20151231]主外键与空表(12c).txt

[20151231]主外键与空表(12c).txt

--主外键的测试例子很多，今天做一个特别的，外部键表为空，也会出现阻塞吗？测试看看。

1.建立环境：
SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

create table p ( x int primary key );
create table c ( x references p );
insert into p values ( 1 );
insert into p values ( 2 );
commit;

2.开始测试:
--session 1:
SCOTT@test01p> @spid
       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
        18          3 5860        68          2 alter system kill session '18,3' immediate;

SCOTT@test01p> insert into p values ( 3 );
1 row created.


--session 2:
SCOTT@test01p> @ spid
       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
       374         45 5836        55         20 alter system kill session '374,45' immediate;

SCOTT@test01p> UPDATE p SET x=2 WHERE x=2;
1 row updated.

--没有出现阻塞!!可以发现12c下不会阻塞


SCOTT@test01p> @viewlock
   SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE    MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- ------------------
    18          3 SCOTT      XXX\Admini WORKGROUP\ SQL*Plus     Transaction  Exclusive  None       65544      20677      SYS    JAVA CLASS /c6d93ee4_JobMediaSh No
                             strator    XXX                                                                                                eetsSuppor

   374         45 SCOTT      XXX\Admini WORKGROUP\ SQL*Plus     DML(TM)      Row-X (SX) None       104471     0          SCOTT  TABLE      P                    No
                             strator    XXX

    18          3 SCOTT      XXX\Admini WORKGROUP\ SQL*Plus     DML(TM)      Row-X (SX) None       104471     0          SCOTT  TABLE      P                    No
                             strator    XXX

    18          3 SCOTT      XXX\Admini WORKGROUP\ SQL*Plus     DML(TM)      Row-S (SS) None       104473     0          SCOTT  TABLE      C                    No
                             strator    XXX

   374         45 SCOTT      XXX\Admini WORKGROUP\ SQL*Plus     Transaction  Exclusive  None       393225     21293                                             No
                             strator    XXX

--/c6d93ee4_JobMediaSheetsSuppor 有时候出现有时候不出现,不知道为什么?正常应该出现这样(重复测试)

SCOTT@test01p> @viewlock
   SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE    MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- -------------------
   374         45 SCOTT      xxx\Admini WORKGROUP\ SQL*Plus     DML(TM)      Row-X (SX) None       104471     0          SCOTT  TABLE      P                    No
                             strator    xxx

    18          3 SCOTT      xxx\Admini WORKGROUP\ SQL*Plus     DML(TM)      Row-X (SX) None       104471     0          SCOTT  TABLE      P                    No
                             strator    xxx

    18          3 SCOTT      xxx\Admini WORKGROUP\ SQL*Plus     DML(TM)      Row-S (SS) None       104473     0          SCOTT  TABLE      C                    No
                             strator    xxx

   374         45 SCOTT      xxx\Admini WORKGROUP\ SQL*Plus     Transaction  Exclusive  None       131076     21976                                             No
                             strator    xxx

    18          3 SCOTT      xxx\Admini WORKGROUP\ SQL*Plus     Transaction  Exclusive  None       196610     22264                                             No
                             strator    xxx