BizTalk – read twice from DB receive port

My receive port is sqlbinding and polling type It calls SP to obtain records and starts the corresponding business process according to the filtering conditions BizTalk group consists of 2 servers; Therefore, 2 receivehostinstances If both host instances are running - at some point, the same request is read twice - causing duplication at the receiver However, why does the reaction port read the same record more than once? Read the update record and update its proc so that it will not be deleted again

I observed this when I submitted 10 requests; The receiving port is read 11 times and 11 choreography starts

I tried the same (10 requests) with a host (in my development), and the reception only showed 10 Any leads?

Solution

The quick answer is that you have two ways to solve this problem:

>Repair stored procedures to run properly in case of concurrency. > Place the SQL polling receive handler in the BizTalk cluster host

The following is an explanation of what is happening, and the implementation details of solving the problem are provided below:

explain

This is due to the way BizTalk receives locations when running on multiple host instances (that is, the receive handler of the adapter specified in the receive location runs on a host with multiple host instances)

In this case, both host instances will run their receive handlers

This is usually not a problem - most receive adapters can manage it and provide you with the behavior you want For example, the file adapter locks the file when reading it, preventing double reading

This is the main problem, and that's what you see - when polling SQL receive locations are hitting stored procedures In this case, BizTalk has no choice but to trust the SQL procedure to provide the correct results

It's hard to say that you don't see your program, but the way you query records does not guarantee unique reading

Maybe you have something similar:

Select * From Record 
Where Status = 'Unread'

Update Record 
Set Status = 'Read'
Where Status = 'Unread'

The above procedure can provide duplicate records, because between select and update, another call of select can sneak in and select records that have not been updated

Implement solutions

Repair procedure

A simple fix to this process is to first update with a unique ID:

Update Record 
Set UpdateId = @@SPID,Status = 'Reading'
Where Status = 'Unread'

Select * From Record
Where UpdateId = @@SPID
And Status = 'Reading'

Update Record
Set Status = 'Read'
Where UpdateId = @@SPID
And Status = 'Reading'

@@SPID should be unique, but if it proves not, you can use newid ()

Use cluster host

In the BizTalk Server Administration Console, when creating a new host, you can specify that the host is clustered Details of performing this operation are in this post by Kent wear

Basically, you can create hosts normally. There are host instances on each server, then right-click the host and select cluster

Then, create an SQL receive handler for polling working under the host and use it at the receive location

BizTalk cluster host ensures that all projects that are members of this host can only run on one host instance at a time This will include your SQL receive location, so you won't have any race conditions when calling your procedure