Tracking Down TM Locks

Background & Overview

Tracking down locking issues can be problematic. The following example explains how to track down a locking issue caused by un-indexed foriegn keys.

Description of Problem

The following issue is reported in an Oracle trace file.

*** 2011-12-14 16:37:50.004
user session for deadlock lock 76d2a5698
  pid=486 serial=52030 audsid=1072481574 user: 222/<none>
  O/S info: user: , term: , ospid: 1234, machine: mymachine.net
            program:
  Current SQL Statement:
  DELETE FROM MYTAB WHERE MYID = :B3 AND MY_FROM_DATE >= :B2 AND MY_TO_DATE <= :B1
ENQUEUE DUMP REQUEST: from 2.25421 on [0x1a5c1][0x0],[TM] for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 4 res [0x1a5c1][0x0],[TM]

Summary of Analysis and Findings

The problem is caused by missing indexes for foreign keys defined on the table involved in TM lock. The high level steps are detailed below.

• Find the table involved in the lock by converting the resname from Hex value 0x1A5C1 to Decimal and querying dba_tables for the object in question.
• Then run FK_Check.sql to find which indexes need to be created.
• Matching up the table in step one to the table in step two will indicate which index to create.

Note: The Resname is identified in trace: res [0x1a5c1][0x0],[TM]

Step-By-Step

1. Convert Resname to Decimal

• echo "ibase=16; 1A5C1" |bc
  • Returns: 107969

2. Find the table involved

• sqlplus '/ as sysdba'
• SQL> select owner,object_name from dba_objects where object_id=107969;
  • Returns: MYOWNER, ANOTHER_TAB

3. Run FK_Check.sql

• sqlplus MYOWNER/MYPASSWORD
• SQL> @FK_Check.sql

Example Output

> echo "ibase=16; 1A5C1" |bc
> 107969
> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select owner,object_name from dba_objects where object_id=107969;

OWNER           OBJECT_NAME
--------         --------------
MYOWNER       ANOTHER_TAB

SQL> connect MYOWNER/MYPASSWORD
SQL> @FK_Check.sql

LINEMSG
--------------------------------------------------------------------------------
Changing data in table MYTAB will lock ANOTHER_TAB table 
Create an index on table ANOTHER_TAB with the following columns to remove
 lock problem

Column = MYFK (1)

FK_Check.sql

The FK_Check script can be found here.