BizTalk SQL Adapter Advice Requested
So I may be demonstrating my SQL Server ignorance, but I need some advice from you all.
Let's say I have the scenario I've drawn out below:
That is, I have the SQL adapter running on two BizTalk Servers, each executing a stored procedure every 2 seconds. That procedure needs to return them the TOP50 rows in a defined table. The problem that two of my customers have bumped into, and to which I don't feel I've given a rock-star answer for, centers around making sure that one SQL adapter doesn't pull the same data that the other adapter has already pulled. Now, of course you have to change a flag after you've read the data, to make sure it doesn't get polled again, but let's say that the adapters get into a cycle where one polls for data less than a second after another. If the first adapter hasn't switched the flag yet, the second adapter may pull that data again.
One possible answer is to create a transaction in the stored procedure which contains both the SELECT and UPDATE commands. However, this could potentially introduce table locking issues and prevent additional data from being inserted into said table. So, I'm not in love with that solution.
So for those of you who are either (a) SQL gurus, or (b) successful implementers of this scenario, what thoughts do you have? Maybe I'm just brain-cramping for the holidays, but I still think this is a useful discussion.