At Least Once Messaging, Atomic operations and SQL Server

In a NServiceBus system with at-least-once-messaging, there is always the chance that two handlers are executed with identical copies of a message. Handlers should be idempotent and protect against the same message producing different outcomes.

When we move deeper down to the repository layer, it raises interesting implications. If we have an Upsert style Stored Procedure (Update/Insert), then should this protect against high concurrency situations?

Take the excerpt below from a financial system:

IF NOT EXISTS (SELECT ReceiptId FROM [dbo].[PaymentLedger] WHERE ReceiptId=@ReceiptId)

      BEGIN

            INSERT INTO [dbo].[PaymentLedger]

The Select then Insert/Update semantic above is not protected against concurrent access and this has a [low] potential of happening with our messaging infrastructure.
If two handlers process a copy of the same message we have the potential to do two inserts into the PaymentLedger table.

This article discusses the problem and suggests a fix.
Even the merge command is not immune from this problem. It also demonstrates a good method for replicating the problem by scheduling a SQL command.

We can rely upon the caller above us to protect us from this scenario. However if we want the Stored Procedure to be explicitly safe against high-volume concurrency then the transaction really should be applied at the SQL level and not reply upon the client applying it.
We may not trust our client because

• may have not applied an idempotency check correctly
• our endpoints often don’t wrap the SQL in a transaction 
• or have disabled distributed transactions. 

An example fix is to modify the SQL as follows:

BEGIN TRAN 

IF NOT EXISTS (SELECT ReceiptId FROM [dbo].[PaymentLedger] WITH (UPDLOCK, SERIALIZABLE) WHERE ReceiptId=@ReceiptId)

BEGIN

      INSERT INTO [dbo].[PaymentLedger]

      ....

END

COMMIT TRAN

This change will affect performance very slightly by reducing the throughput of these inserts/updates.