update语句，不涉及到主键更新，为何会请求子表TM share锁？

repentance

最近一段时间一套环境经常报锁，调查发现有一条update语句会请求子表的TM share锁，该语句并不涉及到主键的更新。
SQL_TEXT:
UPDATE T_POLICY_FEE TPF
  SET TPF.FEE_STATUS = '3', TPF.MATCH_RESULT = '0'
WHERE TPF.PREM_ID = (SELECT T.PREM_ID
            FROM T_POLICY_PREM TPP, T_POLICY_FEE T
            WHERE TPP.PREM_ID = T.PREM_ID
             AND T.FEE_ID = 234868002)
  AND TPF.FEE_ID <> 234868002
  AND TPF.PAY_MODE = 3
  AND TPF.FEE_STATUS = 2;

Event 10704 trc:
ksqcmi: TM,1a8d8,0 mode=2 timeout=21474836
……省略……
ksqcmi: TM,1ab75,0 mode=4 timeout=21474836
ksqcmi: TM,1ab75,0 mode=0 timeout=0
ksqcmi: TM,1abf2,0 mode=4 timeout=21474836
……省略……
ksqcmi: TM,1b3f6,0 mode=3 timeout=21474836
……省略……
ksqcmi: TM,217ed,0 mode=4 timeout=21474836
ksqcmi: TM,5a8bb,0 mode=2 timeout=21474836
标红色部分显示请求了TM share锁，这3个表是T_POLICY_FEE的子表，在对应的外键上均无索引。记得delete主表的时候如果子表外键无索引会请求子表TM share锁，为什么这条UPDATE语句也要请求子表的TMshare锁，求解。

Maclean Liu(刘相兵

请用  dbms_metadata.get_ddl 给出 该表 和其子表的  DDL 定义

repentance

内容太多，回帖受限，表定义见附件

Maclean Liu(刘相兵

T_POLICY_FEE

FEE_STATUS
     CONSTRAINT "FK_POLICY_FEE__FEE_STATUS" FOREIGN KEY ("FEE_STATUS")
      REFERENCES "PICCPROD"."T_FEE_STATUS" ("STATUS_ID") ENABLE,

     CONSTRAINT "CHK_POLICY_FEE__MATCH_RESULT" CHECK (MATCH_RESULT IN ('0','1','2',




If a session deletes a row in the parent-table (DEPT) and a referential constraint
     (foreign key) is created without an index on the child-table (EMP), or if the session
      is updating the column(s) that the foreign key references to then a share lock (level 4)
     is taken on the child table.



不仅delete 会造成 FK的 TM enqueue lock , 更新 foreign key参考的 Column也会造成 TM share lock level =4

repentance

Hi,maclean,3个请求TM4锁的表是下面这3个啊，这3个表是T_POLICY_FEE的子表，使用T_POLICY_FEE.FEE_ID关联，更新不涉及到fee_id，不满足你描述的TM4锁的两个条件
SQL> SELECT object_name,object_type from dba_objects a WHERE a.OBJECT_ID IN (SELECT to_number('1ab75','xxxxx') FROM dual);
OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
T_CLAIM_FEE          TABLE
SQL> SELECT object_name,object_type from dba_objects a WHERE a.OBJECT_ID IN (SELECT to_number('1abf2','xxxxx') FROM dual);
OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
T_COMMISION_INFO     TABLE
SQL> SELECT object_name,object_type from dba_objects a WHERE a.OBJECT_ID IN (SELECT to_number('217ed','xxxxx') FROM dual);
OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
T_ECOM_SETTLE_LIST   TABLE

[ 本帖最后由 repentance 于 2012-6-27 09:42 编辑 ]

Maclean Liu(刘相兵

action plan:

1.

尝试

UPDATE T_POLICY_FEE TPF
  SET TPF.FEE_STATUS = '3', TPF.MATCH_RESULT = '0'
where rownum=1;

这种写法并做 10046 + 10704 trace 看是否存在TM lock           ==>  之后你可以rollback掉

2.

在session级别设置以下参数 并观察

alter session set "_optimizer_join_elimination_enabled"=false;

repentance

按照上面的action plan测试
1.UPDATE T_POLICY_FEE TPF
  SET TPF.FEE_STATUS = '3', TPF.MATCH_RESULT = '0'
where rownum=1;
此语句不会申请子表TM4锁
2.alter session set "_optimizer_join_elimination_enabled"=false;
执行后原本申请TM4锁的语句也不再申请子表TM4锁

maclean能否解答下为何是这样的？

Maclean Liu(刘相兵

看起来你遇到了 一个bug  "ANSI SQL UPDATE STATEMENT RAISES TM LOCK ON TABLE"

Hdr: 8879890 10.2.0.4 RDBMS 10.2.0.4 CONSTRAINTS PRODID-5 PORTID-226 4597319
Abstract: ANSI SQL UPDATE STATEMENT RAISES TM LOCK ON TABLE


PROBLEM:
--------

1. Clear description of the problem encountered

ANSI SQL UPDATE STATEMENT RAISES TM LOCK ON TABLE

2. Pertinent configuration information (MTS/OPS/distributed/etc)

Ansi sql update causes TM lock.

3. Indication of the frequency and predictability of the problem

Consistently...

4. Sequence of events leading to the problem

Update from two sessions with ansi sql update.

5. Technical impact on the customer. Include persistent after effects.

It causes one session to hang on TM lock.


DIAGNOSTIC ANALYSIS:
--------------------
The problem in the SQL style:
ANSI SQL style raises TM lock
Old style SQL doesn't raises TM lock

I have also reproduced in house with customer's testcase.

WORKAROUND:
-----------
none

RELATED BUGS:
-------------
none

REPRODUCIBILITY:
----------------
yes...able to reproduce.

TEST CASE:
----------
Uploading exp dump file and readme file.

STACK TRACE:
------------
None

SUPPORTING INFORMATION:
-----------------------
testcase

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
n/a

DIAL-IN INFORMATION:
--------------------
n/a

IMPACT DATE:
------------
9/1/09


Reduced the test to just the required objects.

The key points here are:
  - QLF_CALCULATION_RECORD has a row level trigger
      QLF_CALCULATION_RECORD_BUI defined on it
  - The trigger has code that may alter the column
      BATCH_POSTING_MONTH_ID
  - BATCH_POSTING_MONTH_ID has a foreign key constraint
      defined on it:
       "QLFCALREC$FK$BPGMONID"
        FOREIGN KEY ("BATCH_POSTING_MONTH_ID")
        REFERENCES "SL_PERIOD_LNK" ("SL_PERIOD_LNK_ID")
  - BATCH_POSTING_MONTH_ID is not indexed

The simplest option here is probably to create an index
on QLF_CALCULATION_RECORD.BATCH_POSTING_MONTH_ID so
that this FK constraint (this is normally recommended
anyway for FK constraints).


BDE Screening
~~~~~~~~~~~~~
Testcase
~~~~~~~~~~~~~~~~~

  Files:  BDETC.tar.Z (containing setup.sql, setup.dmp, tc.sql, q2.sql)

  Steps:  
    sqlplus /nolog @setup
      Creates a user TC with relevant tables.

    sqlplus /nolog @tc
      Runs the ANSI SQL UPDATE statement as TC, shows the locks held,
        then hosts out and runs the same statements in a second session.
        ^
        The second session blocks waiting on a TM mode 5 lock request


Reproduced
~~~~~~~~~~
  Reproduced in 10.2.0.4

  NOT reproduced in 11.1.0.7
  NOT reproduced in RDBMS_MAIN_LINUX_090902        

  NOT Reproduced in 10.2.0.4 + fix for bug 4597319

Workaround/s
~~~~~~~~~~~~
  Put an index on the foreign key columns (recommended)
OR
  set "_optimizer_join_elimination_enabled"=false


Diagnostic Notes
~~~~~~~~~~~~~~~~
  The locking scenario depends on the route taken throught the optimizer
  code . In this case the join elimination code runs and it tries to load
  constraint information , but it also loads constraint enforcement
  cursors which leads to the blocking lock request on the second session.

  This is addressed by the fix for bug 4597319.
  This was confirmed by reproducing the issue in 10.2.0.4,
    then applying jamferna_blr_backport_4597319_10.2.0.4.0
    and trying again and the blocking scenario no longer occurs.


  Hence marking this as a duplicate of bug 4597319.

Maclean Liu(刘相兵

这个bug 的base bug 是 Bug 4597319  CBO loads more constraint data than needed - can cause TM waits

Affects:

    Product (Component)        Oracle Server (Rdbms)
    Range of versions believed to be affected        Versions < 11
    Versions confirmed as being affected       

        10.2.0.4

    Platforms affected        Generic (all / most platforms affected)

Fixed:

    This issue is fixed in       

        11.1.0.6 (Base Release)

Symptoms:
       
Related To:

    Hang (Process Hang)
    Waits for "enq: TM - contention"

       

    Optimizer
    Constraint Related
    _OPTIMIZER_JOIN_ELIMINATION_ENABLED

Description

    Certain CBO options may load more constraint information
    than is needed when finding the execution plan for a
    SQL leading to extra memory use and possible unexpected
    TM lock waits from concurrent sessions sharing the same
    cursor.

    eg: This can occur if there are unindexed foreign key
        constraints and the optimizer attempts join elimination
        (as that in turn may load excess constraint data)


    Workaround
     Remove or disable constraints on the table
     OR
     Avoid the problem CBO option that loads the constraint data.
     eg: In some cases setting "_optimizer_join_elimination_enabled"=false
         can help.

repentance

确实是这个bug，描述和我们系统的状况一致，通过外键建立索引或设置"_optimizer_join_elimination_enabled"=false参数可以解决，多谢maclean。

zengmuansha

更新父表的非关键字的字段 也会影响到子表

chen28

所以建立外键索引还是很有必要
  而且还可以避免 因此引起的性能问题