Is it safe the following approach for UPSERT ?
IF EXISTS (SELECT … FROM MyTable WHERE ID = @param1)
SET Col1 = NewValue
WHERE ID = @param1
INSERT MyTable(ID, Col1)
VALUES (@param1, @param2)
The short answer is no!
- 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@example.com; 2 – firstname.lastname@example.org).
- 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.
IF OBJECT_ID(N'dbo.Customer') IS NOT NULL 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)
INSERT dbo.Customer (Email)
VALUES (Nemail@example.com'), — CustomerID 1
(Nfirstname.lastname@example.org'); — CustomerID 2
CREATE PROCEDURE dbo.InsertOrder (
IF EXISTS(SELECT * FROM dbo.Customer WHERE Email = @Email)
SET OrdersCount = OrdersCount + 1
WHERE Email = @Email;
WAITFOR DELAY '00:00:03';
INSERT dbo.Customer (Email)
VALUES (@Email) — OrdersCount = Default 1
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):
SET Note = N'Note#2'
WHERE Email = Nemail@example.com';
— 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):
EXEC dbo.InsertOrder Nfirstname.lastname@example.org';
SELECT * FROM dbo.Customer;
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@example.com’) statement will return FALSE and both stored procedures will INSERT the customer `firstname.lastname@example.org`.
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.
CREATE UNIQUE INDEX IUN_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.
Is it safe IF EXISTS – UPDATE ELSE INSERT [UPSERT] ? #2
Is it safe IF EXISTS – UPDATE ELSE INSERT [UPSERT] ? #3
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