Print Friendly, PDF & Email
oerr ora 02049 - The number of seconds specified in the distributed_lock_timeout initialization parameter were exceeded while waiting for a lock or for a begin transaction hash collision to end.
SYMPTOMS

An application reports multiple errors ORA-02049: timeout: distributed transaction waiting for lock in the application's log file.

ORA-02049-timeout-distributed-transaction-waiting-for-lock

DEMO

DEMO: ORA-02049: TIMEOUT: DISTRIBUTED TRANSACTION WAITING FOR LOCK

RELATED

HOW TO IDENTIFY BLOCKED AND BLOCKING SESSIONS IN ORACLE DATABASE
HOW TO FIND ALL SESSIONS HOLDING A TABLE LOCK

CAUSE

The error occurs when a local session through a database link attempts to modify a locked row of a table in a remote database. The local session fails with a timeout if it waits longer than a time specified in seconds by distributed_lock_timeout initialization parameter.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout             integer     60
oracle@dbpilot.net> oerr ora 02049
02049, 00000, "timeout: distributed transaction waiting for lock"
// *Cause: "The number of seconds specified in the distributed_lock_timeout
//          initialization parameter were exceeded while waiting for a lock
//          or for a begin transaction hash collision to end."
// *Action: Treat this timeout as a deadlock. Roll back the transaction and
//          try again.
SOLUTION
1. As a DBA you can increase the distributed_lock_timeout parameter to a high value. It will requre a database bounce after that.
SQL> sho parameter distributed_lock_timeout

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout             integer     60

SQL>  alter system set distributed_lock_timeout=600 scope=spfile;

System altered.

SQL> shutdown immediate
SQL> startup

SQL> show parameter distributed_lock_timeout

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout             integer     600
2. As a developer, you can handle the ORA-2049 as a 'try again' exception in your code.

 

REFERENCES

OERR: ORA-2049 "timeout: distributed transaction waiting for lock" Reference Note (Doc ID 19332.1)

Written At
05 OCT 202019:00
Red Hat Release
7.x x64
Database Release
19.6.0.0.0