Trouble shoot -- enq: TM - contention

Resolve  "enq: TM - contention " issues in Oracle

Recently, during  monitoring production system, I found "enq: TM - contention" oracle event. The blocked sessions were executing simple INSERT & UPDATE statements similar to:

INSERT INTO customer VALUES (:1, :2, :3);

Query to find blocking session details:

select sid,serial#,event, blocking_session, username,status,terminal,program,sql_id
from v$session
where BLOCKING_SESSION  IS NOT NULL;

About "enq: TM - contention" :

These kind of Waits i.e., enq: TM - contention indicate there are un-indexed foreign key constraints. Reviewing the CUSTOMER table, we found a foreign key constraint referencing the PRODUCT table that did not have an associated index. This was also confirmed with development team and verified with DDL. We added the index on the column referencing the PRODUCT table and the problem was solved.

Finding the root cause of the enq: TM - contention wait event

Using the above query to find the blocking sessions, we found the real culprit. Periodically, as the company reviewed its vendor list, they "cleaned up" the CUSTOMER  table several times a week. As a result, rows from the CUSTOMER table were deleted. Those delete statements were then cascading to the PRODUCT table and taking out TM locks on it.

Reproducing a typical problem that leads to this wait

This problem has a simple fix, but I wanted to understand more about why this happens. So I reproduced the same issue to see what happens under the covers. I first created a subset of the tables from this CUSTOMER and loaded them with sample data.

CREATE TABLE customer
( customer_id number(10) not null,
customer_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT customer_pk PRIMARY KEY (customer_id)
);
INSERT INTO customer VALUES (1, 'customer 1', 'Contact 1');
INSERT INTO customer VALUES (2, 'customer 2', 'Contact 2');
COMMIT;

CREATE TABLE product
( product_id number(10) not null,
product_name varchar2(50) not null,
customer_id number(10) not null,
CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customer(customer_id)
ON DELETE CASCADE );
INSERT INTO product VALUES (1, 'Product 1', 1);
INSERT INTO product VALUES (2, 'Product 2', 1);
INSERT INTO product VALUES (3, 'Product 3', 2);
COMMIT;

I then executed statements similar to what we found at this customer:

User 1: DELETE customer WHERE customer_id = 1;
User 2: DELETE customer WHERE customer_id = 2;
User 3: INSERT INTO customer VALUES (5, 'customer 5', 'Contact 5');

Similar to the customer's experience, User 1 and User 2 hung waiting on "enq: TM - contention". Reviewing information from V$SESSION I found the following:

-- Find details of blocking sessions
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) = UPPER('&User')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;

-- Solution

Following along with the solution we used for our customer, we added an index for the foreign key constraint on the CUSTOMER table back to the PRODUCT table:

sql> CREATE INDEX idx_fk_customer ON product (customer_id);

When we ran the test case again everything worked fine. There were no exclusive locks acquired and hence no hanging. Oracle takes out exclusive locks on the child table, the PRODUCT table in our example, when a foreign key constraint is not indexed.

Sample query to find unindexed foreign key constraints

Now that we know unindexed foreign key constraints can cause severe problems, here is a script that I use to find them for a specific user (this can easily be tailored to search all schemas):

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;

Thanks
Please feel free to post comments...