ORA-02049: timeout: distributed transaction waiting for lock

[annli]

Hi all,

I'm facing problems with inserting records to tables in remote database via public database link and I've got the following errors:

ORA-02049: timeout: distributed transaction waiting for lock
ORA-02063: preceding line from

Here is the information I've found about this error :

ORA-02049: timeout: distributed transaction waiting for lock

Cause: Exceeded INIT.ORA DISTRIBUTED_LOCK_TIMEOUT seconds waiting for lock

Action: Treat as a deadlock.

Does anyone find the same problem before? How can this be solved? The possible solution I've found is to increase the value of the parameter 'DISTRIBUTED_LOCK_TIMEOUT' (default is 60 sec).

However, the Oracle Server I'm using is Oracle 8.1.6 and the parameter is already obsolete. Could this parameter still be set? If yes, is it needed to restart my database for the parameter to be effective?

My database is production 24x7 database with heavy trasaction traffic, any suggestion on solving the problem without restarting the database?

Could anyone help? I would be appreciated for your kind help. Thanks.

[aarroyob]

Hi Ann,

Are the two databases in 8.1.6?

By the way be care with inserting through database link in 8.1.6 because there is a bug:

1. Select count(*) from table_name@db_link;

X rows

2. Insert into table_name
select count(*) from table_name@db_link;

Different number of rows that the first query

Regards

Angel

[annli]

Both of the databases are 8.1.6.

Actually, I face the problem when calling the stored procedure in Database A to process data in local table and then insert data to a table in Database B by making use of public synonym that points to the remote table via database link.

[anandkl]

probabily somebody else is also doing transaction on the table simoutaneously and that transaction must be holding the lock.Either commit or rollback that transaction or kill that process.

regards
K.L Ananda Kumar

[Raminder]

As the error message suggests, you have to treat it as a deadlock.
Find out which are the other transactions holding locks on the table. Kill the other transaction or convert lock type using dbms_lock package.