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

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

Get the current value of distributed_lock_timeout initialization parameter
SQL> SHO USER
USER is "SYS"

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
TST19C

SQL> sho parameter distributed_lock_timeout

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout             integer     60
Create a DEMO user
SQL> SHO USER
USER is "SYS"

CREATE USER DEMO IDENTIFIED BY DEMO;
GRANT CONNECT, RESOURCE TO DEMO;
GRANT CREATE DATABASE LINK TO DEMO;
GRANT SELECT ON DBA_DB_LINKS TO DEMO;
GRANT SELECT ON V_$MYSTAT TO DEMO;
GRANT SELECT ON V_$DATABASE TO DEMO;
GRANT UNLIMITED TABLESPACE TO DEMO;
Create a dummy table
SQL> CONNECT DEMO/DEMO
Connected.
SQL> SHO USER
USER is "DEMO"

CREATE TABLE DEMO.DUMMY(COL1 NUMBER);
INSERT INTO DEMO.DUMMY VALUES(1);
COMMIT;
SQL> SELECT * FROM DEMO.DUMMY;

      COL1
----------
         1
Create a database link.
SQL> SHO USER
USER is "DEMO"

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
TST19C

-- Create the database link

SQL> CREATE DATABASE LINK DUMMY_DBLINK CONNECT TO DEMO IDENTIFIED BY DEMO USING 'TST19C';

Database link created.

-- Get details about the database link

SET LINES 300
SET PAGES 999
COL OWNER FOR A5
COL DB_LINK FOR A30 
COL USERNAME FOR A10 
COL HOST FOR A10 
SELECT OWNER,DB_LINK,USERNAME,HOST,CREATED FROM DBA_DB_LINKS WHERE DB_LINK LIKE 'DUMMY_DBLINK%';

OWNER DB_LINK                        USERNAME   HOST       CREATED
----- ------------------------------ ---------- ---------- ------------------
DEMO  DUMMY_DBLINK.VMWARE.LOCAL      DEMO       TST19C     08-MAY-22
Connect to database with 2 sessions by DEMO user
-- Session #1 

set sqlprompt "_user'@'SES_1 > "

-- Session #2

set sqlprompt "_user'@'SES_2 > "
In the first session update the row of DUMMY table
SQL> CONNECT DEMO/DEMO
Connected.
SQL> SHO USER
USER is "DEMO"

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

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

       SID
----------
       275

DEMO@SES_1 > UPDATE DEMO.DUMMY SET COL1=2 WHERE COL1=1;

1 row updated.

The first session is now holding the lock on the table. The following query gives details of all locks.

USERNAME       SID SERIAL# PID    STATUS          BLO BLOCKED_BY STATUS_ELAP_SEC CONNECTED_FROM       PROGRAM_NAME         OBJECT_NAME LOCKED_ROWID       LOCKED_MODE LOCKED_MODE_DESC
---------- ------- ------- ------ --------------- --- ---------- --------------- -------------------- -------------------- ----------- ------------------ ----------- ---------------------------------------------
DEMO           275   15271 11877  INACTIVE        NO                         125 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY                               3 ROW_X (SX): Row Exclusive Table Lock
In the second session update the row of DUMMY table through the dblink. The statement will hung for 60 seconds and then fails
SQL> CONNECT DEMO/DEMO
Connected.
SQL> SHOW USER
USER is "DEMO"

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

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

       SID
----------
       277

DEMO@SES_2 > SET TIMING ON
DEMO@SES_2 > UPDATE DEMO.DUMMY@DUMMY_DBLINK SET COL1=3 WHERE COL1=1;
UPDATE DEMO.DUMMY@DUMMY_DBLINK SET COL1=3 WHERE COL1=1
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock
ORA-02063: preceding line from DUMMY_DBLINK


Elapsed: 00:01:00.05

Immediately after the UPDATE statement was issued the second session creates a blocking session for a period of 60 seconds.

SQL> /

BLOCKING             BLOCKED              SECONDS_IN_WAIT MINUTES_IN_WAIT ON_OBJECT  ROWID_FOR_LOCKED_ROW LOCKED_SQLID
-------------------- -------------------- --------------- --------------- ---------- -------------------- ---------------
275,15271(DEMO)      42,573(DEMO)                      43               1 DEMO.DUMMY AAAStoAAHAAAAFcAAA   8jvqu0r8wd3xg

SQL> /

BLOCKING             BLOCKED              SECONDS_IN_WAIT MINUTES_IN_WAIT ON_OBJECT  ROWID_FOR_LOCKED_ROW LOCKED_SQLID
-------------------- -------------------- --------------- --------------- ---------- -------------------- ---------------
275,15271(DEMO)      42,573(DEMO)                      58               1 DEMO.DUMMY AAAStoAAHAAAAFcAAA   8jvqu0r8wd3xg

SQL> /

no rows selected
NOTE

In this demo the SID of the second session is 277 and the SID for the BLOCKED session is 42. The both SID are different because I created new connection through database link by the second session. If I hadn't used database link when updating the DEMO.DUMMY table both SID would have the same ID.

 

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

As this error is just another version of blocking session you can also fix it by terminating a blocking session in a remote database.
Killing any session in the remote database must be performed after a thorough review.

 

That's it. ORA-02049 has been successfully reproduced. Drop DEMO user
SQL> SHOW USER
USER is "SYS"

SQL> DROP USER DEMO CASCADE;

User dropped.

 

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