BizTalk - getting a port that reads twice with DB

Question

My receive port is sqlBinding and typed polling. It calls SP to retrieve the record and, based on the filter condition, the corresponding start of the orchestration. The BizTalk group consists of 2 servers; thus 2 ReceiveHostInstances. If both host instances are running - at one point the same request is read twice - causing a duplicate at the end of the receivers. But why does the re-read port read the same record more than once? A proc that reads updates to a record and updates it so that it is not fixed again.

I observed this scenario when submitting 10 requests; get the port read 11 times and 11 orchestrations have begun.

I tried the same request (10 requests) with one host (as in my Dev), only 10 is displayed on receipt. Any hints?

Answer 1

Quick answer: you have two options for solving this problem:

• Correct the stored procedure so that it works correctly in parallel situations.
• Place the SQL Query Recipient Processor in the BizTalk Cluster Node.

The following is an explanation of what is happening, and below that I give implementation details to fix the problem:

Description

, BizTalk ( , , , , ).

.

- , . , , .

, , - , - SQL . BizTalk , SQL, .

, , , , .

, - :

Select * From Record 
Where Status = 'Unread'

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

, select .

, :

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 , , newid()

BizTalk , . Kent Weare.

, , , .

SQL- , , .

BizTalk , , , . SQL, .