BizTalk SQL Receive Location – Deadlocks, Dirty Reads and Isolation Levels

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 🙂

Ensure you using TCP and not Shared memory as well for performance!

Cheers

Uncategorized

Romiko Derbynew

BizTalk SQL Receive Location – Deadlocks, Dirty Reads and Isolation Levels

Leave a Reply Cancel reply

Share this:

Like this:

Related

Leave a Reply Cancel reply