insert 的enq: TM – contention的情况-1 —–对有主外键关系的表的操作

联系: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

——未完,待续—

此条目发表在 Performence Tuning 分类目录,贴了 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>