Deadlock Code for the WideWorldImporters Sample Database

If you haven’t checked out Microsoft’s new WideWorldImporters sample database for 2016, it’s a pretty cool new little database. The database makes it easy to play around with new 2016 features, and it even ships with some cool little executables to run inserts in the “workload-drivers” folder.

I recently put together some code to reproduce a simple deadlock in WideWorldImporters. This isn’t related to the new features at all– I was just exploring the sample database and writing some demos for locking and blocking. Deadlocks are easier to understand when you have hands-on code that you can step through and watch it happen live, rather than just being mystified by it when it happens later!

Setup – Restore WideWorldImporters

All you need to do is download the WideWorldImporters-Full.bak backup (121MB) and restore it to a test instance with Developer Edition running.

Deadlock-Priority

Let’s Cause a Deadlock

You will use two session windows to create your deadlock.

In session window #1, run this code:

USE WideWorldImporters;
GO

/* Run the BEGIN tran and the first statement in this session.
We're taking out a lock on the Countries table */
WHILE @@TRANCOUNT > 1 ROLLBACK
BEGIN TRAN

    UPDATE Application.Countries
    SET LatestRecordedPopulation = LatestRecordedPopulation + 1
    WHERE IsoNumericCode = 840;

Leaving that there, open session window #2, and run this code:

SELECT CityName, StateProvinceName, sp.LatestRecordedPopulation, CountryName
FROM Application.Cities AS city
JOIN Application.StateProvinces AS sp on
    city.StateProvinceID = sp.StateProvinceID
JOIN Application.Countries AS ctry on 
    sp.CountryID=ctry.CountryID
WHERE sp.StateProvinceName = N'Virginia'

The code in session window #2 should be blocked — you’ll see it sitting there executing.

Back in session window #1, run this:

    UPDATE Application.StateProvinces
    SET LatestRecordedPopulation = LatestRecordedPopulation +1
    WHERE StateProvinceCode=N'VA'
COMMIT
GO

Session window #1 should tell you that 1 row was affected. It’s done!

Look back in session window #2, though. You should see the following message:

Msg 1205, Level 13, State 51, Line 1

Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

 A “deadly embrace”

SQL Server’s deadlock manager woke up, looked around, and saw that our two session windows were stuck. They each were requesting locks that the other session wouldn’t give up– and if the deadlock manager didn’t break the deadlock, they’d be stuck there forever.

I didn’t set the deadlock priority on any of my transactions, so the deadlock manager picked the session that it thought would be the least work to roll back– and it became the victim.

How to learn more about deadlocks

This repro code will get you started. It’s rerunnable: you can keep deadlocking and deadlocking those sessions.

To teach yourself more about how the locks work:

  • Run sp_WhoIsActive with @get_locks=1 from a 3rd session window at different points and observe lock grants and waits between the session
  • Set up an extended events session to pick up the deadlock graph, reproduce the deadlock, and teach yourself to decode the graph. (Erin Stellato has a free Stairway to Extended Events if you’re just getting started with those.
  • What are your options for fixing this deadlock? Test out optimistic locking, query rewrites, setting deadlock priority, and creating indexes to see what works (I’m not saying that all of them do in this case)
  • What is Countries_Archive, and why does it show up in the lock graphs?

Happy learning!

, , , , , ,

7 Responses to Deadlock Code for the WideWorldImporters Sample Database

  1. Nikos June 20, 2017 at 11:34 pm #

    Hi Kendra.How would this scenario play under RCSI or Snapshot isolation ? would there be a deadlock too?

    • Kendra Little June 21, 2017 at 8:31 am #

      First, I encourage you to try it out and “prove” this hands on– it’s nice and easy with the sample database.

      Under RCSI and Snapshot isolation levels, readers don’t block writers, and writers don’t block readers. (Writers still block writers). This sample deadlock involves a writer blocking a reader, and a reader blocking a writer — so RCSI or snapshot isolation levels would prevent the deadlock.

Trackbacks/Pingbacks

  1. WideWorldImporters Deadlocks – Curated SQL - September 14, 2016

    […] Kendra Little has a couple queries to force deadlocks in the WideWorldImporters database: […]

  2. Free Blocking and Deadlock Monitoring for SQL Server - by Kendra Little - September 29, 2016

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

  3. Decoding Key and Page WaitResource for Deadlocks and Blocking - by Kendra Little - October 17, 2016

    […] the WideWorldImporters sample database on my SQL Server […]

  4. Tracing Deadlock Graphs: Extended Events or Server Side Trace - by Kendra Little - December 27, 2016

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

  5. Converting a Datarow to a JSON object with PowerShell | SQL DBA with A Beard - December 18, 2017

    […] Lets show what happens with an example using WideWorldImporters and a query I found on Kendra Littles blogpost about deadlocks […]

Leave a Reply