ORA-02049 Timeout: Distributed Transaction Waiting for Lock


Key Facts:

  • This issue occurs when a session that is involved in a distributed transaction waits for another session’s TX enqueue (row lock) for longer than the distributed_lock_timeout value.
  • Any operation that uses a database link, even a query starts a distributed transaction.

In the below POC, if there was a COMMIT after the [select 1 from dual@database_link], the situation would become a normal lock situation – the second session will proceed once there is a COMMIT in the 1st  session. In a situation which were in a session was waiting on a row lock and involved in distributed transaction, it would timeout after the distributed_lock_timeout period. If the transaction was a normal one, sooner or later a DBA will notice the lock and could kill the holding session.

Workarounds:

There are two ways to deal with this problem:

  • Increase the distributed_lock_timeout value hoping that the session holding the lock releases it before the timeout.
  • Code the application to capture this ORA error and then issue a TRY AGAIN directive.
  • Another workaround would be to add more COMMIT’s as they tend to reset the distributed transaction.

Identifying the Root Cause:

To identify the session holding the resources, what oracle suggests is to dump a system state trace BEFORE the ORA-02049 occurs.

However, I have devised another approach based on oracle’s original suggestion. The basic idea will be to monitor v$session for sessions waiting on the event ‘enq: TX – row lock contention’. If the seconds_in_wait value for such sessions approaches the distributed_lock_timeout value and the session is involved in a distributed transaction we can find the session holding the lock by joining v$lock and v$session.

POC

Session 1

SQL> update dummy_table set table_name ='UPDATED_DUMMY';       -->A LOCAL UPDATE STATEMENT

24 rows updated.

-->NO COMMIT ISSUED

SQL>

Session 2

SQL> select 1 from dual@database_link; ------>THIS STARTS THE DISTRIBUTED TRANSACTION

         1

----------

         1

SQL> update dummy_table set table_name ='UPDATED_DUMMY';  ---->THIS IS A LOCAL UPDATE, BUT ERRORS OUT WITH ORA-02049

update dummy_table set table_name ='UPDATED_DUMMY'

      *

ERROR at line 1:

ORA-02049: timeout: distributed transaction waiting for lock

SQL>

 

Advertisements

2 thoughts on “ORA-02049 Timeout: Distributed Transaction Waiting for Lock

    • Umesh,
      What I did was to schedule a dbms job to capture and email me the details of the sessions which are waiting for more than 15 minutes (the timeout value in my database) on the wait event “enq: TX – row lock contention”. This will be a simple SQL based on v$session – select the sid, serial#, program, sql_id, blocking_status. You can add in a select within select to get the blocking session details (use the column blocking session from v$session). So in a day or two you would have captured the conflicting sessions

      Keep in mind that in 9i you have to link v$session and v$session_wait to
      achieve the above.

      A sample SQL would be:
      select s.sid,s.serial# .sql_id,….. (select s1.sid||s1.serial# ||sql_id….
      from v$session
      where sid=s.sid) “Blocking sess details”
      from v$session s,
      v$session_wait w
      where s.sid=w.sid
      and w.event=’enq: TX – row lock contention’
      and w.seconds_in_wait >10

      Let me know if you have trouble.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s