[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