I am calling Oracle procedure using Biztalk WCf-oracle adapter using composite operation to call same Stored procedure repeatedly.

I am randomly getting error "A message received by adapter "OracleDBAdapter" on receive location "Unknown " with URI "Unknown URL " is suspended.  Error details: Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-02049: timeout: distributed transaction waiting for lock ---> Oracle.DataAccess.Client.OracleException: ORA-02049: timeout: distributed transaction waiting for lock" .

Since it is multiple call to the same procedure, I am suspecting a deadlock.  How can i make sure that repeated calls of the stored procedure is always sequential to avoid any possible deadlock situation.

Below is the message i an sending to send port:

<ns1:Request xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/PAYROLLDBA/Package/PKG_RSD"
             xmlns:ns1="http://PKG_UPDATEPROC_Composite">
  <ns0:UPDATEPROC>
    <ns0:Param1>Value1</ns0:Param1>
    <ns0:Param2>Value2 </ns0:Param2>
    <ns0:Param3>Value3</ns0:Param3>
    <ns0:Param4>Value4</ns0:Param4>
    <ns0:Param5>1</ns0:Param5>
  </ns0:UPDATEPROC>
  <ns0:UPDATEPROC>
    <ns0:Param1>1_Value1</ns0:Param1>
    <ns0:Param2>1_Value2 </ns0:Param2>
    <ns0:Param3>1_Value3</ns0:Param3>
    <ns0:Param4>1_Value4</ns0:Param4>
    <ns0:Param5>1_1</ns0:Param5>
  </ns0:UPDATEPROC>
</ns1:Request>

Regards,

Umair Ahmed

Maybe your timeout is too low for the operations you are performing. You can try increasing the transaction timeout value. http://msdn.microsoft.com/en-us/library/dd787944(BTS.10).aspx

Refer: https://social.msdn.microsoft.com/Forums/en-US/83a444af-fd0c-428d-815c-074e213f138f/biztalk-wcforacle-adapter-error-ora02049-timeout-distributed-transaction-waiting-for-lock?forum=biztalkr2adapters

Umair,

Please try below ,

On the WCF-Custom Transport Properties / Bindings please change the UseAmbientTransaction to False.

The stored procedure was slower because of Full table scan. After creating Index and optimizing pl/sql the error "ORA-02049: timeout: distributed transaction waiting for lock" stopped.

but again. How can i make sure sequential calls to repeated stored procedure calls while using composite schema?

Umair ,

you can try batching the incoming message in Pipeline and pass through a temp folder or direct to message box. Once done you can have a singleton Sequential pattern working with the de-batched  message

----------------------------------------------------------------------------------------------------------

rohit

Maybe your timeout is too low for the operations you are performing. You can try increasing the transaction timeout value. http://msdn.microsoft.com/en-us/library/dd787944(BTS.10).aspx

Refer: https://social.msdn.microsoft.com/Forums/en-US/83a444af-fd0c-428d-815c-074e213f138f/biztalk-wcforacle-adapter-error-ora02049-timeout-distributed-transaction-waiting-for-lock?forum=biztalkr2adapters

Maybe your timeout is too low for the operations you are performing. You can try increasing the transaction timeout value. http://msdn.microsoft.com/en-us/library/dd787944(BTS.10).aspx

Refer: https://social.msdn.microsoft.com/Forums/en-US/83a444af-fd0c-428d-815c-074e213f138f/biztalk-wcforacle-adapter-error-ora02049-timeout-distributed-transaction-waiting-for-lock?forum=biztalkr2adapters

Guys, I am getting the error again. My Oracle developers is noticing many sessions from BizTalk and he is suspecting this as reason of slowness and eventually locking the table.

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

Hi Umair,

I would suggest to check the issue with Oracle DB administrator .It is the Oracle which is getting locked with Biz Talk request  . Oracle DB admin might help you better in this.

http://www.dba-oracle.com/e_ora_02049_timeout_distributed_transaction.htm

Thanks

Abhishek

• Marked as answer by Umair Ahmed 1 hour 11 minutes ago

Umair,

I am agree with Abhishek, and I want to add some details here,

Please look into below related threads and some useful article,

WCF-Custom for Oracle

Oracle Adapter Transaction Time Out question

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

ORA-02049: timeout: distributed transaction waiting for lock

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

Hi Umair,

I would suggest to check the issue with Oracle DB administrator .It is the Oracle which is getting locked with Biz Talk request  . Oracle DB admin might help you better in this.

http://www.dba-oracle.com/e_ora_02049_timeout_distributed_transaction.htm

Thanks

Abhishek

• Marked as answer by Umair Ahmed Monday, August 31, 2015 5:52 AM

Hi Umair,

I would suggest to check the issue with Oracle DB administrator .It is the Oracle which is getting locked with Biz Talk request  . Oracle DB admin might help you better in this.

http://www.dba-oracle.com/e_ora_02049_timeout_distributed_transaction.htm

Thanks

Abhishek

• Marked as answer by Umair Ahmed Monday, August 31, 2015 5:52 AM

Thanks for the help.

The DBA done some fragmentation and the issue has resloved.

Many Thanks again.

Regards,

Umair Ahmed.