Is it safe the following approach for UPSERT ?
UPDATE MyTable
SET Col1 = NewValue
WHERE ID = @param1
ELSE
INSERT MyTable(ID, Col1)
VALUES (@param1, @param2)
The short answer is no!
Example:
- The transaction isolation level is READ COMMITTED which is the default isolation level.
- For this demo I’ve used a customer table to store the customer’s email and the number of sales orders (for every customer).
- I’ve inserted two customers (1 – [email protected]; 2 – [email protected]).
- I’ve created a stored procedure for UPSERT (dbo.InsertOrder). This stored procedure receive the customer’s email, it checks if exists this email and if not then it inserts the new customer (sales orders 1). Otherwise it increments the number of sales orders by 1.
T-SQL script:
DROP TABLE dbo.Customer;
IF OBJECT_ID(N'dbo.InsertOrder') IS NOT NULL
DROP PROCEDURE dbo.InsertOrder;
GO
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
INSERT dbo.Customer (Email)
VALUES (N'[email protected]'), – CustomerID 1
(N'[email protected]'); – CustomerID 2
GO
CREATE PROCEDURE dbo.InsertOrder (
@Email NVARCHAR(100)
)
AS
BEGIN
IF EXISTS(SELECT * FROM dbo.Customer WHERE Email = @Email)
BEGIN
UPDATE dbo.Customer
SET OrdersCount = OrdersCount + 1
WHERE Email = @Email;
END
ELSE
BEGIN
WAITFOR DELAY '00:00:03';
INSERT dbo.Customer (Email)
VALUES (@Email) – OrdersCount = Default 1
END
END;
GO
Note: WAITFOR DELAY is used to delay the INSERTion of the new customers with just 3 seconds.
Now we can start this test executing following statements in separate windows:
Query window #1 (Step 1):
UPDATE dbo.Customer
SET Note = N'Note#2'
WHERE Email = N'[email protected]';
– ROLLBACK — or COMMIT
At this moment the CustomerID = 2 is locked X (exclusive) and no other transaction can read (excepting NOLOCK and READ UNCOMMITTED), update or delete this record. Because this transaction remains open (no COMMIT or ROLLBACK) this record will be locked till the end of transaction.
Query window #2 & #3 (Step 2 & Step 3):
At this moment the IF EXISTS(SELECT * FROM dbo.Customer WHERE Email = @Email) statement will try to read the record with CustomerID = 1 and then the record with CustomerID = 2 requesting a S lock (shared). Because X and S locks aren’t compatible (see Lock Compatibility) both (Step 2 & 3) SELECT * FROM dbo.Customer WHERE Email = @Email statements will be blocked. Note: you can see the “Executing ….” message within tab title.
Query window #1 (Step 4):
I execute the ROLLBACK or COMMIT statement which ends the initial transaction. The X lock for CustomerID = 2 record is released and the SELECT statements (SELECT * FROM dbo.Customer WHERE Email = @Email; Step 2 & 3) continue to be executed. Every EXISTS(SELECT …. WHERE Email = ‘[email protected]’) statement will return FALSE and both stored procedures will INSERT the customer `[email protected]`.
This is the reason why I get duplicated emails at the end (Step 5) of my example:
Simplest solution is to create an unique index/constraint on Email column.
ON dbo.Customer(Email);
This UNIQUE index/constraint guarantees that we can’t have duplicated emails. But this doesn’t means this UPSERT approach is 100% safe because dbo.InsertOrder stored procedure still tries to INSERT duplicated emails (at Step 5):
Next blog On Jan 27 I will discuss what solutions do we have for safe UPSERTs.
See also:
Is it safe IF EXISTS – UPDATE ELSE INSERT [UPSERT] ? #2
Is it safe IF EXISTS – UPDATE ELSE INSERT [UPSERT] ? #3
Revision History:
2014-01-12: I changed the last paragraph.
2014-01-27: I changed the last paragraph.
2014-04-06: I added the [See also] section
[…] 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 […]
Pingback by UPSERT – safe solutions for SQL Server [DRAFT] | Yet another SQL Server enthusiast — 2014-01-27 @ 1:55 AM |