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.