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

If your on Oracle 8.1.x or above also check doc id 1018919.102. Distributed_lock_timeout has become a hidden or more properly a deprecated parameter. Namely change the default at your own risk.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
Sent: Tuesday, October 21, 2003 12:40 PM To: Multiple recipients of list ORACLE-L lock

David,

Take a look at Note 19332.1, which explains the error and what to do next.

In short, the essence of the note is: The error comes if the time waited is mor than the value of the distributed_lock_timeout parameter. Even if you do a select from the remote database, it acquires a TX lock and that can wait. Increase the value of the timeout or, just use an exception handler on the commit statement to retry.

HTH. Arup Nanda

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, October 21, 2003 11:49 AM

> Hi List,
>
> We have a job that copies data in a table on a remote database to a local
> database through a database link. Here are the steps in the job:
>
> 1. truncate the table of t1 on the local database
> 2. insert into t1 select * from t2_at_remotedatabase
> 3. commit
>
> There are only 847 records in the table. The job completes in 1 sec
> normally. However, last Sunday we got ORA-02049: timeout: distributed
> transaction waiting for lock during commit process. As my understanding,
> the error comes from a DML statement that requires locks on a remote
> database can be blocked if another transaction own locks on the requested
> data. I'm pretty sure that there were no any activities on the remote
> database since the application was not open. Also I can see from the log
> file (see below) that 847 records were inserted into the t1 table on the
> local database. The error was generated during the commit process. Does
> any one have any comments? Thanks for any input.
>
> Here is the job log file:
> 847 rows created.
>
> commit
> *
> ERROR at line 1:
> ORA-02049: timeout: distributed transaction waiting for lock
>
> We are in Oracle 8.1.7.4 and SunOS 5.8. We take the default value for
> DISTRIBUTED_LOCK_TIMEOUT .
>
> Dave
>
> _________________________________________________________________
> Get a FREE computer virus scan online from McAfee.
> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: David Boyd
> INET: davidb158_at_hotmail.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: orarup_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Goulet, Dick
  INET: DGoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).