Distributed transaction waiting for lock solutions in Oracle

An error occurs when a session in a distributed transaction waiting for an exclusive TX lock exceeds the waiting time specified in the distributed_lock_timeout parameter.

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

Causes:

The waiting time to acquire a lock is increasing than the specified time in the distributed_lock_timeout

Solutions:

1. We must increase the value of the database parameter DISTRIBUTED_LOCK_TIMEOUT in init.ora or spfile. The default value is 60 seconds. Increase the value to 300 or more depending upon your network speed.

-- Check the value of distributed_lock_timeout parameter
SQL> show parameter distributed


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

distributed_lock_timeout             integer     60

-- Increase the parameter value to 300 seconds
SQL> alter system set distributed_lock_timeout=300 scope=both;


System altered.



SQL> show parameter distributed


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

distributed_lock_timeout             integer     300

Note: Sometimes we must manually check the waiting for lock objects with v$LOCK view.