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