Monday, October 19, 2009

BizTalk SQL adapter deadlocking issues

A few months ago I had an assignment at a company that was having issues with their BizTalk installation. When examining their integration platform I noticed that the event log was filled with deadlock exceptions from the SQL adapter. It was the standard error message stating that a message sent to adapter "SQL" is suspended. The transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The vast amount of deadlocks that occured directly pointed me to the procedures that was being called by the receive locations.

In this particular flow, a number of receive locations where scheduled to poll a database for data every minute. Nothing unusual about this so far. When looking at the procedures, I saw that each one logged the time of execution to a log table used for debugging purposes. This is a valid practise, if it weren't for the fact that the logging was set up wrong. Instead of letting each procedure write it's own row with execution data, the table had one single row with a number of columns, one for each procedure.

So, only one row in the table, which all the procedures tried to write to. The chance of all procedures running at the same time and therefore trying to do an update on this table row at the same time is actually pretty slim if the procedures run fast enough. However, the procedures in this case did take a few seconds to run.

Adding to this is the fact that BizTalk will wrap the call in a serializable transaction meaning that the row will be locked throughout the entire execution of the procedure. The risk of another procedure trying to gain access to the table while its locked increases then quite a bit, resulting in the high volume of deadlocks I saw in the event log.

Since this was in production and a valid test environment was missing (shudder), I made certain that the log table wasn't used for anything (you never know) and then commented out the UPDATE statements in the procedures. Since the entire flow were to be rewritten from scratch, I saw no need to more or less do a transposition of the table to get the procedure timestamps on their own rows since the data were of no more use.

The result: The number of deadlocks were reduced with 97% and according to the server operators, the BizTalk/SQL Server installations now runs a lot better while we build the replacement to this flow.

What to remember from this is that it is both vital to know how BizTalk handles transactions in the SQL adapter and to also know the different table and locking hints available in SQL Server.

No comments:

Post a Comment