外键约束的lock测试，典型的enq: TM -contention等待根因

建表语句

CREATE TABLE DEPT
(
deptno NUMBER CONSTRAINT pk_dept PRIMARY KEY,
dname VARCHAR2(10)
);

CREATE TABLE EMP
(
deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept(deptno),
ename VARCHAR2(20)
);

查询主外键关系，主表DEPT得主键是DEPTNO，子表EMP得外键是deptno,reference主表得deptno.
column owner_name format a10
column table_name format a25
column key_name format a15
column referencing_table format a15
column foreign_key_name format a15

SELECT a.owner owner_name,
a.table_name table_name,
a.constraint_name key_name,
b.table_name referencing_table,
b.constraint_name foreign_key_name
FROM user_constraints a,
user_constraints b
WHERE a.constraint_name = b.r_constraint_name
AND b.constraint_type = 'R'
ORDER BY 1, 2, 3, 4;

OWNER_NAME TABLE_NAME KEY_NAME REFERENCING_TAB FOREIGN_KEY_NAME
---------- --------------------- --------------- --------------- ----------------
SCOTT DEPT PK_DEPT EMP FK_DEPTNO

没有创建索引时
column table_name format a25
SELECT object_id,
object_name
FROM dba_objects
WHERE object_name IN ('EMP','DEPT')
AND owner = 'SCOTT';

OBJECT_ID OBJECT_NAME
---------- -------------------------
13033 DEPT
13035 EMP

查询锁情况
SQL> INSERT INTO DEPT VALUES (1, 'COSTCENTER');
SQL> COMMIT;
SQL> INSERT INTO EMP VALUES (1, 'SCOTT');
SQL> SELECT sid, type, id1, id2,
lmode, request, block
FROM v$lock
WHERE sid IN
(SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID')
);

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
15 TX 589839 56552 6 0 0
15 TM 13035 0 3 0 0
15 TM 13033 0 3 0 0
此时仅仅向子表中插入一条数据，主表和子表都需要 Lmode=3的锁，即row exclusive lock

下面对主表操作DML
SQL> update dept set deptno = 1; <
SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK FROM v$LOCK
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
15 TX 589839 56552 6 0 0
15 TM 13033 0 3 0 0
操作主表时，主表有锁，而子表没有锁。

外键有索引时，操作子表
SQL> create index ind_emp on emp (deptno, ename);
SQL> insert into DEPT values (1, 'COSTCENTER');
SQL> commit;
SQL> insert into EMP values (1, 'SCOTT');

SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK FROM v$LOCK
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
15 TX 393232 54853 6 0 0
15 TM 13035 0 3 0 0
15 TM 13033 0 2 0 0
此时主表持有Row share lock LMODE=2，而子表持有row exclusive lock

DML子表
SQL> update DEPT set deptno = 1;

1 row updated.

SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK FROM v$LOCK
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
15 TX 589839 56552 6 0 0
15 TM 13035 0 2 0 0
15 TM 13033 0 3 0 0

此时主表row exclusive lock (LMODE=3) 子表持有 row share lock LMODE=2

所以需要再子表的外键创建索引一防止enq:TM-contention的等待,这是一个队列，必须排队，造成SQL的缓慢感觉是夯住了。