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)
Database : 19.6.0.0.0
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)