4 Replies Latest reply: Jun 29, 2007 2:27 PM by 247514 RSS

    what´s event "enq: TM - Contention"

      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"
          Consult Oracle Performance Tuning Guide

 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.

 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"
            Hi, thanks a lot.
            • 3. Re: what´s event "enq: TM - Contention"

              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 ?

              Carlos Pinheiro
              • 4. Re: what´s event "enq: TM - Contention"
                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  /

                Run a statspack report will help the troubleshooting as well.