Hi Folks,
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:
http://geekswithblogs.net/gwiele/archive/2004/11/25/15974.aspx
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]
@BatchSize int,
@Stage varchar(3) = null
AS
BEGIN
-- TO OVERRIDE THE BIZTALK ADPATER ISOLATION LEVEL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @ids TABLE (id BIGINT PRIMARY KEY CLUSTERED, wfs_Code_Previous VARCHAR(3)) UPDATE dbo.wfr_WorkflowRecord
SET wfr_wfs_code = 'PRO'
, wfr_Username = system_user
OUTPUT inserted.wfr_id
, deleted.wfr_wfs_Code INTO @ids
FROM dbo.wfr_WorkflowRecord WITH (READPAST) --do not update records that are read by other processes
JOIN
(
SELECT TOP(@BatchSize) wfr_id AS tmp_wfr_id
FROM fee_Feed
INNER JOIN dbo.wfr_WorkflowRecord ON wfr_fee_id = fee_id
LEFT OUTER JOIN dbo.imp_ImportBatch ON imp_id = wfr_imp_id
WHERE wfr_wfs_code = ('SUC')
AND wfr_Batch is null
AND wfr_stg_code = @Stage
AND ISNULL(imp_Finished, 1) = 1 --Only pick up records for a finished import batch (or no batch)
AND fee_isActive = 1 --Only pick up records that are activated
ORDER BY fee_Priority
) tmp ON wfr_id = tmp_wfr_id
WHERE wfr_wfs_code = ('SUC')
AND wfr_Batch is null
AND wfr_stg_code = @Stage ;WITH XMLNAMESPACES (DEFAULT 'http://Workflow.Common.Schemas')
SELECT wfr_wfs_Code AS "WorkflowData/Status"
, wfs_Code_Previous AS "WorkflowData/PreviousStatus"
, wfr_stg_Code AS "WorkflowData/Stage"
, rou_Name AS "WorkflowData/Route"
, '' AS "WorkflowData/Error"
, wrd_XMLData AS "MMITData"
FROM dbo.wfr_WorkflowRecord (NOLOCK) wfr
INNER JOIN dbo.wrd_WorkflowRecordData (NOLOCK) ON wrd_id = wfr_wrd_id
INNER JOIN dbo.fee_Feed (NOLOCK) ON fee_id = wfr_fee_id
INNER JOIN dbo.cfs_ConfigurationSet (NOLOCK) ON cfs_code = fee_cfs_code
INNER JOIN dbo.rou_Route (NOLOCK) ON rou_code = cfs_rou_code
INNER JOIN @ids ON id = wfr_id
FOR XML PATH('WorkflowRecord') UPDATE dbo.wfr_WorkflowRecord
SET wfr_stg_code = 'BIZ'
FROM @Ids
WHERE wfr_id = id
END |
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 :)
Cheers