none
is biztalk uses its own transaction RRS feed

  • Question

  • Hi,

    I am frequently getting deadlocks

    I want to know whether biztalk will call procedure  with its own transaction

    Thursday, June 16, 2016 11:57 AM

Answers

  • Well, the answer to you specific question is yes, but it doesn't really matter because that transaction only covers the operation between the client and the server.

    I want to be vary clear about this.  Deadlocks are not really related to BizTalk Server, the SQL Adapter or anything else on the client side, with one exception.  That one exception is having multiple active Receive Locations, but even then, it's really not the clients fault.

    Deadlock situations are always the result of statement flow within and between other operations executing on the server.  They can be alleviated, or even eliminated, by setting appropriate isolation and transnational levels within the Stored Procedure code itself.  Basically, you have to make the Stored Procedure support concurrency itself, then deadlocks will not be an issue for any client, including BizTalk.

    Sunday, June 19, 2016 12:24 PM
    Moderator
  • In that case, you have to carefully set Transaction Isolation level within the stored proc itself to avoid chances of deadlocking. This is what I shared earlier-

    The old SQL adapter executes stored proc in context of DTC transaction that is started on BizTalk server. You have to implement stored procs in a way that chances of deadlocks are reduced with the correct Transaction Isolation level. This is very relevant in your case-

    By default BizTalk wraps all calls made with the SQL Adapter in a transaction with an isolation level of SERIALIZABLE. This level is much more aggressive with resource locking than the default level of READ COMMITTED. As a result it is important to specify the Transaction Isolation Level for the transaction within the procedure.

    Refer these-

    http://adambowron.blogspot.in/2007/02/avoid-deadlocks-using-sql-adapter-and.html (refer the sample stored proc here, your stored proc needs to set TRANSACTION ISOLATION LEVEL similarly)

    Also refer this, great resource on "Best Practices for the SQL Adapter":

    https://msdn.microsoft.com/en-us/library/cc507804(v=bts.10).aspx

    Developing stored procedures with this concurrency in mind is important to minimizing deadlocks. The previous sample stored procedure avoids deadlocks by using SQL “hints.” (See the WITH statement in the previous code.)

    For more information about hints, see http://go.microsoft.com/fwlink/?LinkId=88860.

    For more information about WITH statements, seehttp://go.microsoft.com/fwlink/?LinkId=88861.

    An alternative concurrency consideration is to use Application Lock in the stored procedure itself. As the name implies, a SQL Application Lock places a lock on an application resource. Using an Application Lock at the top of the stored procedure means that there is only one stored procedure that is executing at a time.

    For more information about SQL Application Locks, seehttp://go.microsoft.com/fwlink/?LinkId=88862.

    Deadlocks MAY happen when concurrent requests happen - make sure that there are no other clients making calls to those SQL tables, etc. at the same time BizTalk is. To be sure you/owner of the database needs to analyze what is causing the deadlocks.

    Refer-

    http://www.dnnsoftware.com/wiki/analyzing-deadlocks-with-sql-server-profiler


    Thanks Arindam







    Thursday, June 16, 2016 1:22 PM
    Moderator

All replies

  • Hi Dinesh

    Where are you getting deadlocks? On an adapter?

    Most of BizTalk transactions are DTC transactions. The transaction can be initiated on BizTalk machine if BizTalk is sending a message via transactional adapter, or interacting with MsgBox. Sometimes, an external transaction can also flow to BizTalk, for example a transactional adapter like MQSeries submits a message to BizTalk. Whenever a transaction crosses a machine boundary, it gets promoted to a DTC transaction, irrespective of who initiated the transaction.


    Thanks Arindam


    Thursday, June 16, 2016 12:14 PM
    Moderator
  • no not in adapters, dead locks on clients database.

    I am calling a select procedure this procedure giving dead locks,

    client complins that biztalk is giving deadlocks,

    i want to know whether it is biztalk transaction  or what?

    also  want to know is biztalk calls select/insert procedure with its own transaction or use the database transaction


    • Edited by DineshNad Thursday, June 16, 2016 12:36 PM
    Thursday, June 16, 2016 12:25 PM
  • Is the database on SQL Server? If useAmbientTransaction = true in the WCF-SQL adapter, then BizTalk flows it's MSDTC transaction to the database. If you are only doing SELECT operation(s) within the stored proc and no updates, set this property to False to avoid deadlocks.

    Read this to understand this better-

    https://social.msdn.microsoft.com/Forums/en-US/a4e46ed1-2bb6-4770-99cc-b6249a04e6d8/wcfsql-binding-ambient-transaction-how-to-turn-off-msdtc?forum=biztalkgeneral

    If useAmbientTransaction = false, the stored proc transaction is used (if the stored proc is using one).

    Are the client stored procs also accessed by other systems or only BizTalk?


    Thanks Arindam






    Thursday, June 16, 2016 12:42 PM
    Moderator
  • i am just using sql adaptor
    Thursday, June 16, 2016 12:55 PM
  • Which version of BizTalk is this? Currently, this adapter is not supported.

    The old SQL adapter executes stored proc in context of DTC transaction that is started on BizTalk server. You have to implement stored procs in a way that chances of deadlocks are reduced with the correct Transaction Isolation level. This is very relevant in your case-

    By default BizTalk wraps all calls made with the SQL Adapter in a transaction with an isolation level of SERIALIZABLE. This level is much more aggressive with resource locking than the default level of READ COMMITTED. As a result it is important to specify the Transaction Isolation Level for the transaction within the procedure.

    Refer these-

    http://adambowron.blogspot.in/2007/02/avoid-deadlocks-using-sql-adapter-and.html (refer the sample stored proc here, your stored proc needs to set TRANSACTION ISOLATION LEVEL similarly)

    https://msdn.microsoft.com/en-us/library/cc507804(v=bts.10).aspx


    Thanks Arindam



    Thursday, June 16, 2016 1:13 PM
    Moderator
  • if i use wcf -sql also i may get dead locks , because i cant set useAmbientTransaction to false(because my select procedure also have update statements )
    then what is the solution(is it biztalk raising deadlocks or what)
    • Edited by DineshNad Thursday, June 16, 2016 1:17 PM
    Thursday, June 16, 2016 1:17 PM
  • In that case, you have to carefully set Transaction Isolation level within the stored proc itself to avoid chances of deadlocking. This is what I shared earlier-

    The old SQL adapter executes stored proc in context of DTC transaction that is started on BizTalk server. You have to implement stored procs in a way that chances of deadlocks are reduced with the correct Transaction Isolation level. This is very relevant in your case-

    By default BizTalk wraps all calls made with the SQL Adapter in a transaction with an isolation level of SERIALIZABLE. This level is much more aggressive with resource locking than the default level of READ COMMITTED. As a result it is important to specify the Transaction Isolation Level for the transaction within the procedure.

    Refer these-

    http://adambowron.blogspot.in/2007/02/avoid-deadlocks-using-sql-adapter-and.html (refer the sample stored proc here, your stored proc needs to set TRANSACTION ISOLATION LEVEL similarly)

    Also refer this, great resource on "Best Practices for the SQL Adapter":

    https://msdn.microsoft.com/en-us/library/cc507804(v=bts.10).aspx

    Developing stored procedures with this concurrency in mind is important to minimizing deadlocks. The previous sample stored procedure avoids deadlocks by using SQL “hints.” (See the WITH statement in the previous code.)

    For more information about hints, see http://go.microsoft.com/fwlink/?LinkId=88860.

    For more information about WITH statements, seehttp://go.microsoft.com/fwlink/?LinkId=88861.

    An alternative concurrency consideration is to use Application Lock in the stored procedure itself. As the name implies, a SQL Application Lock places a lock on an application resource. Using an Application Lock at the top of the stored procedure means that there is only one stored procedure that is executing at a time.

    For more information about SQL Application Locks, seehttp://go.microsoft.com/fwlink/?LinkId=88862.

    Deadlocks MAY happen when concurrent requests happen - make sure that there are no other clients making calls to those SQL tables, etc. at the same time BizTalk is. To be sure you/owner of the database needs to analyze what is causing the deadlocks.

    Refer-

    http://www.dnnsoftware.com/wiki/analyzing-deadlocks-with-sql-server-profiler


    Thanks Arindam







    Thursday, June 16, 2016 1:22 PM
    Moderator
  • Well, the answer to you specific question is yes, but it doesn't really matter because that transaction only covers the operation between the client and the server.

    I want to be vary clear about this.  Deadlocks are not really related to BizTalk Server, the SQL Adapter or anything else on the client side, with one exception.  That one exception is having multiple active Receive Locations, but even then, it's really not the clients fault.

    Deadlock situations are always the result of statement flow within and between other operations executing on the server.  They can be alleviated, or even eliminated, by setting appropriate isolation and transnational levels within the Stored Procedure code itself.  Basically, you have to make the Stored Procedure support concurrency itself, then deadlocks will not be an issue for any client, including BizTalk.

    Sunday, June 19, 2016 12:24 PM
    Moderator