none
Deadlocks in BizTalk event viewer RRS feed

  • Question

  • I am getting below deadlock issue in BizTalk event viewer.

    The adapter failed to transmit message going to send port "CS.CVM.Send.DataLoad.MQ.CVM" with URL "mssql://153.2.209.66/P001/CVM". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 147) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    Server stack trace: 
       at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

    Exception rethrown at [0]: 
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)
    ClientConnectionId:c268f588-374b-4835-8fd5-e6ff7e304a31
    Error Number:50000,State:51,Class:13".

    Monday, August 8, 2016 6:02 PM

Answers

  • For clarity, this is not a problem with BizTalk Server or the SQL Adapter.

    The problem is because of the SQL code, SQL or Stored Procedure, running on the server.  Specifically, it is not written to execute concurrently.

    You have two options:

    1. Correct the SQL to support concurrency.  This is the correct solution.

    2. Ensure only one instance of Receive Location or Send Port is executing at the same time.

    • Proposed as answer by Angie Xu Sunday, August 14, 2016 8:54 AM
    • Marked as answer by Angie Xu Monday, August 15, 2016 2:06 AM
    Monday, August 8, 2016 6:18 PM

All replies

  • For clarity, this is not a problem with BizTalk Server or the SQL Adapter.

    The problem is because of the SQL code, SQL or Stored Procedure, running on the server.  Specifically, it is not written to execute concurrently.

    You have two options:

    1. Correct the SQL to support concurrency.  This is the correct solution.

    2. Ensure only one instance of Receive Location or Send Port is executing at the same time.

    • Proposed as answer by Angie Xu Sunday, August 14, 2016 8:54 AM
    • Marked as answer by Angie Xu Monday, August 15, 2016 2:06 AM
    Monday, August 8, 2016 6:18 PM
  • Hello,

    That's a SQL Server error so has nothing to do with your BizTalk process, other than the BizTalk application is making simultaneous requests.

    It will likely go a way if you set Ordered Delivery on the SQL Send Port.  With Ordered Delivery checked, BizTalk will perform the SQL Operations one at a time.

    The 'real' solution is to optimize the stored procedure to better handle concurrency.

    It could be that your stored procedure code is executing under the BizTalk server default transaction level serializable. Change it to read committed.

    You can set the transaction level by following statement in your stored proc.

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

    The best resource to look for when experiencing deadlock with using WCF-SQL see The BizTalk SQL Server Adapter isolation level post (SQL Level) or  MSDN Best Practices (BizTalk).

    To find out what is causing your Deadlock Events. You can do this in one of two ways, either run a SQL Server Profiler Trace to catch and record the Deadlock Event or you can enable some SQL Server Trace Flags that will record the details of the Deadlock Event to the SQL Server Error Log.

    For further reading take a look at:

    How to track down Deadlocks Using SQL Server Profiler

    Minimizing Deadlocks

    Detecting and Ending Deadlocks


    Rachit Sikroria (Microsoft Azure MVP)

    Tuesday, August 9, 2016 3:46 AM
    Moderator
  • Hi,

    1>Recycle the SQL host instance of the suspended message and resume it, you shall be good.

    2>If the above doesn't resolved the issue check on the SQL side for what is causing the deadlock.

    Also is this keeps happening you may want to rethink on how the BizTalk is configured for this work flow.

    Also are you using SQL adapter? you are better off using the WCF SQL adapter. There you have a PolledDataAvailableStatement option which in a way can be used to control the way you are interacting with SQL, a sort of throttling this way minimizing the chance of deadlock.


    Regards Pushpendra K Singh



    Wednesday, August 10, 2016 7:33 PM