[20151231]主外键与空表.txt

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

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

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@book> @ &r/spid
       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
       232       2365 31115       21        242 alter system kill session '232,2365' immediate;

insert into p values ( 3 );

--session 2:
SCOTT@book> @ &r/spid

       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
        90       6817 31147       32        155 alter system kill session '90,6817' immediate;

UPDATE p SET x=2 WHERE x=2; 
--挂起!

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
---- ---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ---------- ---------- ------ ---------- ------------ ----- --------------------
  90       6817 SCOTT      oracle     gxqyydg4   SQL*Plus     DML(TM)      None       Share      89269      0          SCOTT  TABLE      C            No    00000000827517E0
232       2365 SCOTT      oracle     gxqyydg4   SQL*Plus     DML(TM)      Row-X (SX) None       89269      0          SCOTT  TABLE      C            Yes
  90       6817 SCOTT      oracle     gxqyydg4   SQL*Plus     DML(TM)      Row-X (SX) None       89267      0          SCOTT  TABLE      P            No    00000000827517E0
232       2365 SCOTT      oracle     gxqyydg4   SQL*Plus     DML(TM)      Row-X (SX) None       89267      0          SCOTT  TABLE      P            No
232       2365 SCOTT      oracle     gxqyydg4   SQL*Plus     Transaction  Exclusive  None       655360     4982                                      No

SCOTT@book> @ &r/wait
P1RAW            P2RAW            P3RAW                    P1         P2         P3    SID    SERIAL#       SEQ# EVENT                                    STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ------ ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00               1650815232          1          0    102       3369         36 SQL*Net message to client                WAITED SHORT TIME                 3               0
00000000544D0004 0000000000015CB5 00               1414332420      89269          0     90       6817         29 enq: TM - contention                     WAITING                    92302382              92

--据说这种情况在12c下不会出现问题。回家测试看看。

3.建立索引看看问题是否消失：

SCOTT@book> create index i_c_x on c(x);
Index created.

--重复测试：
--session 1:
SCOTT@book> insert into p values ( 3 );
1 row created.

--session 2:
SCOTT@book> UPDATE p SET x=2 WHERE x=2;
1 row updated.

--没有出现阻塞！
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
------ ---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
    90       6817 SCOTT      oracle     gxqyydg4   SQL*Plus     DML(TM)      Row-X (SX) None       89269      0          SCOTT  TABLE      C                    No
   232       2365 SCOTT      oracle     gxqyydg4   SQL*Plus     DML(TM)      Row-X (SX) None       89269      0          SCOTT  TABLE      C                    No
    90       6817 SCOTT      oracle     gxqyydg4   SQL*Plus     DML(TM)      Row-X (SX) None       89267      0          SCOTT  TABLE      P                    No
   232       2365 SCOTT      oracle     gxqyydg4   SQL*Plus     DML(TM)      Row-X (SX) None       89267      0          SCOTT  TABLE      P                    No
    90       6817 SCOTT      oracle     gxqyydg4   SQL*Plus     Transaction  Exclusive  None       589825     1478                                              No
   232       2365 SCOTT      oracle     gxqyydg4   SQL*Plus     Transaction  Exclusive  None       655378     4989                                              No
6 rows selected.

# cat viewlock.sql
column sid format 99999
column username format a10
column osuser format a10
column machine format a10
column lock_type format a12
column mode_held format a10
column mode_requested format a10
column lock_id1 format a10
column lock_id2 format a10
column owner format a6
column object_type format a10
column object_name format a20
column block format a5
column lockwait format a20

SELECT se.SID, se.serial#,se.username, se.osuser, se.machine,se.module,
       DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML(TM)', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
       DECODE (lk.lmode,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (lk.lmode)
              ) mode_held,
       DECODE (lk.request,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (lk.request)
              ) mode_requested,
       TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner, ob.object_type, ob.object_name,
       DECODE (lk.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, se.lockwait
  FROM v$lock lk, dba_objects ob, v$session se
WHERE lk.TYPE IN ('TX','TM', 'UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id(+);