This article is contributed. See the original author and article here.

This week I worked on a very interesting service request about a deadlock issue when our customer is running 32 threads at the same time inserting data. I saw an important thing that may prevent a deadlock. 

 

Background:

 

  • Our customer has two tables with the following structure:

 

CREATE TABLE [dbo].[Header]
(
	[Id] UNIQUEIDENTIFIER NOT NULL, 
	CONSTRAINT [PK_Header] PRIMARY KEY CLUSTERED([Id])
)
GO

CREATE TABLE [dbo].[Detail]
(
	[Id] UNIQUEIDENTIFIER NOT NULL, 
	[HeaderId] UNIQUEIDENTIFIER NOT NULL,
	CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED([Id]),
	CONSTRAINT [FK_Detail_Header]
		FOREIGN KEY ([HeaderId])
		REFERENCES [dbo].[Header] ([Id])
)
GO

 

 

  • In our customer code, they create a transaction that insert a new row in the header table and after insert around 1000 rows in the detail table. This operation is running by 32 threads at the same time. 
  • During the execution we saw some deadlocks because 2 transactions block each other from continuing because each has locked a database resource that the other transaction needs. SQL Server handles deadlocks by terminating and rolling back transactions that were started after the first transaction. In this situation it is a KeyLock.

 

Solution:

 

  • We saw that the deadlock is happening when these a transaction needs to lock a resource that the other transaction has in exclusive mode. 
  • We have two options: 
    • Retry the operation about the transaction that was killed by SQL Server.
    • Create a new Partition Key based on the number of threads running. 

My suggestion suggested was to create a partitioned table based on the numbers of threads, for example:

 

  • Create a partition function for the 32 threads, running the following command:

 

CREATE PARTITION FUNCTION [PartitioningByInt](int) AS RANGE RIGHT FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17..32)

 

 

  • Create the partition schema based on the function defined:

 

CREATE PARTITION SCHEME [PartitionByinT] AS PARTITION [PartitioningByInt] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]...)

 

 

  • For the table header perform two operations:
    • First, include the partition key.
    • Second, include the PK to have the referential integrity with Detail table. 

 

CREATE TABLE [dbo].[Header](
       [Id] [uniqueidentifier] NOT NULL,
       [IdPartition] [int] NOT NULL)

CREATE CLUSTERED INDEX [ClusteredIndex_on_PartitionByinT_637343967904700811] ON [dbo].[Header]
( [IdPartition]) 
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartitionByinT]([IdPartition])


create UNIQUE nonclustered INDEX [PK_Header] ON [dbo].[Header] 
([Id] ASC,iDpARTITION) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartitionByinT]([IdPartition])

 

 

  • In the detail table, include the IdPartition column to maintain the same referential integrity with header table.

 

CREATE TABLE [dbo].[Detail](
       [Id] [uniqueidentifier] NOT NULL,
       [HeaderId] [uniqueidentifier] NOT NULL,
       [IdPartition] [int] NOT NULL,
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED 
(
       [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail]  WITH CHECK ADD  CONSTRAINT [FK_Detail_Header] FOREIGN KEY([HeaderId], [IdPartition])
REFERENCES [dbo].[Header] ([Id], [IdPartition])
GO

ALTER TABLE [dbo].[Detail] CHECK CONSTRAINT [FK_Detail_Header]
GO

 

 

But, what is the outcome?

 

  • As we discussed previously, at the moment of the transaction that needs to lock exclusively the PartitionKey of the Primary Key, instead of that all transactions lock the same, every transaction will lock the partition key of the partition, reducing a lot the contention and preventing any deadlock. 

 

Enjoy!

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.