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

zengmuansha

11^#

发表于 2012-6-28 16:06:26

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

10^#

发表于 2012-6-28 15:00:42

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

9^#

发表于 2012-6-28 14:24:33

这个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.

8^#

发表于 2012-6-28 14:15:51

回复 7# 的帖子

看起来你遇到了一个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.

7^#

发表于 2012-6-28 14:09:28

按照上面的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能否解答下为何是这样的？

6^#

发表于 2012-6-27 10:48:19

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;

5^#

发表于 2012-6-27 09:21:38

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 编辑 ]

life5901_ora_1270004.txt

27.12 KB, 下载次数: 876

10046 level8+10704

4^#

发表于 2012-6-26 19:45:08

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

3^#

发表于 2012-6-26 17:48:18

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

table metadata.txt

18.75 KB, 下载次数: 940

2^#

发表于 2012-6-26 11:00:07

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