How to Obtain a System State Trace BEFORE the Error ORA-2049 Occurs, While Still Experiencing the Contention (Doc ID 789517.1)

Last updated on APRIL 16, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2 [Release 10.1 to 11.2]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2


Goal

This document is intended to explain under what conditions an ORA-02049 'timeout: distributed transaction waiting for lock' error can be signaled, and to provide atoolto diagnose situations when this occurs.  The challenge with troubleshooting these types of errors is that by the time the error is thrown, the contention no longer is present.  A type of trace used byOracleSupport called a system state dump is useful for finding the session holding the resources that the session(s) signaling ORA-02049 are waiting on, but it must be obtained before the error is signaled.  Therefore the contention cannot be captured even by cascading a system state trace off of an ORA-02049 errorstack trace, which is automatically written when the ORA-02049 error occurs.

An ORA-02049 'timeout: distributed transaction waiting for lock' error occurs when a session that is involved in a distributed transaction waits for another session's TX enqueue (row lock) for longer than the database distributed_lock_timeout. What constitutes a 'distributed transaction' is what generates confusion on this topic. Any operation that uses a database link - even a query - starts a distributed transaction. For example, consider the following:

Session 1:
-----------
SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------------------------
ORCL102A.WORLD

SQL> update scott.dept set loc='NOME' where deptno=10;

1 row updated.


Session 2:
----------
SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------------------------
ORCL102A.WORLD

SQL> select 1 from dual@orcl102b.world;

         1
----------
         1

SQL> update scott.dept set loc='NOME' where deptno=10;
update scott.dept set loc='NOME' where deptno=10
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock

 If a 'commit' is issued in session 2 after the distributed query but before the update operation, no ORA-02049 error is encountered. 

Common methods of dealing with this problem are: 

  1. Increase the database initialization parameter distributed_lock_timeout. This will enable sessions involved in a distributed transaction to wait longer for a row lock, in hopes that the session holding the row lock will release it in the increased span of time.

  2. Code the application so that the error is a 'try again' exception - if it hits the error, keep trying the action until it succeeds. Eventually the session holding the contended row lock should release it and the waiting session should be able to proceed.

However, it is a common desire for customers to want to identify the session holding the resources.  The procedure presented below allows us to do this five seconds before an ORA-02049 will be signaled.

NOTE: "enq: TX - row lock contention" is based on how your application has been designed, coded and being executed by different sessions to cause row level contention. This wait can only be fixed by changing the application code, design or usage of the application.
 
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms