Definition

Waiting session want to take out a TM or table modification lock table but another session already holds the lock. TM locks are taken out  on an object to insure that the object structure does not change while an operation is happening.  For example, when taking out a TX lock to do an insert, the session will also take out a TM lock  to protect the structure of the table being inserted into.

Typically: happens when a session inserts into the child table which has a foreign key relation and  then  modification is made on a parent table by another session.  If the child table lacks an index on the foreign key field then the modification of the parent will try and lock the child table which will hang on “enq: TM – contention” if the insert on the child is uncommitted by the second user:

User 1                                                                     User 2

create table parent (

     id number primary key);

 create table child (

     id number references parent,

     name varchar2(20));

 insert into parent values (1);

 insert into parent values (2);

 commit;

                                                                                delete from parent where id=2;

delete from parent where id=2;

  Rare: of the child is partitioned and some of the partitions are in a read only tablespace then a insert into the child will  take at TM mode 4 and an insert into the parent will try and take a TM mode 3 which will lock , see bug 6066587 (not a bug) 

Other: rebuild index, shrink, validate structure will request TM in mode 4 which is incompatible with active transactions that have TX locks and corresponding TM in mode 3 (share)

Analysis

If you have Active Session History data either from v$active_session_history in 10g or SASH (http://ashmasters.com/ash-simulation/) or similar data the select out the following fields

select

        event,

        sql_id,

        mod(p1,16)  as “mode”,

        p2|| ‘ ‘ || o.name obj

 from v$active_session_history ash,

      obj$ o

 where

       event  like ‘enq: TM%’

   and o.obj# (+)= ash.p2

 order by sample_time;

 This will find all “enq: TM – contention” waits and if the mode is “4” then it is probably a problem of unindexed foreign key. The child table should then be the table that shows up under “OBJ” column. The query text can be looked up using the SQL_ID. 

EVENT                          SQL_ID        mode OBJ

—————————— ————- —- ———–

enq: TM - contention           8zw36yw3fq4yy    4 53372 CHILD

enq: TM - contention           8zw36yw3fq4yy    4 53372 CHILD 

Solution

 Index the foreign key column in child table.

See 3.1.1 Enq: TM – contention , finding FK constraint risks

For a script to find unindexed foreign keys

http://blog.go-faster.co.uk/2007/10/tm-locking-checking-for-missing-indexes.html