- 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.
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.
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>