Yet another SQL Server enthusiast

2014-03-16

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

Filed under: concurrency,EXISTS,safe,UPSERT — ---- @ 1:25 PM

I presented here and here the reasons why one of the standard approach used for UPSERT

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

isn’t safe from concurrency point of view if we use default setting for transactions isolation level: read committed.

This means that

  1. We might get duplicate values (see #2) if we don’t use an unique index or
  2. We might get “Cannot insert duplicate key row…” errors if we use an unique index to prevent duplicate values. In this last blog on this subject and I want to show you how simple is to get above error.

The only difference between this test and previous test is the next index:

Code Snippet
  1. CREATE UNIQUE NONCLUSTERED INDEX [IUN_Customer_Email]
  2. ON [dbo].[Customer] ([Email] ASC)

The ostress.exe tool is executed with the same parameters:

sqlserver.ostress.upsert.test

The parameter -o”d:\SqlOstressOutput” represents the output folder. This folder is used to write the results of every T-SQL script and connection plus ostress.log file which collects overall results.

This time, this unique index prevents insertion of duplicate emails:

Code Snippet
  1. CustomerID  Email Note OrdersCount
  2. ———– —– —- ———–
  3. 1           [email protected] NULL 1
  4. 2           [email protected] NULL 1001
  5. 4           [email protected] NULL 995
  6. 9           [email protected] NULL 994

and, also, D:\SqlOstressOutput\ostress.log contains some errors generated by T-SQL scripts because at some moments they try to insert duplicate emails (see lines 34 – 46) but IUN_Customer_Email index prevents duplicate emails :

Code Snippet
  1. 03/13/14 15:10:37.875 [0x00002878] OSTRESS, A Generic ODBC-Based Stress/Replay Utility.
  2. Version 9.04.0004 built for x64.
  3.   Copyright1997-2013 Microsoft. All Rights Reserved
  4. 03/13/14 15:10:37.876 [0x00002878]             Computer: SGBD
  5. 03/13/14 15:10:37.877 [0x00002878]          Base Module: C:\\cf2 Program Files\\cf2 Microsoft Corporation\\cf2 RMLUtils\\cf2 ostress.exe
  6. 03/13/14 15:10:37.877 [0x00002878]           Process Id: 6848
  7. 03/13/14 15:10:37.877 [0x00002878]  Active proc mask(0): 0x0000000F
  8. 03/13/14 15:10:37.877 [0x00002878]         Architecture: 9
  9. 03/13/14 15:10:37.878 [0x00002878]            Page size: 4096
  10. 03/13/14 15:10:37.878 [0x00002878]                 CPUs: 4
  11. 03/13/14 15:10:37.878 [0x00002878]     Processor groups: 1
  12. 03/13/14 15:10:37.879 [0x00002878]         Highest node: 0
  13. 03/13/14 15:10:37.879 [0x00002878] —————————————
  14. 03/13/14 15:10:37.879 [0x00002878]                Group: 0
  15. 03/13/14 15:10:37.880 [0x00002878] —————————————
  16. 03/13/14 15:10:37.880 [0x00002878]         Processor(s): 0x00000003 Function units: Shared
  17. 03/13/14 15:10:37.881 [0x00002878]         Package mask: 0x0000000F
  18. 03/13/14 15:10:37.882 [0x00002878]         Processor(s): 0x0000000C Function units: Shared
  19. 03/13/14 15:10:37.884 [0x00002878]         Processor(s): 0x0000000F assigned to Numa node: 0
  20. 03/13/14 15:10:37.887 [0x00002878] Current time bias: -120 minutes -2.00 hours DST Standard
  21. 03/13/14 15:10:37.888 [0x00002878] Max threads setting: 10000
  22. 03/13/14 15:10:37.889 [0x00002878] Arguments:
  23. 03/13/14 15:10:37.891 [0x00002878] -S(local)\\cf2 SQL2012
  24. 03/13/14 15:10:37.892 [0x00002878] -E
  25. 03/13/14 15:10:37.893 [0x00002878] -dTestUPSERT
  26. 03/13/14 15:10:37.894 [0x00002878] -od:\\cf2 SqlOstressOutput
  27. 03/13/14 15:10:37.896 [0x00002878] -id:\\cf2 SqlOstressInput\\cf2 SQLQuery*.sql
  28. 03/13/14 15:10:37.897 [0x00002878] -n10
  29. 03/13/14 15:10:37.898 [0x00002878] -r100
  30. 03/13/14 15:10:37.900 [0x00002878] -q
  31. 03/13/14 15:10:37.901 [0x00002878] Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0x00060101 and Defined: 0x00060101
  32. 03/13/14 15:10:37.903 [0x00002878] Default driver: SQL Server Native Client 11.0
  33. 03/13/14 15:10:39.857 [0x0000327C] [spid 69] SQLState: 23000, Native Error: 2601, Severity: 14, State: 1, Line: 14
  34. [SQL Server]Cannot insert duplicate key row in object ‘dbo.Customer’ with unique index ‘IUN_Customer_Email’. The duplicate key value is (d@d.d).
  35.  
  36. 03/13/14 15:10:39.857 [0x00001988] [spid 71] SQLState: 23000, Native Error: 2601, Severity: 14, State: 1, Line: 14
  37. [SQL Server]Cannot insert duplicate key row in object ‘dbo.Customer’ with unique index ‘IUN_Customer_Email’. The duplicate key value is (c@c.c).
  38.  
  39. 03/13/14 15:10:39.858 [0x00000A14] [spid 54] SQLState: 23000, Native Error: 2601, Severity: 14, State: 1, Line: 14
  40. [SQL Server]Cannot insert duplicate key row in object ‘dbo.Customer’ with unique index ‘IUN_Customer_Email’. The duplicate key value is (d@d.d).
  41.  
  42. 03/13/14 15:10:39.858 [0x00003888] [spid 79] SQLState: 23000, Native Error: 2601, Severity: 14, State: 1, Line: 14
  43. [SQL Server]Cannot insert duplicate key row in object ‘dbo.Customer’ with unique index ‘IUN_Customer_Email’. The duplicate key value is (d@d.d).
  44.  
  45. 03/13/14 15:10:39.874 [0x0000038C] [spid 62] SQLState: 23000, Native Error: 2601, Severity: 14, State: 1, Line: 14
  46. [SQL Server]Cannot insert duplicate key row in object ‘dbo.Customer’ with unique index ‘IUN_Customer_Email’. The duplicate key value is (d@d.d).
  47.  
  48. more errors

Solutions: to prevent these concurrency problems here I’ve presented few solutions. Please bear in mind that you should rigorous test selected solution. Next blog will present pros and cons for every solution.

See also:

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

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

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: