Imagine you have a SQL Receive location, that is pulling data from SQL on a regular interval. Also let’s say the receive location is pulling data every 5 seconds or so. There is a good chance, when BizTalk decides to throttle the system resources that multiple receive location queries (same query), will be running at the same time.
In fact I use the SQL receive location and my custom SQL send port (see blog on this, allows you to send XML data directly to SQL from BizTalk, and it is free to download, since the aggregator pattern is flawed.). So it is imperative that I can ensure deadlocks do not occur and dirty reads done dirt cheap.
One of the first things you can do when pulling data from SQL is change the isolation level, as the BizTalk SQL adapter has it’s own isolation level (Serializable), which loves to cause deadlocks. Here is a nice blog about it:
So here is our sample BizTalk SQL receive location:
Here is how I configure the SQL code to pull data without causing deadlocks.
CREATE PROCEDURE [dbo].[GetWorkflowRecord]
;WITH XMLNAMESPACES (DEFAULT ‘http://Workflow.Common.Schemas’)
First a more relaxed isolation level should be cool to pull data. So we choose Read Committed.
- READ COMMITTED: Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
- Ok, the next thing I do is use a READPAST hint when updating data, this ensures I do not acquire locks by other update statements. The advantage of this table hint is that, like NOLOCK, blocking does not occur when issuing queries. In addition, dirty reads are not present in READPAST because the hint will not return locked records. The downside of the statement is that, because records are not returned that are locked, it is very difficult to determine if your result set, or modification statement, includes all of the necessary rows. You may need to include some logic in your application to ensure that all of the necessary rows are eventually included.
- Since we using BizTalk receive location, it will eventually get records that a ReadPast forgot, so no hassle, here.
- Thirdly if my update has an Inner and an Outer query, I ensure the filter is placed in both, this avoids allot of locking issues when concurrent updates are running on the same data table. See the bold wfr_wfs_code filters on ‘SUC’ in the outer and inner query for the update.
- Lastly, ANY selects I am doing, I use a WITH (NOLOCK) to ensure my so innocent select statements do not acquire Shared Locks on SQL resources.
I chose the above query, as it has a bit of everything in it. I hope you find this as useful as I did. I have a good SQL guru sitting next to me at work, so thanks to Christodoulos Koukoulidis for all his SQL Geek tips, without him, I think I would have a dirty read done dirt cheap solution 🙂
Ensure you using TCP and not Shared memory as well for performance!