Intermittently getting error ORA-02049: timeout: distributed transaction waiting for lock — oracle-tech

    Forum Stats

  • 3,715,997 Users
  • 2,242,925 Discussions
  • 7,845,725 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Intermittently getting error ORA-02049: timeout: distributed transaction waiting for lock

ppratapwar
ppratapwar Member Posts: 7 Blue Ribbon
edited July 2019 in ODP.NET

We are Intermittently getting error 'ORA-02049: timeout: distributed transaction waiting for lock.

The application is just trying to do 2 transactions in the transaction scope for a parent and child tables.

Example - Table 1 is Parent Table AND Table 2 is Child Table

The application inserts the record in Table 1 and in the same transaction scope application tries to add the related record in the child table.

This is a really simple code. We tried this test when no one was accessing the database. Surprisingly, it is an intermittent issue.

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited July 2019
    ppratapwar wrote:We are Intermittently getting error 'ORA-02049: timeout: distributed transaction waiting for lock.The application is just trying to do 2 transactions in the transaction scope for a parent and child tables. Example - Table 1 is Parent Table AND Table 2 is Child TableThe application inserts the record in Table 1 and in the same transaction scope application tries to add the related record in the child table.This is a really simple code. We tried this test when no one was accessing the database. Surprisingly, it is an intermittent issue.

    Where does the transaction become distributed? It seems like both tables are on the same database?

  • ppratapwar
    ppratapwar Member Posts: 7 Blue Ribbon
    edited July 2019

    Yes, both tables are in the same database. But Insert in tables is happening from the C# application which opens the transaction scope and calls 2 stored procedure to insert in 2 tables in the same transaction scope because of which it becomes distributed.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited July 2019
    ppratapwar wrote:Yes, both tables are in the same database. But Insert in tables is happening from the C# application which opens the transaction scope and calls 2 stored procedure to insert in 2 tables in the same transaction scope because of which it becomes distributed.

    That should be just a normal every day multi statement transaction - it only needs one site to see the commit so it doesn’t need to be distributed. Whatever you’re doing to make it distributed - don’t do it.

    That said, I wouldn’t expect an insert to wait on a lock unless it was trying to insert the same unique Key of an uncommitted (Different transaction) row (you are using sequences right?) or if the parent Table row for the foreign key was uncommitted in another transaction.

    Alex Keh-Oracle
  • ppratapwar
    ppratapwar Member Posts: 7 Blue Ribbon
    edited July 2019

    Yes, it is normal everyday multi-statement transactions. That's why I am surprised to see the issue.

    Yes, we are using the sequences to create the PK. I have tried to isolate the environment to have only my transaction during the period of time then also I am getting the error.

    If I change the code to make independent transactions for parent and child and have delete statement if any issue occurs during a child transaction then everything works fine. But this is not the correct way to implement the functionality.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited July 2019
    ppratapwar wrote:Yes, it is normal everyday multi-statement transactions. That's why I am surprised to see the issue.Yes, we are using the sequences to create the PK. I have tried to isolate the environment to have only my transaction during the period of time then also I am getting the error.If I change the code to make independent transactions for parent and child and have delete statement if any issue occurs during a child transaction then everything works fine. But this is not the correct way to implement the functionality. 

    Can you share the code you’re running? Or at least a short demo that could replicate the problem?

  • ppratapwar
    ppratapwar Member Posts: 7 Blue Ribbon
    edited July 2019

    Here is the code -

                    using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required))

                    {

                        ParentDAL parentDAL = new parentDAL();

                        indexId = parentDAL.GenerateUMCID(parentInfo.parent.parentTypeCode);

                        parentInfo.parent.indexId = indexId.ToString();

                        //This statement calls the function which in turn calls the stored procedure for parent insert, simple insert into statement

                        newparentID = parentDAL.Insertparent(parentInfo.parent);

                        ChildDAL childDAL = new ChildDAL();

                        child.parentID = newparentID;

                        //This statement calls the function which in turn calls the stored procedure for child insert, simple insert into statement

                        childDAL.InsertChild(child);

                        transactionScope.Complete();

                   }

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited July 2019
    ppratapwar wrote:Here is the code - using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required)) { ParentDAL parentDAL = new parentDAL(); indexId = parentDAL.GenerateUMCID(parentInfo.parent.parentTypeCode); parentInfo.parent.indexId = indexId.ToString(); //This statement calls the function which in turn calls the stored procedure for parent insert, simple insert into statement newparentID = parentDAL.Insertparent(parentInfo.parent);  ChildDAL childDAL = new ChildDAL(); child.parentID = newparentID; //This statement calls the function which in turn calls the stored procedure for child insert, simple insert into statement childDAL.InsertChild(child); transactionScope.Complete(); }

    Instead of using TransactionScope to start a distributed transaction (it has no idea how many databases will be used), use OracleTransaction (Which is done at a connection level so won’t be distributed unless the DB deems it to be).

    https://docs.oracle.com/database/121/ODPNT/OracleTransactionClass.htm#ODPNT2214

  • ppratapwar
    ppratapwar Member Posts: 7 Blue Ribbon
    edited July 2019

    The complete application framework is defined in such a way that we are using the transaction scope at Business Object Layer and the connection created using the Data Access Layer. This code is working fine for years and is now getting an intermittent error. I just need to find out the root cause of the issue. If this was working from the years why it is now it starts throwing the exception that to only in the intermittent call, not all calls.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited July 2019
    ppratapwar wrote:The complete application framework is defined in such a way that we are using the transaction scope at Business Object Layer and the connection created using the Data Access Layer. This code is working fine for years and is now getting an intermittent error. I just need to find out the root cause of the issue. If this was working from the years why it is now it starts throwing the exception that to only in the intermittent call, not all calls.

    Your framework is using distributed transactions, it may have been able to cope like that for years but without it you wouldn’t have this problem.

    Distributed transactions are only introducing the lock timeout, so you could argue that the second insert being blocked temporarily is the problem. If you have paid for the diagnostics pack licence then you can find out why it was blocked temporarily by querying v$active_session_history for the problem period.

Sign In or Register to comment.