This article tackles how to implement safe UPSERTs from concurrency point of view. A previous blog has presented a common solution for UPSERT and it has demonstrated that this solution isn’t safe. Current blog specifies common solutions for UPSERT operations with a single row.
To discuss these solutions I will use the same table dbo.Customer:
DROP TABLE dbo.Customer;
CREATE TABLE dbo.Customer (
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
Email NVARCHAR(100) NOT NULL,
Note NVARCHAR(50) NULL,
OrdersCount INT NOT NULL DEFAULT (1)
);
GO
If we want to insert a new customer then the identifier used for customers is the email address. If there is no email address stored within dbo.Customer then we need to insert a new customer with 1 order. Otherwise (the customer’s email already exists) we need to increment by 1 the OrdersCount column.
From this point of view we need also an unique index on Email column:
ON dbo.Customer(Email);
This index has two important roles:
- It prevents duplicated emails and
- It helps those queries which tries to find customers using the email address as identifier (WHERE Email = constant / @variable).
Solutions for a single row
– Parameters
DECLARE @Email NVARCHAR(100)
SET @Email = '[email protected]';
– End of Parameters
BEGIN TRANSACTION;
IF EXISTS(SELECT * FROM dbo.Customer WITH(XLOCK, HOLDLOCK) WHERE Email = @Email)
BEGIN
UPDATE dbo.Customer
SET OrdersCount = OrdersCount + 1
WHERE Email = @Email;
END
ELSE
BEGIN
INSERT dbo.Customer(Email)
VALUES (@Email) – OrdersCount = Default 1
END
COMMIT; – Use ROLLBACK for testing
GO
– Solution #2: MERGE
– Parameters
DECLARE @Email NVARCHAR(100)
SET @Email = '[email protected]';
– End of Parameters
BEGIN TRANSACTION;
MERGE dbo.Customer WITH(HOLDLOCK) trg
USING (VALUES(@Email)) src(Email) ON trg.Email = src.Email
WHEN MATCHED
THEN
UPDATE
SET OrdersCount = trg.OrdersCount + 1
WHEN NOT MATCHED BY TARGET
THEN
INSERT (Email)
VALUES (@Email);
COMMIT;
GO
– Solution #3: UPDATE + INSERT
– Parameters
DECLARE @Email NVARCHAR(100)
– End of Parameters
SET @Email = '[email protected]';
BEGIN TRANSACTION;
UPDATE trg
SET OrdersCount = OrdersCount + 1
FROM dbo.Customer trg WITH(HOLDLOCK)
WHERE trg.Email = @Email;
IF @@ROWCOUNT = 0
INSERT dbo.Customer(Email)
VALUES (@Email);
COMMIT;
GO
Solutions for multiple rows
– Parameters
DECLARE @CustomerEmail TABLE( Email NVARCHAR(100) NOT NULL PRIMARY KEY );
INSERT @CustomerEmail (Email) VALUES (N'[email protected]');
INSERT @CustomerEmail (Email) VALUES (N'[email protected]');
INSERT @CustomerEmail (Email) VALUES (N'[email protected]');
– End of Parameters
DECLARE @CustomerEmailForUPDATE TABLE(
Email NVARCHAR(100) NOT NULL PRIMARY KEY
);
BEGIN TRANSACTION;
INSERT @CustomerEmailForUPDATE (Email)
SELECT ce.Email
FROM @CustomerEmail ce
WHERE EXISTS(SELECT * FROM dbo.Customer c WITH(XLOCK, HOLDLOCK) WHERE c.Email = ce.Email);
UPDATE dbo.Customer
SET OrdersCount = OrdersCount + 1
FROM dbo.Customer c
INNER JOIN @CustomerEmailForUPDATE cefu ON c.Email = cefu.Email
INSERT dbo.Customer(Email)
SELECT ce.Email – OrdersCount = Default 1
FROM @CustomerEmail ce
WHERE NOT EXISTS(SELECT * FROM @CustomerEmailForUPDATE cefu WHERE cefu.Email = ce.Email)
COMMIT; – Use ROLLBACK for testing
GO
– Solution #2: MERGE
– Parameters
DECLARE @CustomerEmail TABLE( Email NVARCHAR(100) NOT NULL PRIMARY KEY );
INSERT @CustomerEmail (Email) VALUES (N'[email protected]');
INSERT @CustomerEmail (Email) VALUES (N'[email protected]');
INSERT @CustomerEmail (Email) VALUES (N'[email protected]');
– End of Parameters
BEGIN TRANSACTION;
MERGE dbo.Customer WITH(HOLDLOCK) trg
USING @CustomerEmail src ON trg.Email = src.Email
WHEN MATCHED
THEN
UPDATE
SET OrdersCount = trg.OrdersCount + 1
WHEN NOT MATCHED BY TARGET
THEN
INSERT (Email)
VALUES (src.Email);
COMMIT; – Use ROLLBACK for testing
GO
– Solution #3: UPDATE + INSERT
– Parameters
DECLARE @CustomerEmail TABLE( Email NVARCHAR(100) NOT NULL PRIMARY KEY );
INSERT @CustomerEmail (Email) VALUES (N'[email protected]');
INSERT @CustomerEmail (Email) VALUES (N'[email protected]');
INSERT @CustomerEmail (Email) VALUES (N'[email protected]');
– End of Parameters
DECLARE @CustomerEmailUPDATED TABLE(
Email NVARCHAR(100) NOT NULL PRIMARY KEY
);
BEGIN TRANSACTION;
UPDATE trg
SET OrdersCount = OrdersCount + 1
OUTPUT inserted.Email INTO @CustomerEmailUPDATED
FROM dbo.Customer trg WITH(HOLDLOCK)
INNER JOIN @CustomerEmail src ON trg.Email = src.Email;
INSERT dbo.Customer(Email)
SELECT ce.Email
FROM @CustomerEmail ce
WHERE NOT EXISTS(SELECT * FROM @CustomerEmailUPDATED upd WHERE upd.Email = ce.Email);
COMMIT;
GO
The next blog
- will present solutions for scenarios where we need to insert or update multiple rows (2014-02-18) and
- will discuss pros and cons of these solutions.
Revision History:
2014-02-08: First update. I have added a new section for multiple rows. Solutions for a single row: some minor changes.
Leave a Reply