Tag Archives | deadlocks

New online course: Troubleshooting Blocking & Deadlocks for Beginners (Free!)

I made y’all a SQL Server style Valentine’s day present: a new FREE online training course.

Here’s a quick trailer:

Course info: Troubleshooting Blocking & Deadlocks for Beginners

You need to prove if blocking is killing the performance on your SQL Server. Learn to set up simple, lightweight monitoring using free tools and scripts to find the queries causing your blocking and deadlock problems.

Here’s how the course is laid out…

  • Welcome! Here’s what you need to get started (2 minutes)
  • Wait stats, alerts, and troubleshooting blocking live (43 minutes)
  • Troubleshooting blocking that happens when you’re away from your desk (31 minutes)
  • Capture and prevent deadlocks (29 minutes)
  • Interview questions (21 minutes)
  • Learning plan (3 minutes)

Lessons in these modules are each between 5 and 15 minutes on average, so you can easily move through the course in short sessions.

Enroll for free at SQLWorkbooks.com.

What students have said about this course

I gave an hour version of this course at the SQL PASS Summit in 2016. Here’s what students wrote in their session evaluations:

  • “This session was hyper-approachable and gave me a lot of tools I can use to improve on a place I’m weak on.”
  • “Great Info and demos”
  • “Moar dinosaurs! Excellent tips. Always great to find more free tools to help diagnose issues.”
  • “Speaker was excellent. Very knowledgeable. Learned a lot of very useful information that I can take back to the workplace. “
  • “I’m well versed with blocking, deadlocking, and resolution.  This session was a good review of key points but still contained information that I hadn’t yet been aware of.  “
  • “The description of how to read a deadlock graph was one of the best I have ever heard.”
  • “Kendra was a delight to listen to.  She supplied useful information in an easy to learn fashion.”
  • “Kendra had some great examples of everything she was talking about. That helped a lot in learning how to deal with blocking and deadlocks.”
  • “It was great to see something that I understood previously but from a different perspective. Everything was thoroughly explained and related in ways that made sense and painted a picture, literally there were pictures in the slides.”

I’m getting a little choked up reading those comments– man, what an awesome audience that was. Happy Valentines Day to me!

Here’s the link again if you don’t feel like scrolling up.

Tracing Deadlock Graphs: Extended Events or Server Side Trace

deadlock-manager-sql-serverDeadlock graphs are incredibly helpful for figuring out why queries are getting automatically killed off by SQL Server.

But it can be tricky to trace deadlocks in SQL Server. You might be tempted to use Profiler, but the application has lots of baggage. You’re better off using either Extended Events (which may be confusing), or a Server Side Trace (which you can script from Profiler– if you know exactly which buttons to push in the right order). You might look in the System Health trace on recent versions of SQL Server, but if the deadlock didn’t happen recently, then it may have rolled off the trace.

To make this all easier, I’ve created a gist on GitHub sharing TSQL to save you a bunch of time.

Choose the script that works for you. You can:

  1. Use a simple Extended Events trace to get deadlock graphs via the sqlserver.xml_deadlock_report event
  2. Use a Server Side SQL Trace to get deadlock graphs (for older versions of SQL Server, or people who like SQL Trace)
  3. Use a (much more verbose) Extended Events trace to get errors, completed statements, and deadlock graphs. You only need something like this if the input buffer showing in the deadlock graph isn’t enough, and you need to collect the other statements involved in the transactions. You do this by matching the transaction id for statements to the xactid for each item in the Blocked Process Report. Warning, this can generate a lot of events and slow performance.

View or download the code from GitHub, or get it below.

When testing out these deadlock traces, you might want code to reproduce a deadlock in SQL Server.

/***********************************************************************
Copyright 2016, Kendra Little - LittleKendra.com
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/
/***********************************************************************
COLLECT DEADLOCK GRAPHS WITH EXTENDED EVENTS
This script contains TSQL to:
* Create an Extended Events Trace collecting sqlserver.xml_deadlock_report
* Start the trace
* Code to stop and delete the trace is commented out
Notes:
This works with SQL Server 2012 and higher
Change the filename to a relevant location on the server itself
Tweak options in the WITH clause to your preference
Note that there is no automatic stop for this! If you want that, use a
Server Side SQL Trace instead.
THIS CREATES AND STARTS AN EXTENDED EVENTS TRACE
***********************************************************************/
/* Create the Extended Events trace */
CREATE EVENT SESSION [Deadlock Report] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
(SET filename=
N'S:\XEvents\deadlock-report.xel', max_file_size=(1024),max_rollover_files=(4))
/* File size is in MB */
WITH (
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS /* 0 = unlimited */,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON)
GO
/* Start the Extended Events trace */
ALTER EVENT SESSION [Deadlock Report]
ON SERVER
STATE = START;
GO
/***********************************************************************
Test a deadlock with the code here:
https://www.littlekendra.com/2016/09/13/deadlock-code-for-the-wideworldimporters-sample-database/
***********************************************************************/
/* Stop the Extended Events trace when you want with a command like this */
--ALTER EVENT SESSION [Deadlock Report]
-- ON SERVER
-- STATE = STOP;
--GO
/* Drop the trace when you're done with a command like this */
--DROP EVENT SESSION [Deadlock Report] ON SERVER;
--GO
/***********************************************************************
THIS OPTION USES A SERVER SIDE SQL TRACE TO PICK UP THE BPR.
YOU ONLY NEED THIS *OR* ONE OF THE XEVENTS TRACES
***********************************************************************/
/* Modified from a script generated from SQL Server Profiler */
/* Pre-requisites and notes:
This works with SQL Server 2005 and higher
Change the filename to a relevant location on the server itself
Tweak options to your preference (including the end date)
Code to stop and delete the trace is commented out at the end
THIS CREATES AND STARTS A SERVER SIDE SQL TRACE
*/
declare @rc int;
declare @TraceID int;
declare @maxfilesizeMB bigint;
declare @TraceEndDateTime datetime;
declare @TraceFilename nvarchar(500);
declare @rolloverfilecount int;
set @TraceEndDateTime = '2020-12-12 00:00:00.000';
set @maxfilesizeMB = 1024;
set @TraceFilename = N'S:\XEvents\Deadlock-Graph';
set @rolloverfilecount = 4;
/* Create the basic server side trace */
exec @rc = sp_trace_create
@TraceID output,
@options = 2 /* trace will use rollover files */,
@tracefile = @TraceFilename,
@maxfilesize = @maxfilesizeMB,
@stoptime = @TraceEndDateTime,
@filecount = @rolloverfilecount;
if (@rc != 0) goto error;
/* Add the deadlock graph event and collect some columns */
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
/* Start the trace */
exec sp_trace_setstatus @TraceID, 1
/* Return list of traces to the caller */
select * from sys.traces;
GOTO finish
error:
select [email protected]
finish:
GO
/***********************************************************************
Test a deadlock with the code here:
https://www.littlekendra.com/2016/09/13/deadlock-code-for-the-wideworldimporters-sample-database/
***********************************************************************/
/***********************************************************************
STOP AND DELETE THE Deadlock-Graph Trace (when needed)
***********************************************************************/
--/* Get the trace id */
--DECLARE @id int
--SELECT @id=id
--FROM sys.traces where path like '%Deadlock-Graph%';
--/* Stop the trace by setting its status to 0 */
--EXEC sp_trace_setstatus @traceid = @id , @status = 0;
--/* Delete the trace by setting the status to 2 */
--EXEC sp_trace_setstatus @traceid = @id , @status = 2;
--/* Return remaining traces */
--SELECT * FROM sys.traces;
--GO
/***********************************************************************
COLLECT ERRORS, STATEMENTS, AND DEADLOCK GRAPHS WITH EXTENDED EVENTS
VERY VERBOSE
This script contains TSQL to:
* Create an Extended Events Trace collecting
errors, completed statements, and deadlock report
* Trace collects transaction_id on statement completed events, this
can be matched to xactid for each item in the Blocked Process Report
* Start the trace
* Code to stop and delete the trace is commented out at the end
Notes:
This works with SQL Server 2012 and higher
*** This will generate a LOT of data on most servers and may slow performance
due to collecting statement completed events. If in doubt, start
with the alternate script collecting sqlserver.xml_deadlock_report only. ***
Change the filename to a relevant location on the server itself
Tweak options in the WITH clause to your preference
Note that there is no automatic stop for this! If you want that, use a
Server Side SQL Trace instead.
THIS CREATES AND STARTS AN EXTENDED EVENTS TRACE
***********************************************************************/
/* Create the Extended Events trace */
CREATE EVENT SESSION [Errors + Statements + Deadlock Report] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)
ACTION(sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
(SET filename=
N'S:\XEvents\errors-statements-deadlock-report.xel', max_file_size=(1024),max_rollover_files=(4))
/* File size is in MB */
WITH (
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS /* 0 = unlimited */,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON)
GO
/* Start the Extended Events trace */
ALTER EVENT SESSION [Errors + Statements + Deadlock Report]
ON SERVER
STATE = START;
GO
/***********************************************************************
Test a deadlock with the code here:
https://www.littlekendra.com/2016/09/13/deadlock-code-for-the-wideworldimporters-sample-database/
***********************************************************************/
/* Stop the Extended Events trace when you want with a command like this */
--ALTER EVENT SESSION [Errors + Statements + Deadlock Report]
-- ON SERVER
-- STATE = STOP;
--GO
/* Drop the trace when you're done with a command like this */
--DROP EVENT SESSION [Errors + Statements + Deadlock Report] ON SERVER;
--GO

Setting up Free Blocking Alerts and Deadlock Monitoring (Dear SQL DBA Episode 17)

What tools in SQL Server will notify you about blocking and help track the queries behind your toughest blocking and deadlocking problems?

Watch the 21 minute video, subscribe to the podcast, or read the episode notes and links below.

Dear SQL DBA,

What is the best way to set up blocking and deadlock alerts on the server? I want to be notified automatically without any impact on the prod server.

I have tried alerts with SQL server performance condition alerts with SQL server agent. They do not show the queries or tables involved etc?

Thanks,

All Blocked Up


Woo hoo, I love this question!

So first off, I’m going to answer this discussing the free, built-in tools with SQL Server. If you have a budget for custom monitoring tools, you can buy fancy tools that have customized notifications for blocking and which capture the queries and plans involved. If that’s the case, set up free trials against a test system.

But not everyone has budget for every single SQL Server instance. So it’s extremely useful to know what SQL Server offers to help you with this.

And by the way, if you’re going to be at the SQLPASS Summit in Seattle in just under a month, I’m giving a session that shows different blocking scenarios. Come see my session, The Great Performance Robbery: Locking Problems and Solutions on Thursday, Oct 27th at 10:45AM in room 6C.

Free, simple blocking notifications

I like to set up blocking notifications with a simple SQL Server agent alert on the “SQLServer: General Statistic: Processes Blocked” performance counter.

This will not give you the queries involved in the blocking — don’t worry, we’ll cover tracking that in the next step.

This alert is low impact and it will let you know when you need to look at the SQL Server.

To get this alert to work, you’ll need to:

  • Configure the SQL Server Agent service to auto start
  • Set up database mail, enable it on the  SQL Server Agent, and then restart the SQL Server Agent
  • Configure an Operator in the SQL Server Agent
  • Create a new ‘performance’ style alert, base it on the “Processes Blocked” counter, and tell it who to notify

A few things to note:

  1. The SQL Agent doesn’t poll counters constantly – and we want this to be lightweight, so that’s a good thing. It will only poll every 15-30 seconds, and there’s no published guaranteed SLA on that polling frequency.
  2. If you really need something more sensitive and reliable, you need a monitoring system fully independent / outside of the SQL Server to be polling in and checking it for availability and performance.
  3. You can configure the alert to only fire every X minutes. I highly recommend that, so you don’t get an inbox of alerts every 20 seconds

Create some blocking in a test database or in tempdb and make sure the alert works.

I have example code to create blocking and deadlocks for your dev environments in my post, Deadlock Code for the WorldWideImporters Sample Database.

For production databases, you can create a temp table and write similar code to create blocking in those.

lookout-query-comin-through-wide

Finding the queries involved with the Blocked Process Report

OK, we’ve got notifications. We need SQL Server to give us more information on who is involved in the blocking.

I like to use the built-in Blocked Process Report for this. This has been in SQL Server for a long time, and it’s extremely useful.

The Blocked Process Report shows you the “input buffer” of the commands involved – it may be partial information and not the full text of the query. It will also show you the login name for who is running what, and the type of lock requests involved.

The Blocked Process Report is pretty lightweight, because SQL Server has to frequently wake up and look for blocking situations that can’t resolve themselves. By default, the deadlock monitor wakes up every 5 seconds and looks around to see if there is a deadlock which it needs to break. You may enable a feature called the ‘Blocked Process Report’ that tells SQL Server to additionally issue a report on any blocking which it finds.

To get this to work, you need to:

  • Enable the sp_configure option for the blocked process threshold. This defaults to ‘0’, which is off. You configure the threshold to the number of seconds you’d like the threshold to be. This should be a value of 5 or higher, because making the deadlock monitor run constantly could tank your performance. A good ‘starting’ value is 30 seconds.
  • You also need to set up a trace to collect an event called the ‘blocked process report’. Setting the threshold causes the event to be output, but SQL Server won’t collect it for you unless you start a SQL Trace or an Extended events trace that collects that event.

Once you have the trace file, you can copy it off of the production server to interpret it.

Michael J Swart has written a great free tool called the Blocked Process Report Viewer to help interpret the blocking chains. It’s free at https://sqlblockedprocesses.codeplex.com.

The viewer makes it easier to see the root of the blocking chain and who was blocking whom.

This trace is pretty lightweight, but with any trace you want to make sure that you don’t add a bunch of events that you don’t need, and that you periodically clean up the files and don’t let it impact drive space.

When I talk about running traces, I don’t mean running Profiler

We’re entering dangerous territory here. Whenever you talk about tracing in SQL Server these days, someone gets offended.

Here’s what you need to know. There’s two main ways to run a trace:

  1. SQL Trace. This is the old school option. You can run this using…
    1. The Profiler client (I don’t like this option)
    2. A Server Side trace scripted out from Profiler (much better!).  You can get up to speed on Server Side Traces reading this article on generating a service side trace by Jen McCown. (Note that she wrote this article back in 2009. That’s fine, SQLTrace hasn’t been changing since then.)
  2. Extended Events. This is much easier to use on SQL Server 2012 and higher than in previous versions because a GUI was introduced in Management Studio for it under Object Explorer.

I do not like leaving the Profiler application running because I’ve seen it do everything from slowing down performance to filling up drives over the years. And creating Server Side traces isn’t very hard if you do want to use SQL Trace.

I personally only like to have a trace running if I know I need it and am going to look at it. So I only enable this when I have a blocking problem. Whenever you choose to leave a trace running, you need to periodically check in on the files its created and clean up after it.

Detecting and handling deadlocks

What about locking problems where SQL Server has to step in and kill one of the queries?

You have a few built in options about how to get info on this. There are some trace flags that you can turn on which cause some information about who is involved in the deadlock to be printed to the SQL Server Error Log. This isn’t my preferred option because the information is very hard to parse through and read.

I find it more helpful to get a ‘deadlock graph’, which is a picture of how the locking fight went down.

On older versions of SQL Server, you can capture the deadlock graph with a server side trace.

On newer versions of SQL Server, you can capture this with an Extended Events trace.

A great resource for deciding how to capture deadlock information is Jonathan Kehayias’ excellent Simple Talk article, Handling Deadlocks in SQL Server.  He covers how to collect the graphs, shows examples of how they look, and gets you started tackling them.

If you get through this point and need to get really fancy with deadlocks, Michael J Swart recently wrote about using Event Notifications to collect execution plans related to deadlocks in his post, “Build Your Own Tools“. Just don’t try to run before you walk: this is pretty advanced stuff and you need to be comfortable using Service Broker, which is part of Event Notifications behind the scenes.

Updates, Oct 12, 2016:

  • Deadlock alerting: While it’s always better for the application itself to catch, handle, and log/alert on failed queries (not just deadlocks, but timeouts and anything else)… if your application can’t do that, and you can’t change it, check out this SQL Magazine article by Michael Campbell on alerting on deadlocks from SQL Server.
  • Also, thanks to Johan for pointing out that the free SQL Sentry Plan Explorer client tool is also able to display deadlock graphs. Just save them as an XDL file and you can open and view them.

Quick recap – getting more info on blocking

A fast rundown of the free, built-in tools we covered:

  • I like to use a simple, light, performance alert in the SQL Server agent for notification about blocking
  • I like the Blocked Process report to find out who’s blocking whom – collected by a server side SQL Trace or Extended events
  • I find collecting deadlock graphs with either a server side SQL Trace or Extended Events to be the most helpful way to figure out who’s involved in the nastiest blocking tangles.

Want to submit a Dear SQL DBA Question?

Want clarification on some of the basics? Got a question that jumped into your mind reading or listening to this? I’d love to hear it– asking is always free and easy!

Testing an Insert for Race Conditions with Ostress.exe

test-for-race-conditions-rectangle

Whenever we have multiple sessions modifying data, things get tricky. When we have a pattern of “check if the data exists and then do a thing,” multiple sessions get even more complicated.

It’s not very practical to try to open a bunch of sessions in SQL Server Management Studio to run commands in a loop. It’s hard to manage and if you’re like me, you find a way to crash SSMS doing it.

It’s much easier to test for race conditions using the free OStress.exe tool from Microsoft. Download OStress.exe as part of the RML Utilities for SQL Server (x64) here.

Our Example: Insert a Row if It Doesn’t Exist Already

We have a simple table named dbo.RaceCondition with two columns: RaceConditionId and RaceConditionValue. RaceConditionId is the clustered Primary Key. A unique index on RaceConditionValue prevents duplicate rows from being inserted.

Here’s how to create it:

IF DB_ID('RaceConditionTesting') is not null
BEGIN
    USE MASTER;
    ALTER DATABASE RaceConditionTesting SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE RaceConditionTesting;
END
GO

CREATE DATABASE RaceConditionTesting;
GO

use RaceConditionTesting;
GO

CREATE TABLE dbo.RaceConditionTable
(
    RaceConditionId int IDENTITY(1,1) NOT NULL,
    RaceConditionValue varchar(50) NOT NULL,
    CONSTRAINT pk_RaceConditionTable PRIMARY KEY CLUSTERED (RaceConditionId ASC)
);
GO

CREATE UNIQUE INDEX ix_RaceConditionTable_RaceConditionValue on dbo.RaceConditionTable(RaceConditionValue);
GO

/* Insert one row */
INSERT dbo.RaceConditionTable (RaceConditionValue) VALUES ('Foo');
GO

Our First Attempt: A Single Select Statement

Here’s the code to insert rows into dbo.RaceConditionTable. dbo.InsertIfNotExists contains just one INSERT/SELECT statement.

Note that this is all one statement. There’s no “IF” statement. There’s also no hints.

IF OBJECT_ID('dbo.InsertIfNotExists') IS NULL
    EXEC ('CREATE PROCEDURE dbo.InsertIfNotExists as RETURN 0;');
GO
ALTER PROCEDURE dbo.InsertIfNotExists (
    @RaceConditionValue varchar(50)
)
AS
    SET NOCOUNT ON;

    INSERT dbo.RaceConditionTable (RaceConditionValue) 
    SELECT x.newval
    FROM (SELECT @RaceConditionValue as newval) as x
    LEFT JOIN dbo.RaceConditionTable as p on 
        x.newval=p.RaceConditionValue
    WHERE p.RaceConditionValue IS NULL;
GO

Our Simple Test Harness: dbo.RaceConditionTest and Ostress.exe

Now we need a way to test this.  dbo.RaceConditionTest is a procedure that looks at RaceCondition table, picks the highest value for RaceConditionId in the table, adds one to it and turns that into a character value for RaceConditionValue.

We are going to run this procedure in OStress.exe across multiple threads. Those threads will all be generating values for RaceConditionValue using the same table. Will the simple INSERT/SELECT statement use the right locks to prevent them from generating the same values? If so, they’ll fail, because our unique index does not allow duplicate values.

IF OBJECT_ID('dbo.RaceConditionTest') IS NULL
    EXEC ('CREATE PROCEDURE dbo.RaceConditionTest as RETURN 0;');
GO
ALTER PROCEDURE dbo.RaceConditionTest 
AS
    DECLARE @namevarchar varchar(50)
    SELECT @namevarchar = CAST(MAX(RaceConditionId)+1 AS varchar(50))
    FROM dbo.RaceConditionTable

    EXEC dbo.InsertIfNotExists @[email protected];
GO

I’ll run OStress.exe with this command:

ostress.exe -Q"exec RaceConditionTesting.dbo.RaceConditionTest" -n6 -r100 -o"c:\ostressoutput"

This tells OStress to run the RaceConditionTest procedure on six threads, try to run it 100 times on each thread, and to put output files in c:\ostressoutput.

Test Results: The Simple Insert/Select

OStress.exe spews insert failures everywhere with our first INSERT statement. The message “Cannot insert duplicate key row in object ‘dbo.RaceConditionTable’ with unique index ‘ix_RaceConditionTable_RaceConditionValue'” is everywhere:

ostress-insert-select-duplicate-key

Here’s the insert command we’ve been using, to review:

    INSERT dbo.RaceConditionTable (RaceConditionValue) 
    SELECT x.newval
    FROM (SELECT @RaceConditionValue as newval) as x
    LEFT JOIN dbo.RaceConditionTable as p on 
        x.newval=p.RaceConditionValue
    WHERE p.RaceConditionValue IS NULL;

The OStress.exe results show that the locks on the LEFT JOIN to race condition to check if a row exists can be processed and released before the INSERT runs. This means that the following can happen:

  • Session A sees that key 266 does not exist and prepares to insert
  • Session B sees that key 266 does not exist and prepares to insert
  • Session A runs its  insert
  • Session B attempts to run its insert, but fails because of a duplicate key error

Revised Code: Higher Locks with UPDLOCK

What if we tell SQL Server to take out a higher lock when it reads the row? If we block when we read, maybe that will solve the problem by making others wait. Let’s test it. Here’s our revised code:

IF OBJECT_ID('dbo.InsertIfNotExists') IS NULL
    EXEC ('CREATE PROCEDURE dbo.InsertIfNotExists as RETURN 0;');
GO
ALTER PROCEDURE dbo.InsertIfNotExists (
    @RaceConditionValue varchar(50)
)
AS
    SET NOCOUNT ON;

    INSERT dbo.RaceConditionTable (RaceConditionValue) 
    SELECT x.newval
    FROM (SELECT @RaceConditionValue as newval) as x
    LEFT JOIN dbo.RaceConditionTable as p WITH (UPDLOCK) on 
        x.newval=p.RaceConditionValue
    WHERE p.RaceConditionValue IS NULL;
GO

The only change here is that the LEFT JOIN now has a hint telling SQL Server to take out an update lock (UPDLOCK) when it reads the row.

Test Results: UPDLOCK

Re-running OStress.exe, I can see that I haven’t fixed the issue. I’ve still got unique key violations galore:

ostress-insert-select-updlock-duplicate-key

Even though I’m taking out UPDLOCKS, the following race condition pattern can still occur

  • Session A takes out an UPDLOCK, sees that key 266 does not exist, releases its UPDLOCK, and prepares to insert
  • Session B takes out an UPDLOCK, sees that key 266 does not exist, releases its UPDLOCK, and prepares to insert
  • Session A runs its  insert
  • Session B attempts to run its insert, but fails because of a duplicate key error

We need to hold that lock.

More Revised Code: Higher Locks with UPDLOCK, Lock Duration with HOLDLOCK

We can protect against race conditions like this by telling SQL Server to take out more locks when it processes the existence check and to hold them for the duration of the transaction. Here’s the revised code:

IF OBJECT_ID('dbo.InsertIfNotExists') IS NULL
    EXEC ('CREATE PROCEDURE dbo.InsertIfNotExists as RETURN 0;');
GO
ALTER PROCEDURE dbo.InsertIfNotExists (
    @RaceConditionValue varchar(50)
)
AS
    SET NOCOUNT ON;

    INSERT dbo.RaceConditionTable (RaceConditionValue) 
    SELECT x.newval
    FROM (SELECT @RaceConditionValue as newval) as x
    LEFT JOIN dbo.RaceConditionTable as p WITH (UPDLOCK, HOLDLOCK) on 
        x.newval=p.RaceConditionValue
    WHERE p.RaceConditionValue IS NULL;
GO

Now the LEFT JOIN now has a hint telling SQL Server is taking out an (UPDLOCK) when it reads the row AND to protect that key range for the duration of the transaction (HOLDLOCK). So we’re using Serializable Isolation level and telling it to lock it like we’re modifying, no matter what.

Test Results: UPDLOCK, HOLDLOCK

Re-running OStress.exe, I get nice clean output this time:

ostress-insert-select-updlock-holdlock

No key insert violations! I get the same thing running across 20 threads, 1,000 executions per thread.

There Are Downsides to Taking Out High Locks and Holding Them Longer

Higher locks that are held longer can mean more blocking, and of course that can slow you down. If new rows come in relatively rarely, I probably want to make my code a bit more complex and only take out the higher locks when I really have to. I also want to make sure my indexes are optimized to help me read and lock the smallest range possible for speed. Check out a sample of that scenario in my prior post.