enq: TM – contention

It is common for a DBA  to see “enq: TM – contention” wait events. We are seeing this wait events from DMLs against a parent table :

SQL> insert into teachers values (3, 'Teacher 3');

Through investigation, the problem is due to missing indexes of foreign keys. Below are the steps for the test case:

1) Create a parent table:

SQL> create table teachers 
     ( 
       teacher_id number not null, 
       teacher_name varchar2(20) not null, 
       constraint teacher_pk 
       primary key ( teacher_id )
      );

Table created.

SQL>insert into teachers values (  1, 'James Smith');

1 row created.

SQL> insert into teachers values ( 2,'Richard Lee');

1 row created.

SQL> commit;

Commit complete.

2) Create a Child table:

SQL> create table students 
     ( 
       student_id number not null, 
       student_name varchar2(20) not null, 
       teacher_id number not null, 
       constraint student_pk primary key ( student_id), 
       constraint student_fk foreign key ( teacher_id) 
                             references teachers(teacher_id)
       on delete cascade
     );

Table created.

SQL> insert into students values (1,'Student 1', 1);

1 row created.

SQL> insert into students values (2,'Student 2',1);

1 row created.

SQL> insert into students values (3,'Student 3',2);

1 row created.

SQL> insert into students values (4,'Student 4',2);

1 row created.

SQL> commit;

Commit complete.

3) Get three sessions and run sql respectively :

— Session 1:

SQL> delete from teachers where teacher_id=1;

— Session 2:

SQL> delete from teachers where teacher_id=2;

— Session 3:

SQL> insert into teachers values (3, 'Teacher 3');

4) Check session WAIT EVENT:

SQL>SELECT l.sid, s.blocking_session blocker, s.event, l.type, 
           l.lmode, l.request, o.object_name, o.object_type 
FROM v$lock l, dba_objects o, v$session s 
WHERE UPPER(s.username) = 'TESTUSER' 
AND l.id1 = o.object_id (+) 
AND l.sid = s.sid 
ORDER BY sid, type;

...
...
485   385 enq: TM - contention   TM   0    5 STUDENTS       TABLE
485   385 enq: TM - contention   TM   3    0 TEACHERS       TABLE
766       enq: TM - contention   TM   3    0 TEACHERS       TABLE
766       enq: TM - contention   TM   0    3 STUDENTS       TABLE
....

5) Check missing indexes for foreign keys, and created those indexes:

SQL>SELECT * FROM (
    SELECT c.table_name, cc.column_name, cc.position column_position
    FROM user_constraints c, user_cons_columns cc
    WHERE c.constraint_name = cc.constraint_name
    AND c.constraint_type = 'R'
  MINUS
    SELECT i.table_name, ic.column_name, ic.column_position
    FROM user_indexes i, user_ind_columns ic
    WHERE i.index_name = ic.index_name
                )
 ORDER BY table_name, column_position;

TABLE_NAME                     COLUMN_NAME   COLUMN_POSITION
------------------------------ ------------- ---------------
STUDENTS                       TEACHER_ID                  1

SQL> create index idx_student_fk on students(TEACHER_ID);

Index created.

6) Test step 3 again, there is no more “enq: TM Contention” wait events.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.