Unindexed foreign keys & enq:TM - contention

I was recently reminded of the chaos that can be caused by an unindexed foreign key column when there are corresponding updates or deletes from the parent table. To illustrate this particular situation here is some example DDL

CREATE TABLE parent_t
(
parent_id	INTEGER NOT NULL,
parent_name	VARCHAR2(30) NULL,
CONSTRAINT parent_t_pk PRIMARY KEY (parent_id)
);

INSERT INTO parent_t SELECT object_id, object_name FROM dba_objects;

CREATE TABLE child_t
(
child_id	INTEGER NOT NULL,
parent_id	INTEGER NULL,
child_name	VARCHAR2(30) NULL,
CONSTRAINT child_t_pk PRIMARY KEY (child_id),
CONSTRAINT parent_id_fk FOREIGN KEY (parent_id) REFERENCES parent_t (parent_id)
);

in short we have two tables, a parent (parent_t) and a child (child_t) and we have a foreign key constraint declared between them on the parent_id column but no index on the foreign key column (parent_id) in the child table i.e. we have no index on child_t (parent_id). This is a relatively common situation and by itself does not cause any problems, the problems arise when we update the primary key column(s) or delete parent rows.

E.g. along comes a session that inserts a row into the child table

SQL> select distinct sid from v$mystat;

       SID
----------
       135

SQL> insert into child_t values (1,20,'child 1');

1 row created.

and along comes a second session that attempts an update on any parent row e.g.

SQL> select distinct sid from v$mystat;

       SID
----------
        71

SQL> update parent_t set parent_id=10, parent_name='par 10' where parent_id=10;

This parent update blocks waiting on eng:TM - contention. However, the situation now rapidly gets worse, other operations against the parent table also stack up behind the first one e.g. a regular insert

SQL> select distinct sid from v$mystat;
       SID
----------
       132

SQL> insert into parent_t values (1000000,'par 1000000');

This parent insert also blocks waiting on eng:TM - contention. With a busy parent table it is easy to see that the situation will rapidly turn into a problem. At the moment v$session shows the blockers as

SQL> select sid, sql_id, blocking_session, event, p2 from v$session where blocking_session is not null
     union
     select sid, sql_id, blocking_session, event, p2 from v$session where sid in (select blocking_session from v$session);

       SID SQL_ID        BLOCKING_SESSION EVENT                                P2
---------- ------------- ---------------- ---------------------------- ----------
        71 25nmv2v6ht56k              135 enq: TM - contention             178452
       132 f0f74a4jzdnpd               71 enq: TM - contention             178452
       135                                SQL*Net message from client           1

while utllockt.sql shows the following output

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD      LOCK_ID1          LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
135               None
   71             DML               Share          Row-X (SX)     178452            0
   132            DML               Row-X (SX)     Row-X (SX)     178452            0

here is the dba_locks output

SQL> select session_id, lock_type, mode_held, mode_requested, lock_id1, blocking_others
from dba_locks where session_id in (71,135,132) order by last_convert

SESSION_ID LOCK_TYPE         MODE_HELD       MODE_REQUESTED  LOCK_ID1          BLOCKING_OTHERS
---------- ----------------- --------------- --------------- ----------------- ---------------
       132 DML               Row-X (SX)      None            178450            Not Blocking
       132 DML               None            Row-X (SX)      178452            Not Blocking
        71 DML               Row-X (SX)      None            178450            Not Blocking
        71 DML               None            Share           178452            Not Blocking
       135 Transaction       Exclusive       None            65559             Not Blocking
       135 DML               Row-X (SX)      None            178450            Not Blocking
       135 DML               Row-X (SX)      None            178452            Blocking
       132 AE                Share           None            100               Not Blocking
        71 AE                Share           None            100               Not Blocking
       135 AE                Share           None            100               Not Blocking

What is happening in this situation is that SID=71 is blocking waiting to obtain a share lock on the CHILD_T table but this is not available because of the Row-X (SX) lock held by the uncommited insert by session SID=135. For enq:TM - contention waits the ‘p2′ value corresponds to the dba_objects.object_id value i.e.

SQL> select object_id, object_name from dba_objects where object_id = 178452

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
    178452 CHILD_T

If we remove the ‘dummy’ primary key update from the SQL issued by SID=71 i.e. if we change the SQL to

SQL> select distinct sid from v$mystat;

       SID
----------
        71

SQL> update parent_t set parent_name='par 10' where parent_id=10;

the problem goes away because the RI no longer requires the child table lock. It is worth noting that the child table is empty during this, of course there is an uncommitted row which is causing the problem but obviously the size of the child table is not a factor here. Besides fixing the SQL to remove the unnecessary update we could also index the foreign key column ‘create index child_t_parent_id on child_t(parent_id)’ or remove the constraint. In general, if you join from the parent to the child which is often the case e.g. ‘get me all the order lines for this order’ then having the index in place is desirable from the performance perspective. In terms of troubleshooting this if you see lots of sessions blocking on enq:TM - contention if you take the p2 value and check the corresponding object for foreign keys with missing indexes that will usually point you in the right direction.