sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle dml操作产生TM锁 lmode=6 分析原因和解决办法

时间:2016-12-04 20:22   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库出现异常锁,dml操作产生TM锁 而且lmode是6,针对parallel dml 会产生TM lmode 为6的lock。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: dml with lmode=6

Oracle dml操作产生TM锁,而且lmode是6.我们都知道正常情况下,dml产生的tm lmode应该是2或3,当然老版本的db有一些差异,详见下文:

SQL> SHOW USER
USER IS "ROGER"

SQL> CREATE TABLE DEPT  (deptno NUMBER CONSTRAINT pk_dept PRIMARY KEY,dname varchar2(10));
TABLE created.

SQL> CREATE TABLE EMP  (deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept(deptno), ename varchar2(20));
TABLE created.

SQL> INSERT INTO DEPT VALUES (1, 'killdb1');
1 ROW created.

SQL> INSERT INTO EMP VALUES (1, 'killdb2');
1 ROW created.

SQL> commit;
Commit complete.

SQL> INSERT INTO DEPT VALUES (2, 'killdb3');
1 ROW created.

SQL> INSERT INTO EMP VALUES (2, 'killdb4');
1 ROW created.

SQL> commit;
Commit complete.

SQL> COLUMN owner_name         format a10
SQL> COLUMN TABLE_NAME         format a25
SQL> COLUMN key_name           format a15
SQL> COLUMN referencing_table  format a15
SQL> COLUMN foreign_key_name   format a15

SQL> SELECT 
  2      A.OWNER owner_name,
  3      A.TABLE_NAME TABLE_NAME,
  4      A.CONSTRAINT_NAME key_name,
  5      B.TABLE_NAME referencing_table,
  6      B.CONSTRAINT_NAME foreign_key_name
  7    FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B 
  8    WHERE
  9      A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME AND
10      B.CONSTRAINT_TYPE = 'R'
11      AND a.TABLE_NAME IN('DEPT','EMP')
12    ORDER BY 1, 2, 3, 4;

OWNER_NAME TABLE_NAME                KEY_NAME        REFERENCING_TAB FOREIGN_KEY_NAM
---------- ------------------------- --------------- --------------- ---------------
ROGER      DEPT                      PK_DEPT         EMP             FK_DEPTNO
——– no index test

SQL> col object_name FOR a20
SQL> SELECT object_id, object_name
  2    FROM dba_objects
  3   WHERE object_name IN ('EMP', 'DEPT')
  4     AND owner = 'ROGER';

OBJECT_ID OBJECT_NAME
---------- --------------------
     56005 DEPT
     56007 EMP

---insert

SQL> INSERT INTO DEPT VALUES (3, 'oracleplus.net');
1 ROW created.

SQL> commit
Commit complete.

SQL> INSERT INTO EMP VALUES (3, 'oracleplus.net');
1 ROW created.  -----no commit

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
  3   WHERE sid IN
  4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      56005          0          2          0          0
       138 TM      56007          0          3          0          0
       138 TX     786441        245          6          0          0


---update

SQL> UPDATE dept SET dname ='killdb2' WHERE deptno=1;
1 ROW updated.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
  3   WHERE sid IN
  4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      56005          0          3          0          0
       138 TX     983049        254          6          0          0

SQL> ROLLBACK;
ROLLBACK complete.

SQL> INSERT INTO DEPT VALUES (4, 'baidu.com');
1 ROW created.

SQL> INSERT INTO EMP VALUES (4, 'google.cn');
1 ROW created.

SQL> UPDATE dept SET dname ='killdb2' WHERE deptno=1;
1 ROW updated.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
  3   WHERE sid IN
  4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      56005          0          3          0          0
       138 TM      56007          0          3          0          0
       138 TX     851977        239          6          0          0


---delete

SQL> ROLLBACK;
ROLLBACK complete.

SQL> SELECT * FROM dept;

    DEPTNO DNAME
---------- ----------
         1 killdb1
         2 killdb3
         3 oracleplus.net

SQL> SELECT * FROM emp;

    DEPTNO ENAME
---------- --------------------
     Oracleoracleplus.net    1 killdb2
         2 killdb4
         3 oracleplus.net

SQL> DELETE FROM emp WHERE DEPTNO=1;
1 ROW deleted.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
  3   WHERE sid IN
  4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      56005          0          3          0          0
       138 TM      56007          0          3          0          0
       138 TX    1179670        249          6          0          0

针对子表外键列上无index的情况针对dml操作,产生的TM lmode为3的lock,而且是子表和父表都会产生,这就非常不好了,容易产生死锁,这就是为什么针对外键列需要建index的原因,下面来看看有index的情况。

———with index

---insert


SQL> ROLLBACK;
ROLLBACK complete.

SQL> CREATE INDEX ind_emp ON emp (deptno, ename);
INDEX created.

SQL> INSERT INTO DEPT VALUES (4, 'baidu.com');
1 ROW created.

SQL> commit;
Commit complete.

SQL> INSERT INTO EMP VALUES (4, 'google.cn');
1 ROW created.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
  3   WHERE sid IN
  4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      56005          0          2          0          0
       138 TM      56007          0          3          0          0
       138 TX     917547        249          6          0          0


----update

SQL> ROLLBACK;
ROLLBACK complete.

SQL> UPDATE dept SET dname ='killdb2' WHERE deptno=1;
1 ROW updated.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
  3   WHERE sid IN
  4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      56005          0          3          0          0
       138 TX     851990        239          6          0          0


---delete

SQL> ROLLBACK;
ROLLBACK complete.

SQL>  DELETE FROM emp WHERE DEPTNO=1;
1 ROW deleted.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
  3   WHERE sid IN
  4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      56005          0          2          0          0
       138 TM      56007          0          3          0          0
       138 TX    1114138        232          6          0          0

可以看到,针对父表的操作,不会在子表上产生锁,针对子表的操作会在子表和父表都产生锁,但是有点不同,父表是lmode 2,子表是lmode 3,而无index的情况是:子表和父表都是tm lmode 3.

mos上一篇文档描述了针对该情况的改变:

Changes in behaviour in different versions

When your application has referential integrity and attempts to modify the child/parent table, Oracle will
get additional locking on the parent/child table when there is no index on the foreign key. It is therefore
recommended to add indexes for all foreign key defined in the database.

When indexes are added on foreign keys columns of the child table, Oracle will only require Mode 3 Row-X (SX)
locks on the modified table (parent or child) in Oracle 8.1.7 and 9.0.1. In Oracle9.2 onwards, 'mode 2 Row-S
(SS)' locks are required on the linked table (child table when modifying the parent table, or parent table
when modifying the child table).

When indexes are not present on child table foreign keys columns, on top of the previous locking
situation Oracle will require:

a) In 8.1.7, 'mode 4 Share' locks on the child table when updating/deleting from the parent table.
The lock mode even becomes a 'mode 5 S/Row-X (SSX)' lock when deleting from the parent table with
a 'delete cascade' foreign key constraint.Those locks can't be disabled (ORA-00069) and are held
during the full transaction time.

b) In 9.0.1, Oracle only need those additional locks during the execution time of the UPDATE or DELETE.
Those locks are downgraded to 'mode 3 Row-X (SX)' locks when the execution is finished. It is thus an
improvement compared to Oracle 8.1.7.

c.In 9.2.0, the downgraded 'mode 3 Row-X (SX)' locks are no longer required except when deleting from
a parent table with a 'delete cascade' constraint.

In higher versions, as the locks are not help for the entire duration of the transaction, but only during
the actual execution of the UPDATE/DELETE, you may not be able to "catch" them.

上面我还是还是没有模拟出产生TM lmode=6的情况,下面继续:

正常情况下,对于TM 级别产生lmode 6的锁,那么肯定是针对该表有DDL比如alter table或其他,这里我模拟另外一种:

SQL> CREATE TABLE test_pdml
  2  storage (initial 1M NEXT 1M pctincrease 0)
  3  nologging
  4  AS SELECT * FROM dba_objects WHERE 1=2;

TABLE created.

SQL> ALTER TABLE test_pdml parallel (degree 4);
TABLE altered.

SQL> INSERT /*+ append parallel(test_pdml,4) */
  2  INTO test_pdml
  3    SELECT * FROM dba_objects;

51136 ROWS created.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
  3   WHERE sid IN
  4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 PS          1          0          4          0          0
       138 PS          1          1          4          0          0
       138 PS          1          2          4          0          0
       138 PS          1          3          4          0          0
       134 PS          1          0          4          0          0
       145 PS          1          1          4          0          0
       159 PS          1          2          4          0          0
       135 PS          1          3          4          0          0
       138 TM      56084          0          6          0          0
       134 TS          6   25166477          4          0          0
       145 TS          6   25170189          4          0          0

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       135 TS          6   25170317          4          0          0
       159 TS          6   25170445          4          0          0
       138 TS          6   25170445          4          0          0
       138 TS          6   25170189          4          0          0
       138 TS          6   25166477          4          0          0
       138 TS          6   25170317          4          0          0
       135 TX     786455        245          6          0          0
       145 TX    1179664        249          6          0          0
       138 TX     917540        249          6          0          0
       134 TX     851987        239          6          0          0
       159 TX    1310723        248          6          0          0

22 ROWS selected.

SQL> SELECT object_id,owner,object_name FROM dba_objects WHERE object_name=UPPER('test_pdml');

OBJECT_ID OWNER                          OBJECT_NAME
---------- ------------------------------ --------------------
     56084 ROGER                          TEST_PDML

SQL> commit;
Commit complete.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
  3   WHERE sid IN
  4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

no ROWS selected

SQL> UPDATE /*+ parallel(test_pdml,4) */ test_pdml SET owner='oracleplus.net';
51136 ROWS updated.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
WHERE sid IN
  3    4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 PS          1          0          4          0          0
       138 PS          1          1          4          0          0
       138 PS          1          2          4          0          0
       138 PS          1          3          4          0          0
       136 PS          1          0          4          0          0
       135 PS          1          1          4          0          0
       159 PS          1          2          4          0          0
       134 PS          1          3          4          0          0
       138 TM      56084          0          6          0          0
       136 TM      56084          0          1          0          0
       136 TM      56084          1          4          0          0

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       135 TM      56084          0          1          0          0
       135 TM      56084          1          4          0          0
       159 TM      56084          0          1          0          0
       159 TM      56084          1          4          0          0
       134 TM      56084          0          1          0          0
       134 TM      56084          1          4          0          0
       136 TX     983061        254          6          0          0
       134 TX    1376273        247          6          0          0
       135 TX    1048590        244          6          0          0
       138 TX    1245196        252          6          0          0
       159 TX    1114154        232          6          0          0

22 ROWS selected.

SQL> commit;
Commit complete.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
  3   WHERE sid IN
  4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

no ROWS selected

SQL> DELETE /*+ parallel(test_pdml,4) */ FROM test_pdml WHERE object_id <20001;
19454 ROWS deleted.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
  2    FROM v$LOCK
  3   WHERE sid IN
  4         (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID'));

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 PS          1          0          4          0          0
       138 PS          1          1          4          0          0
       138 PS          1          2          4          0          0
       138 PS          1          3          4          0          0
       145 PS          1          0          4          0          0
       134 PS          1          1          4          0          0
       159 PS          1          2          4          0          0
       135 PS          1          3          4          0          0
       138 TM      56084          0          6          0          0
       145 TM      56084          0          1          0          0
       145 TM      56084          1          4          0          0

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       134 TM      56084          0          1          0          0
       134 TM      56084          1          4          0          0
       159 TM      56084          0          1          0          0
       159 TM      56084          1          4          0          0
       135 TM      56084          0          1          0          0
       135 TM      56084          1          4          0          0
       135 TX     917535        249          6          0          0
       134 TX    1310729        248          6          0          0
       138 TX     851997        239          6          0          0
       145 TX    1179667        249          6          0          0
       159 TX     786442        245          6          0          0

22 ROWS selected.

我们可以看到针对parallel dml 会产生TM lmode 为6的lock。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle dml操作产生TM锁 lmode=6 分析原因和解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1361.html

Oracle研究中心

关键词:

tm lmode=6

dml操作产生TM锁 而且lmode是6

Oralce为什么要针对外键列需要建index