Is it safe IF EXISTS – UPDATE ELSE INSERT [UPSERT] ? #2

2014-03-04

Is it safe IF EXISTS – UPDATE ELSE INSERT [UPSERT] ? #2

Filed under: concurrency,EXISTS,safe,UPSERT — ---- @ 8:45 AM

One of previous articles showed how easily we might get duplicate rows if we use following approach for UPSERT:

Code Snippet
IF EXISTS(SELECT  FROM MyTable WHERE ID = @param1)
     UPDATE    MyTable
     SET     Col1 = NewValue
     WHERE   ID = @param1
 ELSE
     INSERT  MyTable(ID, Col1)
     VALUES  (@param1, @param2)    

This time I decided to do other tests but without WAITFOR. I’ve used following table (the same table) without any non-clustered indexes (like IUN_Customer_Email) and with just two rows:

Code Snippet
CREATEDATABASETestUPSERT
GO
 DBCC FREEPROCCACHE;
 GO
 USE TestUPSERT
 GO
 IF OBJECT_ID(N'dbo.Customer') IS NOT NULL
     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
 INSERT dbo.Customer (Email) VALUES (N'[email protected]');
 INSERT dbo.Customer (Email) VALUES (N'[email protected]');
 GO

For my test I’ve used three scripts which try to insert or updated multiple times three emails: [email protected] (SQLQuery1.sql), [email protected] (SQLQuery2.sql) and [email protected] (SQLQuery3.sql). These files are available in D:\SqlOstressInput folder.

Content of SQLQuery{1|2|3}.sql script:

Code Snippet
– Parameters
 DECLARE @Email NVARCHAR(100);
 SET @Email = '[email protected]'; – or '[email protected]' or '[email protected]' 
 – End of Parameters
 BEGIN TRANSACTION;
     IF EXISTS(SELECT * FROM dbo.Customer 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;

To run these scripts multiple times (100 times) using 10 concurrent connections I’ve used ostress.exe which is included into RML utilities for SQL Server. Before test I’ve used DBCC DROPCLEANBUFFERS and for ostress.exe I’ve used following arguments:

Code Snippet
[RML] C:\Program Files\Microsoft Corporation\RMLUtils> 
 ostress -S(local)\SQL2012 -E -dTestUPSERT -o"d:\SqlOstressOutput" -i"d:\SqlOstressInput\SQLQuery*.sql" -n10 -r100 -q

After few tries I succeeded to get duplicate rows (sample output, you might get different results) and for some emails ([email protected] and [email protected]) the OrderCount is wrong:

Code Snippet
CustomerID  Email Note OrdersCount
 ———– —– —- ———–
 1           [email protected] NULL 1
 2           [email protected] NULL 1001
 3           [email protected] NULL 999
 4           [email protected] NULL 999
 5           [email protected] NULL 996
 6           [email protected] NULL 996
 7           [email protected] NULL 996
 8           [email protected] NULL 996
 9           [email protected] NULL 996

Conclusion: This approach for UPSERT (without any unique indexes and table hints or without serializable transaction isolation level) isn’t safe.

Note: for this test I used

Code Snippet
Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (X64) 
     Oct 19 2012 13:38:57 
     Copyright (c) Microsoft Corporation
     Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Transaction isolation level was READ COMMITTED (default).

2 Comments »

[…] presented here and here why one of the standard approach used for […]

Pingback by Is it safe IF EXISTS – UPDATE ELSE INSERT [UPSERT] ? #3 | Yet another SQL Server enthusiast — 2014-03-16 @ 10:53 PM | Reply
[…] Is it safe IF EXISTS – UPDATE ELSE INSERT [UPSERT] ? #2 […]

Pingback by Is it safe IF EXISTS – UPDATE ELSE INSERT [UPSERT] ? | Yet another SQL Server enthusiast — 2014-04-06 @ 7:27 PM | Reply

RSS feed for comments on this post. TrackBack URI

Yet another SQL Server enthusiast

2014-03-04