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.