DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

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

  1. #1
    Join Date
    Jul 2000
    Location
    Hong Kong
    Posts
    8

    Question

    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.
    Ann (aka Darknight)
    OCP 8i,9i

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    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

  3. #3
    Join Date
    Jul 2000
    Location
    Hong Kong
    Posts
    8

    Exclamation Re:ORA-02049: timeout: distributed transaction waiting for lock

    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.

    Ann (aka Darknight)
    OCP 8i,9i

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  5. #5
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width