Which approach to concurrency safe upserts is best for table-valued input if MERGE is forbidden?
5
votes
2
answers
796
views
[This classic concurrency safety article](https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/) is clearly designed for only upserting one row at a time. In my situation, I have a table-valued input and I want to upsert each row in a concurrency safe way. I know that this isn't always possible, but I want to get as close as possible.
MERGE
seems like a natural solution to it, but I distrust it and truly am in a situation where it is [bug prone](https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-to-use-merge/) . The remaining two approaches in Michael J. Swart's article are:
1) _Inside a Transaction With Lock Hints (Update More Common)_
CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS
SET XACT_ABORT ON;
BEGIN TRAN
UPDATE TOP (1) dbo.AccountDetails WITH (UPDLOCK, SERIALIZABLE)
SET Etc = @Etc
WHERE Email = @Email;
IF (@@ROWCOUNT = 0)
BEGIN
INSERT dbo.AccountDetails ( Email, Etc )
VALUES ( @Email, @Etc );
END
COMMIT
2) _Inside a Transaction With Lock Hints (Insert More Common)_
CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS
SET XACT_ABORT ON;
BEGIN TRAN
INSERT dbo.AccountDetails ( Email, Etc )
SELECT @Email, @Etc
WHERE NOT EXISTS (
SELECT *
FROM dbo.AccountDetails WITH (UPDLOCK, SERIALIZABLE)
WHERE Email = @Email
)
IF (@@ROWCOUNT = 0)
BEGIN
UPDATE TOP (1) dbo.AccountDetails
SET Etc = @Etc
WHERE Email = @Email;
END
COMMIT
I could adapt either of these to use table variables (e.g. I suspect that IF (@@ROWCOUNT = 0)
needs totally removing), but **does the usage of a table-valued input make it obvious that we should prefer either the first or second solution?** If not, then on what basis should the decision be made?
Asked by J. Mini
(1237 rep)
Apr 11, 2025, 11:15 PM
Last activity: Apr 13, 2025, 10:35 AM
Last activity: Apr 13, 2025, 10:35 AM