I presented here and here the reasons why one of the standard approach used for UPSERT
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
-
We might get duplicate values (see #2) if we don’t use an unique index or
-
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:
- CREATE UNIQUE NONCLUSTERED INDEX [IUN_Customer_Email]
- ON [dbo].[Customer] ([Email] ASC)
The ostress.exe tool is executed with the same parameters:
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:
- CustomerID Email Note OrdersCount
- ———– —– —- ———–
- 1 [email protected] NULL 1
- 2 [email protected] NULL 1001
- 4 [email protected] NULL 995
- 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 :
- 03/13/14 15:10:37.875 [0x00002878] OSTRESS, A Generic ODBC-Based Stress/Replay Utility.
- Version 9.04.0004 built for x64.
- Copyright1997-2013 Microsoft. All Rights Reserved
- 03/13/14 15:10:37.876 [0x00002878] Computer: SGBD
- 03/13/14 15:10:37.877 [0x00002878] Base Module: C:\\cf2 Program Files\\cf2 Microsoft Corporation\\cf2 RMLUtils\\cf2 ostress.exe
- 03/13/14 15:10:37.877 [0x00002878] Process Id: 6848
- 03/13/14 15:10:37.877 [0x00002878] Active proc mask(0): 0x0000000F
- 03/13/14 15:10:37.877 [0x00002878] Architecture: 9
- 03/13/14 15:10:37.878 [0x00002878] Page size: 4096
- 03/13/14 15:10:37.878 [0x00002878] CPUs: 4
- 03/13/14 15:10:37.878 [0x00002878] Processor groups: 1
- 03/13/14 15:10:37.879 [0x00002878] Highest node: 0
- 03/13/14 15:10:37.879 [0x00002878] —————————————
- 03/13/14 15:10:37.879 [0x00002878] Group: 0
- 03/13/14 15:10:37.880 [0x00002878] —————————————
- 03/13/14 15:10:37.880 [0x00002878] Processor(s): 0x00000003 Function units: Shared
- 03/13/14 15:10:37.881 [0x00002878] Package mask: 0x0000000F
- 03/13/14 15:10:37.882 [0x00002878] Processor(s): 0x0000000C Function units: Shared
- 03/13/14 15:10:37.884 [0x00002878] Processor(s): 0x0000000F assigned to Numa node: 0
- 03/13/14 15:10:37.887 [0x00002878] Current time bias: -120 minutes -2.00 hours DST Standard
- 03/13/14 15:10:37.888 [0x00002878] Max threads setting: 10000
- 03/13/14 15:10:37.889 [0x00002878] Arguments:
- 03/13/14 15:10:37.891 [0x00002878] -S(local)\\cf2 SQL2012
- 03/13/14 15:10:37.892 [0x00002878] -E
- 03/13/14 15:10:37.893 [0x00002878] -dTestUPSERT
- 03/13/14 15:10:37.894 [0x00002878] -od:\\cf2 SqlOstressOutput
- 03/13/14 15:10:37.896 [0x00002878] -id:\\cf2 SqlOstressInput\\cf2 SQLQuery*.sql
- 03/13/14 15:10:37.897 [0x00002878] -n10
- 03/13/14 15:10:37.898 [0x00002878] -r100
- 03/13/14 15:10:37.900 [0x00002878] -q
- 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
- 03/13/14 15:10:37.903 [0x00002878] Default driver: SQL Server Native Client 11.0
- 03/13/14 15:10:39.857 [0x0000327C] [spid 69] SQLState: 23000, Native Error: 2601, Severity: 14, State: 1, Line: 14
- [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).
- 03/13/14 15:10:39.857 [0x00001988] [spid 71] SQLState: 23000, Native Error: 2601, Severity: 14, State: 1, Line: 14
- [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).
- 03/13/14 15:10:39.858 [0x00000A14] [spid 54] SQLState: 23000, Native Error: 2601, Severity: 14, State: 1, Line: 14
- [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).
- 03/13/14 15:10:39.858 [0x00003888] [spid 79] SQLState: 23000, Native Error: 2601, Severity: 14, State: 1, Line: 14
- [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).
- 03/13/14 15:10:39.874 [0x0000038C] [spid 62] SQLState: 23000, Native Error: 2601, Severity: 14, State: 1, Line: 14
- [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).
- … 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.