ORA-02049: TIMEOUT: DISTRIBUTED TRANSACTION WAITING FOR LOCK

 
SYMPTOMS

 
RELATED
 
A BLOCKING SESSION
ORA-00060: DEADLOCK DETECTED WHILE WAITING FOR RESOURCE

 
DIAGNOSE
 
The error occurs when a session is trying to modify table’s row(s) in a remote database through a database link that is already locked by another session. The session’s transaction fails on a timeout if it’s waiting longer than an allowed wait time in seconds. An allowed wait time is ruled through the distributed_lock_timeout parameter. By default a session waits 60 seconds.

SQL> sho parameter distributed_lock_timeout

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

 
The description of the ORA-02049 error

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.

 
TESTCASE
 
Here is a simple testcase showing how to reproduce the ORA-02049 error

(+) Find out the current value of an allowed wait time for a distributed transaction

SQL> sho parameter distributed_lock_timeout

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

 
(+) Create the user TEST1 and the table TEST1 for the testcase

SQL> SHO USER
USER is "SYS"

CREATE USER TEST1 IDENTIFIED BY TEST1;
GRANT CONNECT,RESOURCE TO TEST1;
GRANT UNLIMITED TABLESPACE TO TEST1;
CREATE TABLE TEST1.TEST1(COL1 NUMBER);
INSERT INTO TEST1.TEST1 VALUES(1);
COMMIT;

 
The testcase table contains the following data

SQL> SELECT * FROM TEST1.TEST1;

      COL1
----------
         1

 
(+) Create a database link DBLINK_1 that is connecting to the local database ANGULAR using the TEST1 user credentials

SQL> SHO USER
USER is "SYS"

-- Create the database link
SQL> CREATE DATABASE LINK DBLINK_1 CONNECT TO TEST1 IDENTIFIED BY TEST1 USING 'ANGULAR';

Database link created.

-- Get details about the created database link
SET LINES 300
SET PAGES 999
COL OWNER FOR A5
COL DB_LINK FOR A10 
COL USERNAME FOR A10 
COL HOST FOR A10 
SELECT * FROM DBA_DB_LINKS WHERE DB_LINK='DBLINK_1';

OWNER DB_LINK    USERNAME   HOST       CREATED            HID SHA VAL INT
----- ---------- ---------- ---------- ------------------ --- --- --- ---
SYS   DBLINK_1   TEST1      ANGULAR    05-OCT-20          NO  NO  YES NO

 
(+) Connect to the database by 2 different sessions. I’m going to do it by means of the sqlplus

-- SESSION 1 

set sqlprompt "_user'@'SES_1 > "

-- SESSION 2 

set sqlprompt "_user'@'SES_2 > "

 
(+) In the first session perform the row update without issuing the commit/rollback command

SQL> set sqlprompt "_user'@'SES_1 > "

SYS@SES_1 > SELECT DISTINCT SID FROM V$MYSTAT;

       SID
----------
       271


SYS@SES_1 > UPDATE TEST1.TEST1 SET COL1=2 WHERE COL1=1;

1 row updated.

 
Now the single row of the TEST1 table is locked by the first session.

SET LINES 300 
SET PAGES 999
COL USERNAME FOR A10
COL SID FOR 99999999
COL SERIAL# FOR 9999999999
COL STATUS FOR A10
COL MACHINE FOR A20
COL OBJECT_NAME FOR A11
COL LOCKED_MODE FOR 9999

SELECT S.USERNAME,
       S.SID,
       S.SERIAL#, 
       S.STATUS,
       S.LAST_CALL_ET SECONDS_IN_WAIT_LASTSTATUS,
       S.MACHINE,
       DO.OWNER || '.' || DO.OBJECT_NAME OBJECT_NAME,
       LO.LOCKED_MODE
FROM
       V$SESSION S,
       V$LOCKED_OBJECT LO,
       DBA_OBJECTS DO
WHERE
       LO.SESSION_ID=S.SID AND
       LO.OBJECT_ID=DO.OBJECT_ID;

USERNAME         SID     SERIAL# STATUS     SECONDS_IN_WAIT_LASTSTATUS MACHINE              OBJECT_NAME LOCKED_MODE
---------- --------- ----------- ---------- -------------------------- -------------------- ----------- -----------
SYS              271       36510 INACTIVE                            8 dbpilot.net          TEST1.TEST1           3

 
(+) In the second session try to modify the same row of the TEST1 table through the DBLINK_1 dblink

SQL> set sqlprompt "_user'@'SES_2 > "

SYS@SES_2 > SELECT DISTINCT SID FROM V$MYSTAT;

       SID
----------
       266

SYS@SES_2 > SET TIMING ON
SYS@SES_2 > UPDATE TEST1.TEST1@DBLINK_1 SET COL1=3 WHERE COL1=1;

 
After the update is made the second session has hanged for a while.
While the second session is waiting a blocking session can be observed in the database

BLOCKING             WAITING              SECONDS_IN_WAIT ON_OBJECT       LOCKED_ROWID       WAITING_SQLID
-------------------- -------------------- --------------- --------------- ------------------ ---------------
271,36510(SYS)       24,50707(TEST1)                   60 TEST1.TEST1     AAAVIhAAHAAAAFeAAA b59c85h1pj9qp

SQL>
SQL>
SQL> /

no rows selected

 
After the second session has exceeded the value of the distributed_lock_timeout parameter it fails with the ORA-02049 error

SYS@SES_2 > UPDATE TEST1.TEST1@DBLINK_1 SET COL1=3 WHERE COL1=1;
UPDATE TEST1.TEST1@DBLINK_1 SET COL1=3 WHERE COL1=1
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock
ORA-02063: preceding line from DBLINK_1


Elapsed: 00:01:00.07

NOTE: Both the SECONDS_IN_WAIT column of the previous output for the blocking session and the Elapsed time of the second session are equal to 60 seconds.

 
 
SOLUTION
 
1. As a database administrator you can increase the distributed_lock_timeout parameter to a high value. A database must be bounced after that.

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

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