联系：QQ(5163721)
标题：insert 的enq: TM – contention的情况-1 —–对有主外键关系的表的操作
作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
insert 的enq: TM – contention的情况-1 —–对有主外键关系的表的操作
insert 的enq: TM – contention的情况-2 —–只有主键约束无子表的情况
####################################################################################
总结1 ：当外键无索引时
1，对子表的insert操作所在的事务没有完成前，对于父表的DML操作（INSERT/UPDATE/DELETE）都会因为不能获得对子表的TM锁而出现enq: TM – contention。
2，在总结1的基础上，如果又有了对子表的insert（我没有测试，但是我怀疑这里如果改成对子表的update和delete，也是相同道理……）.
那么这个对子表的insert同样被阻塞，等待事件也是 enq: TM – contention。
3，对父表的insert会阻塞对父表的delete。同样的道理，此时对父表的insert也阻塞对父表的update pk操作。
####################################################################################
SQL> conn lunar/lunar
Connected.
SQL> CREATE TABLE DEPT
2 (
3 deptno NUMBER CONSTRAINT pk_dept PRIMARY KEY,
4 dname VARCHAR2(10)
5 );
CREATE TABLE EMP
(
deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept(deptno),
ename VARCHAR2(20)
);
Table created.
SQL> SQL> 2 3 4 5
Table created.
SQL>
SQL> select index_name from dba_indexes where OWNER='LUNAR';
INDEX_NAME
------------------------------
PK_DEPT
Elapsed: 00:00:00.21
SQL>
SQL> insert into DEPT values (1, 'COSTCENTER');
1 row created.
SQL> insert into EMP values (1, 'SCOTT');
1 row created.
SQL>
SQL> set linesize 200
SQL> set pages 999
SQL> set timing on
SQL> /
OWNER_NAME TABLE_NAME KEY_NAME REFERENCING_TAB FOREIGN_KEY_NAM
---------- ------------------------- --------------- --------------- ---------------
LUNAR DEPT PK_DEPT EMP FK_DEPTNO
Elapsed: 00:00:01.07
SQL>
SQL> column table_name format a25
SQL> SELECT object_id,
2 object_name
FROM dba_objects
3 4 WHERE object_name IN ('EMP','DEPT')
5 AND owner = 'LUNAR';
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
1062788 DEPT
1062790 EMP
Elapsed: 00:00:00.08
SQL>
#########################################################
当没有索引的时候：
#########################################################
测试1, 在子表发生：Insert，然后在父表上有update操作
SQL> rollback; Rollback complete. Elapsed: 00:00:00.00 SQL> select * from dept; no rows selected Elapsed: 00:00:00.02 SQL> select * from emp; no rows selected Elapsed: 00:00:00.01 SQL>
Session 1: 对子表进行insert，不commit时：
SQL> INSERT INTO DEPT VALUES (1, 'COSTCENTER');
1 row created.
Elapsed: 00:00:00.00
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> INSERT INTO EMP VALUES (1, 'SCOTT');
1 row created.
Elapsed: 00:00:00.00
SQL> SELECT sid, type, id1, id2,
2 lmode, request, block
3 FROM v$lock
4 WHERE sid IN
5 (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID') );
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
321 AE 884349 0 4 0 0
321 TM 1062790 0 3 0 0
321 TM 1062788 0 3 0 0
321 TX 65546 71036 6 0 0
Elapsed: 00:00:00.55
SQL> SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID');
SID
----------
321
Elapsed: 00:00:00.01
SQL>
这是，我们看到，该回话在主表（1062788 DEPT）和子表（1062790 EMP）上都分别持有了 exclusive lock(LMODE=3)锁
同时，该回话还持有一个tx锁（在子表 emp上）
另开一个会话，观察锁：
SQL> select substr(type,1,2) type,substr(name,1,30) name ,substr(description,1,40) description 2 from v$lock_type 3 where substr(type,1,2)='AE' 4 ; TYPE NAME -------- ------------------------------------------------------------------------------------------------------------------------ DESCRIPTION ---------------------------------------------------------------------------------------------------------------------------------------------------------------- AE Edition Lock Prevent Dropping an edition in use Elapsed: 00:00:00.01 SQL>
Session 2 : 然后，我们在父表做DML操作update:
SQL> SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID');
SID
----------
220
SQL> update dept set deptno = 1;
...hang...
Session 3: 检查锁的变化
SELECT sid, type, id1, id2, lmode, request, block
FROM v$lock
WHERE sid IN (220,321)
ORDER BY SID;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
220 TM 1062790 0 0 4 0
220 TM 1062788 0 3 0 0
220 AE 884349 0 4 0 0
321 TX 65546 71036 6 0 0
321 TM 1062790 0 3 0 1 --------blocker
321 TM 1062788 0 3 0 0
321 AE 884349 0 4 0 0
SPID SID SERIAL# STATUS
------------------------ ---------- ---------- --------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID EVENT STATUS
------------- ---------------------------------------------------------------- --------
12456 220 20401 ACTIVE
update dept set deptno = 1
4xjxb3twbayrj enq: TM - contention ACTIVE
SQL> select * from v$lock where block=1;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F6C85314EE0 00007F6C85314F40 321 TM 1062790 0 3 0 253 1
Elapsed: 00:00:00.07
SQL>
这是我们看到，Session 2 在子表（1062790 EMP）上请求LMODE=4，并在主表（1062788 DEPT）上持有等待：enq: TM – contention
这是很清晰，有blocker了，也就是Session 1（SID 321），此时该回话正在对子表（1062790 EMP）进行插入操作
##################################
换句话说，当外键无索引时，对子表的insert操作，会造成对父表的更新操作的阻塞。
##################################
Session 4 更新父表：
SQL> SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID');
SID
----------
116
SQL> update dept set deptno = 1 where deptno = 2;
.....HANG.....
Session 3： 观察
SELECT sid, type, id1, id2, lmode, request, block
FROM v$lock
WHERE sid IN (220,321,116) AND TYPE!='AE'
ORDER BY SID,type,ID1;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
116 TM 1062788 0 3 0 0
116 TM 1062790 0 0 4 0
220 TM 1062788 0 3 0 0
220 TM 1062790 0 0 4 0
321 TM 1062788 0 3 0 0
321 TM 1062790 0 3 0 1
321 TX 65546 71036 6 0 0
SPID SID SERIAL# STATUS
------------------------ ---------- ---------- --------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID EVENT STATUS
------------- ---------------------------------------------------------------- --------
12602 116 64289 ACTIVE
update dept set deptno = 1 where deptno = 2
2ha4z6qzxngj1 enq: TM - contention ACTIVE
12456 220 20401 ACTIVE
update dept set deptno = 1
4xjxb3twbayrj enq: TM - contention ACTIVE
SQL> select * from v$lock where block=1;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F6C85314EE0 00007F6C85314F40 321 TM 1062790 0 3 0 386 1
这是我们看到，session 4（sid 116）遵循了跟session 220同样的所规律
这是我们在Session 1中执行commit，在Session 3中观察：
Session 1： SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL>
可以看到Session 2和Session 4都完成了update：
Session 2：
SQL> SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID');
SID
----------
220
SQL> update dept set deptno = 1;
1 row updated.
SQL>
Session 4:
SQL> SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID');
SID
----------
116
SQL> update dept set deptno = 1 where deptno = 2;
0 rows updated.
SQL>
Session 3：
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
116 TM 1062788 0 3 0 0 -------session 4获得了父表的update加LMODE=3的TM锁
220 TM 1062788 0 3 0 0 -------session 2也获得了父表的update加LMODE=3的TM锁
220 TX 1048603 29682 6 0 0
此时的等待事件：
SQL> select sid,event from v$session_wait where sid in (220,321,116);
SID EVENT
---------- ----------------------------------------------------------------
116 SQL*Net message from client
220 SQL*Net message from client
321 SQL*Net message from client
Elapsed: 00:00:00.00
SQL>
这里我们看到，当外键无索引时，对子表的insert操作，会造成对父表的更新操作的阻塞，该阻塞直到子表的insert事务结束，才自动释放。
测试2, 在子表发生：Insert，然后在父表上有update操作，然后再有一个会话对子表做insert
重复上面的操作，不对session 1进行commit时，再开一个窗口（Session 5）对子表做insert
SQL> conn lunar/lunar
Connected.
SQL> SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID');
SID
----------
222
SQL> INSERT INTO EMP VALUES (2,'LUNAR');
。。。。。hang。。。。。。。
这是我们看到，对于子表的insert语句被阻塞了。
在Session 3中观察:
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
116 TM 1062788 0 3 0 0
116 TM 1062790 0 0 4 0
220 TM 1062788 0 3 0 0
220 TM 1062790 0 0 4 0
222 TM 1062788 0 3 0 0
222 TM 1062790 0 0 3 0 --------session 220请求以LMODE=3的模式对子表加TM锁
321 TM 1062788 0 3 0 0
321 TM 1062790 0 3 0 1
321 TX 1310752 43053 6 0 0
SPID SID SERIAL# STATUS
------------------------ ---------- ---------- --------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID EVENT STATUS
------------- ---------------------------------------------------------------- --------
12602 116 64289 ACTIVE
update dept set deptno = 1 where deptno = 2
2ha4z6qzxngj1 enq: TM - contention ACTIVE
12456 220 20401 ACTIVE
update dept set deptno = 1
4xjxb3twbayrj enq: TM - contention ACTIVE
12766 222 49879 ACTIVE
INSERT INTO EMP VALUES (2,'LUNAR')
83wzmhc9kz5dp enq: TM - contention ACTIVE
这里我们看到：
1，当外键无索引时，对子表的insert操作，会造成对父表的更新操作的阻塞，该阻塞直到子表的insert事务结束，才自动释放。
2，如果对子表有insert操作，在这个insert所在事务没有commit之前，有对父表的update，那么接下来的对子表的inser会被阻塞
测试3：
在测试2的场景上，将session 2和session 4中的对父表的update执行Ctrl+C，然后分别改为INSERT 和delete 父表（dept）的操作，观察一下
Session 4：
SQL> update dept set deptno = 1 where deptno = 2; ^Cupdate dept set deptno = 1 where deptno = 2 * ERROR at line 1: ORA-01013: user requested cancel of current operation SQL> Session 2： SQL> update dept set deptno = 1; ^Cupdate dept set deptno = 1 * ERROR at line 1: ORA-01013: user requested cancel of current operation
Session 5 随之自动解锁，并报了正常的违反约束的错误：
SQL> SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID');
SID
----------
222
SQL> INSERT INTO EMP VALUES (2,'LUNAR');
INSERT INTO EMP VALUES (2,'LUNAR')
*
ERROR at line 1:
ORA-02291: integrity constraint (LUNAR.FK_DEPTNO) violated - parent key not
found
SQL>
在session 2中执行delete 父表的操作：
SQL> SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID');
SID
----------
220
SQL> delete from dept where deptno = 1;
..... HANG......
在session 3中观察：
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
220 TM 1062788 0 3 0 0
220 TM 1062790 0 0 4 0
321 TM 1062788 0 3 0 0
321 TM 1062790 0 3 0 1
321 TX 1310752 43053 6 0 0
SPID SID SERIAL# STATUS
------------------------ ---------- ---------- --------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID EVENT STATUS
------------- ---------------------------------------------------------------- --------
12456 220 20401 ACTIVE
delete from dept where deptno = 1
56m0bvac2yfkm enq: TM - contention ACTIVE
12766 222 49879 INACTIVE
INSERT INTO EMP VALUES (2,'LUNAR')
83wzmhc9kz5dp SQL*Net message from client INACTIVE
注意：
1，delete父表的操作被阻塞，依然是正在请求以LMODE=4的模式对子表（1062790 EMP）的TM锁
2，这里的sid 222中为非活动会话，当该回话执行了commit或者rollback后，这个Insert的“SQL*Net message from client”等待就会消失了。
测试4： 在测试3的场景上，继续session 4中的对父表做insert 的操作，观察一下
Session 4：
SQL> SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID');
SID
----------
116
SQL> INSERT INTO DEPT VALUES (2,'LUNAR');
。。。。。hang 。。。。。。。。。。
在Session 3观察：
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
116 TM 1062788 0 3 0 0
116 TM 1062790 0 0 3 0
220 TM 1062788 0 3 0 0
220 TM 1062790 0 0 4 0
321 TM 1062788 0 3 0 0
321 TM 1062790 0 3 0 1
321 TX 1310752 43053 6 0 0
SPID SID SERIAL# STATUS
------------------------ ---------- ---------- --------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID EVENT STATUS
------------- ---------------------------------------------------------------- --------
12602 116 64289 ACTIVE
INSERT INTO DEPT VALUES (2,'LUNAR')
g2dc3zwjr19hf enq: TM - contention ACTIVE
12456 220 20401 ACTIVE
delete from dept where deptno = 1
56m0bvac2yfkm enq: TM - contention ACTIVE
这里我们看到，虽然Session 4执行的是对父表的insert，session 2执行的是对父表的 delete，他们的表现跟update的表现都是相同的。
也就是说，对于子表的insert操作所在的事务没有完成前，对于父表的DML操作（INSERT/UPDATE/DELETE）都会因为不能获得对子表的TM锁而出现enq: TM – contention
测试5： 在测试4的基础上，我们在Session 1对做commit，完成对子表的insert操作，然后观察其他session：
Session 1：
SQL> INSERT INTO EMP VALUES (1, 'SCOTT'); 1 row created. Elapsed: 00:00:00.00 SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL>
Session 4：对父表的insert插入操作随着session 1的commit 自动解锁：
SQL> SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID');
SID
----------
116
SQL> INSERT INTO DEPT VALUES (2,'LUNAR');
1 row created.
SQL>
Session 2（SID 220）：对父表的delete操作依然是hang的状态：
SQL> delete from dept where deptno = 1;
...HANG ...
在session 3观察：
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
116 TM 1062788 0 3 0 0
116 TM 1062790 0 3 0 1 ----------session 4（sid 116）是阻塞者了
116 TX 1310742 42713 6 0 0
220 TM 1062788 0 3 0 0 ----------session 2依然是刚才的状态
220 TM 1062790 0 0 4 0
220 TX 65567 71014 6 0 0
SPID SID SERIAL# STATUS
------------------------ ---------- ---------- --------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID EVENT STATUS
------------- ---------------------------------------------------------------- --------
12456 220 20401 ACTIVE
delete from dept where deptno = 1
56m0bvac2yfkm enq: TM - contention ACTIVE
也就是说，这里我们看到了，对父表的insert，阻塞了对父表的delete
——未完，待续—
