Deadlock Reproduction

Deadlock problems are very common when multiple users connect to the same SQL Server at the same time.

Usually deadlock problems are not found until real users connect to the SQL Server in a production scenario, where it can lead to application timeouts, data loss or crashes.

This tutorial will show how to catch and eliminate a deadlock with Performance Test Tool. The deadlock is only reproducible in a multiple concurrent connections scenario, and will be not be found in a normal development scenario or QA test.

We will create a database, TemporaryTestDatabase, and create a table, CompanyTable in the database. Data will be inserted in CompanyTable, and a trigger, UpdateDateTrigger, will update a column in the same table.

Create the database, TemporaryTestDatabase:
use [master]

create database [TemporaryTestDatabase]

Create the table, CompanyTable:
use [TemporaryTestDatabase]

create table dbo.CompanyTable
	Id int identity (1, 1) not null,
	CompanyName varchar(50) not null,
	DateAdded smalldatetime null

Create the trigger:
create trigger dbo.UpdateDateTrigger on dbo.CompanyTable
after insert
	set nocount on

	update dbo.CompanyTable
	set DateAdded = getdate()
	from dbo.CompanyTable p
	inner join inserted i on i.Id = p.Id
	where p.Id = i.Id

Let's try to manually insert a row into CompanyTable:
insert into dbo.CompanyTable (CompanyName)
values ('Company1')

Everything is fine. No deadlocks.

Let's do the same in Performance Test Tool:

Set the Task Collection to be executed with 1000 concurrent connections and disable Performance Counters sampling:

The 1000 connections are started within 1 second and a deadlock occurs already on the fifth connection:

To avoid the lock on the heap, we can create an index on CompanyTable:
create unique clustered index ix_id on dbo.CompanyTable (Id)

The 1000 connections can now execute without a deadlock:

Last edited Nov 16, 2016 at 4:28 PM by larshove, version 4