Handle deadlock in polling Scenarii

Situation

In some scenarii, you must poll a datatable and you use WCF-SQL. This polling must update a column for  rows which are red. Due to the polling frequency you might encounter deadlock.

Consequence there is no more activity in your BizTalk Server, and you can loose some newly data which are never inserted

So you need to know if you have deadlock, here is the query that retrieve all status’ queries.

SELECT P.spid, P.ecid, P.status, P.loginame, P.hostname, P.blocked AS blk, D.name as dbname, P.cmd, P.request_id, T.text FROM sys.sysprocesses P CROSS APPLY sys.dm_exec_sql_text(P.sql_handle) T LEFT JOIN sys.sysdatabases D ON D.dbid = P.dbid

If you find suspended, then you’ve got DeadLock

The problem come from the fact that you try to insert new data while you try to update with biztalk already existing data.

Workaround

You must specify a deadlock victim. Most of time, we declare BizTalk as deadlock victim. Indeed, BizTalk have a frequence polling, if it can’t read this time, it could read the next time. But it’s just a general case.

To set a deadlock victim, in your SQL sentence you have to specify the deadlock priority. there are three levels

LOW

Specifies that the current session will be the deadlock victim if it is involved in a deadlock and other sessions involved in the deadlock chain have deadlock priority set to either NORMAL or HIGH or to an integer value greater than -5. The current session will not be the deadlock victim if the other sessions have deadlock priority set to an integer value less than -5. It also specifies that the current session is eligible to be the deadlock victim if another session has set deadlock priority set to LOW or to an integer value equal to -5.

NORMAL

Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to HIGH or to an integer value greater than 0, but will not be the deadlock victim if the other sessions have deadlock priority set to LOW or to an integer value less than 0. It also specifies that the current session is eligible to be the deadlock victim if another other session has set deadlock priority to NORMAL or to an integer value equal to 0. NORMAL is the default priority.

HIGH

Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to an integer value greater than 5, or is eligible to be the deadlock victim if another session has also set deadlock priority to HIGH or to an integer value equal to 5.

Source MSDN

For my problem, BizTalk will have a low deadlock priority and the program which insert data the hight deadlock priority. We never loose data again

Publicités

Cannot access a disposed object. Object name: ‘TransactionScope’.

The adapter « WCF-SQL » raised an error message. Details « System.ObjectDisposedException: Cannot access a disposed object.
Object name: ‘TransactionScope’.
at System.Transactions.TransactionScope.Complete()
at System.ServiceModel.Dispatcher.TransactionRpcFacet.ThreadLeave()
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage7(MessageRpc& rpc) ».

 

When using WCF-SQL adapter you can obtain this error by requesting a SQL server which doesn’t have MSDTC active.

 

To clear this issue configure MSDTC like your BizTalk server

The Messaging Engine failed to create the receive adapter « WCF-SQL ».

In deployment scenario, I met this error :

The Messaging Engine failed to create the receive adapter « WCF-SQL ».
InboundAssemblyPath: « NULL »

InboundTypeName: « Microsoft.Adapters.Sql.BizTalk.WcfBtsSqlReceiver, Microsoft.Adapters.Sql.BizTalk, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 »

Exception Details: « Could not load file or assembly ‘Microsoft.Adapters.Sql.BizTalk, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. Le fichier spécifié est introuvable. »

After quick search we found that the adapter pack x64 was not installed, install it will quick this issue

WCF-SQL and XML parameter

WCF-SQL can be used with a Stored Procedure that has an XML parameter.

The Xml parameter is converted in a string parameter in the generated code. So for passing an XML, you need to convert it in string with encoding.

It exists a simpler manner :

  • In your orchestration, only create your XML parameter as a message
  • Send it via your send port to WCF-SQL
  • Now in the send port click on configure
  • Click the Messages tab, and in the Outbound WCF message body section, choose the Template option.
  • In the XML text box, specify the template that will be used to construct the WCF message. By doing so, you create a message that conforms to the operation for the WCF-based SQL adapter
  • For the ADD_LAST_EMP_XML_INFO stored procedure, you must specify the following template:
    <ADD_LAST_EMP_XML_INFO xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
    <xml_info>
    <bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/> </xml_info>
    </ADD_LAST_EMP_XML_INFO>
  • The Tag encoding is very important. By specifying string, it’ll replace <> and other special caracter by their encoding (like &gt; …)

This method  is very powerfull, the only problem is you need to ensure that your schema is up to date manually

For more information see MSDN : http://msdn.microsoft.com/en-us/library/dd788497(v=bts.10).aspx

SQL-WCF port and OperationName

When you generate a binding with Add generated item ->consume WCF adapter and import it in BizTalk you could meet this issue when you try to test it :

The adapter failed to transmit message going to send port "WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_Custom" with URL "mssql://.//MeteorLogging?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.UnsupportedOperationException: The action "<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Operation Name="spBTS_WebUp_Meteor_EntLibLogging_WriteLog" Action="TypedProcedure/dbo/spBTS_WebUp_Meteor_EntLibLogging_WriteLog" />
</BtsActionMapping>" was not understood.

Lire la suite