This discussion is archived
4 Replies Latest reply: Jun 29, 2007 12:27 PM by 247514 RSS

what´s event "enq: TM - Contention"

537198 Newbie
Currently Being Moderated
What´s difference between events "enq: TM - Contention" and "enq: TX - row lock contention" ?

When my procedure executes in n instances, the event "enq: TM - Contention" appers and generates lockwait in v$session. what´s this ???
  • 1. Re: what´s event "enq: TM - Contention"
    247514 Expert
    Currently Being Moderated
    Consult Oracle Performance Tuning Guide

    10.3.7.2.3 TM enqueue
    The most common reason for waits on TM locks tend to involve foreign key constraints where the constrained columns are not indexed. Index the foreign key columns to avoid this problem.

    10.3.7.2.4 TX enqueue
    These are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.
  • 2. Re: what´s event "enq: TM - Contention"
    537198 Newbie
    Currently Being Moderated
    Hi, thanks a lot.
  • 3. Re: what´s event "enq: TM - Contention"
    537198 Newbie
    Currently Being Moderated
    HI,

    How to prevent the TM contention (TABLE LOCK) ???
    I create indexes in foreign keys, run the analyze table, but the contention continous....

    have any query tuning to check parameters ?? Have any parameter can be altered or checked ?

    thanks,
    Carlos Pinheiro
  • 4. Re: what´s event "enq: TM - Contention"
    247514 Expert
    Currently Being Moderated
    Does someone trying to modify the tables while your database are being used by other users?

    TM lock is required because Oracle need to make sure the table structure is not changed while it doing insert for example.

    A transaction usually has both TX lock and TM lock, a select on v$lock will reveal that
    ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
    ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
    000000038BEFE918 000000038BEFEA98         17 TX      65559       3158          6          0         52          0
    000000038BEEC0C8 000000038BEEC0F0         17 TM       9690          0          3          0         52          0
    By linking ID1 with DBA_OBJECTS you can find out which object being locked
    SQL> select object_name from dba_objects where object_id=9690
      2  /

    OBJECT_NAME
    --------------------------------------------------------------------------------------------------------------------------------
    T_TEST
    Run a statspack report will help the troubleshooting as well.