Deadlock issue in SQL Server 2008 R2 which does not make sense

Question

Hi,

I have a deadlock happening on my system that I cannot explain.

I have a table called tblPPObject which has a PK on a int column called InsertionID.

I have 3 other tables which have a FK relation with the PK on the tblPPObject table.

I have a number of threads that are saving to the database at the same time and this is where I have seen the deadlock.

The deadlock is always between the merge procedure for tblPPObject and the merge procedure for one of the other 3 tables (all of which have a FK relation to the tblPPObject table). The deadlock graph is shown below:

So the deadlock is on the primary key column of the tblPPObject table. The objects the are in conflict are as follows:

ALTER procedure [dbo].[spMergePPObject](@temptable varchar(max), @updatetime datetime)
as
begin
	
	begin try

		set nocount on
		
		declare @sqlstatement nvarchar(max)
		
		set @sqlstatement = 'merge tblPPObject as t
		using (select * from ' + @temptable + ') as s
		on t.InsertionID = s.InsertionID
		when matched and s.UpdateID is not null then update set UpdateID = s.UpdateID, UpdateTime = @updatetime, TypeNameID = s.TypeNameID
		when not matched then insert (InsertionID, ObjectID, TypeNameID, UpdateId, UpdateTime) values (s.InsertionID, s.ObjectID, s.TypeNameID, s.UpdateID, @updatetime);'

		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime

		exec('drop table ' + @temptable)

	end try
	begin catch

		declare @errormessage varchar(256)

		-- Get the error message
		select @errormessage = ERROR_MESSAGE()

		-- Raise an error and return
		raiserror('Error updating entries in the tblPPObject Table. %s', 16, 1, @errormessage)

	end catch

end

and

ALTER procedure [dbo].[spMergePPObjectChildObject](@temptable varchar(max), @updatetime datetime)
as
begin
	
	begin try

		set nocount on
			
		declare @sqlstatement nvarchar(max)
				
		set @sqlstatement = 'merge tblPPObjectChildObject as t
		using (select * from ' + @temptable + ') as s
		on t.ChildInsertionID = s.ChildInsertionID
		when matched and s.UpdateID is not null then update set UpdateID = s.UpdateID, UpdateTime = @updatetime, IsDeleted = 0
		when not matched then insert (InsertionID, ChildInsertionID, PropertyMapNameID, UpdateID, UpdateTime, IsDeleted) values (s.InsertionID, s.ChildInsertionID, s.PropertyMapNameID, s.UpdateID, @updatetime, 0);'

		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime

		set @sqlstatement = 'update tblPPObjectChildObject
		set IsDeleted = 1, UpdateTime = @updatetime
		from tblPPObjectChildObject as t
		join (select distinct InsertionID, UpdateID from ' + @temptable + ') as s
		on t.InsertionID = s.InsertionID
		where s.UpdateID != t.UpdateID'

		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime

		exec('drop table ' + @temptable)

	end try
	begin catch

		declare @errormessage varchar(256)

		-- Get the error message
		select @errormessage = ERROR_MESSAGE()

		-- Raise an error and return
		raiserror('Error updating entries in the tblPPObjectChildObject Table. %s', 16, 1, @errormessage)

	end catch

end

I am confused why the spMergePPObjectChildObject procedure is requiring a lock on the tblPPObject table. I have also got a non-clustered index on the InsertionID column in the tblPPObject so I did not think that the spMergePPObjectChildObject procedure would require a lock on the tblPPObject primary key column.

Has anyone got any ideas?

Thanks

Ian

Answer 1

Ian

What if you change to ---no need SELECT *

using ' + @temptable + ' as s

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Any particular reason why the PK column (InsertionID) of tblPPObject also has a non-clustered index defined on it as well?  Is PK also a clustered index?

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 3

Hi,

The PK column on the tblPPObject table is a clustered index on the InsertionID column.

I put the non-clustered index on the table as well (on the InsertionID column) as I thought that this may help the other queries in not having to access the PK of the tblPPObject table but this does not seem to be the same.

Thanks

Ian

Answer 4

You should remove the non-clustered index as it's not required.

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 5

I have taken this non-clustered index off and I am still having the same deadlock problem.

Ian

Answer 6

I have made that change and I still get the deadlock.

Thanks

Ian

Answer 7

I have made that change and I still get the deadlock.

Thanks

Ian

Sorry. I should have clarified that my question was not related to helping your deadlock. It was just an observation on a design flaw.

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 8

associatedObjectId=72057594057457664 --replace with your
keylock hobtid=72057594057457664----replace with your

--what does it return?

SELECT o.name, i.name 
FROM sys.partitions p 
JOIN sys.objects o ON p.object_id = o.object_id 
JOIN sys.indexes i ON p.object_id = i.object_id 
AND p.index_id = i.index_id 
WHERE p.hobt_id = 72057594065256448 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 9

SELECT o.name, i.name 
 FROM sys.partitions p 
 JOIN sys.objects o ON p.object_id = o.object_id 
 JOIN sys.indexes i ON p.object_id = i.object_id 
 AND p.index_id = i.index_id 
 WHERE p.hobt_id = 72057594132037632

This returns:

tblPPObject	PK_tblPPObject

which is what I would expect. The look is on PK_tblPPObject.

Thanks

Ian

Answer 10

Ian

I am not sure how does spMergePPObjectChildObject stored procedure is related to the deadlock? What is @table --real name=tblPPObject?

>>>The deadlock is always between the merge procedure for tblPPObject and the merge procedure for one of the >>other 3 tables (all of which have a FK relation to the tblPPObject table).

Can you show us the order you are calling stored procedures?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 11

Hi,

I did not describe the FK and PK very well at the start.

tblPPObject has a PK on InsertionID which is a clustered index.

tbiPPObjectChildObject has a PK on ChildInsertionID which is a clustered index.

tblPPObjectChildObject has a FK on ChildInsertionID which references the PK on the tblPPObject.

The InsertionID in tblPPObjectChildObject must also be in the tblPPObject table but I cannot add a FK because SQL complains that there is a cyclic issue.

The table name passed in is a temp table created on the fly which contains the data. The temp table is never tblPPObject.

The save routine always calls the procedures in the same order:

spMergePPObject

spMergePPObjectChildObject

spMergePPObjectChildObjectList

spMergePPObjectProperty

Does that help?

Ian

Answer 12

Hi,

We think that we may have found something based on this article:

http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

Looking at the deadlock graph two things are happening and both are doing a merge.

The spMergePPObject procedure is taking out an exlusive lock on the tblPPObject table index (so that it can work out what to insert and update).

The spMergePPObjectChildObject procedure then also takes out an exlusive lock on the tblPPObject table index (I am not sure how it can do this at the same time as spMergePPObject?).

The spMergePPObject procedure then requests an update lock on tblPPObject and cannot get it.

The spMergePPObjectChildObject then requests an update lock on the tblPPObject table and cannot get it.

You then have deadlock. It seems to be the order in which the merge function gets its locks causes problems in that the order in which the locks are obtained is not deterministic i.e. it depends on whether or not an insert or update is being done.

Does any this make any sense?

Thanks

Ian

Answer 13

Hi,

I think that we single biggest quesiton here is

Why does the spMergeObjectChildObject procedure need an exclusive lock on the PK of the tblPPObject table?

Since it does require an exclusive lock there are two sp's with an exclusive lock to the same index and they both need to access this index and they cannot.

Thanks

Ian

Answer 14

Can you provide us with values for @temtable that those procedures are accepted

I think the problem is spMergePPObject with concurrent running users , have you  thought about SNAPSHOP ISOLATION LEVEL?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 15

We are using snapshot isolation level (well read committed snapshot actually).

I cannot easily provide details of the values being passed in when the issue occurs unfortunately but I don't think that this is actually relevant.

Thanks

Ian

Answer 16

Hi,

I just wanted to give you a bit more background.

I have attached the stored procedures that we are using. In our save routine we are calling them in the following order:

spMergePPObject
spMergePPObjectChildObject
spMergePPObjectChildObjectList
spMergePPObjectProperty
spMergePPObjectBlobProperty

So if we get two concurrent saves then I guess that the lock escalation could possibly cause a deadlock.

I have attached the sp's in question below.

CREATE procedure [dbo].[spMergePPObject](@temptable varchar(max), @updatetime datetime)
as
begin
	
	begin try

		set nocount on
		
		declare @sqlstatement nvarchar(max)
		
		set @sqlstatement = 'merge tblPPObject as t
		using (select * from ' + @temptable + ') as s
		on t.InsertionID = s.InsertionID
		when matched and s.UpdateID is not null then update set UpdateID = s.UpdateID, UpdateTime = @updatetime, TypeNameID = s.TypeNameID
		when not matched then insert (InsertionID, ObjectID, TypeNameID, UpdateId, UpdateTime) values (s.InsertionID, s.ObjectID, s.TypeNameID, s.UpdateID, @updatetime);'

		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime

		exec('drop table ' + @temptable)

	end try
	begin catch

		declare @errormessage varchar(256)

		-- Get the error message
		select @errormessage = ERROR_MESSAGE()

		-- Raise an error and return
		raiserror('Error updating entries in the tblPPObject Table. %s', 16, 1, @errormessage)

	end catch

end
GO
CREATE procedure [dbo].[spMergePPObjectBlobProperty](@temptable varchar(max), @updatetime datetime)
as
begin
	
	begin try

		set nocount on
			
		declare @sqlstatement nvarchar(max)
	
		set @sqlstatement = 'merge tblPPObjectBlobProperty as t
		using (select * from ' + @temptable + ') as s
		on t.InsertionID = s.InsertionID and t.PropertyMapNameID = s.PropertyMapNameID
		when matched then update set [Value] = s.Value, UpdateID = s.UpdateID, UpdateTime = @updatetime, IsDeleted = 0
		when not matched then insert (InsertionID, PropertyMapNameID, Value, UpdateId, UpdateTime, IsDeleted) values (s.InsertionID, s.PropertyMapNameID, s.Value, s.UpdateID, @updatetime, 0);'

		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime

		set @sqlstatement = 'update tblPPObjectBlobProperty
		set IsDeleted = 1, UpdateTime = @updatetime
		from tblPPObjectBlobProperty as t
		join (select distinct InsertionID, UpdateID from ' + @temptable + ') as s
		on t.InsertionID = s.InsertionID
		where s.UpdateID != t.UpdateID'

		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime
		
		exec('drop table ' + @temptable)

	end try
	begin catch

		declare @errormessage varchar(256)

		-- Get the error message
		select @errormessage = ERROR_MESSAGE()

		-- Raise an error and return
		raiserror('Error updating entries in the tblPPObjectBlobProperty Table. %s', 16, 1, @errormessage)

	end catch

end
GO
CREATE procedure [dbo].[spMergePPObjectChildObject](@temptable varchar(max), @updatetime datetime)
as
begin
	
	begin try

		set nocount on
			
		declare @sqlstatement nvarchar(max)
				
		set @sqlstatement = 'merge tblPPObjectChildObject as t
		using (select * from ' + @temptable + ') as s
		on t.ChildInsertionID = s.ChildInsertionID
		when matched and s.UpdateID is not null then update set UpdateID = s.UpdateID, UpdateTime = @updatetime, IsDeleted = 0
		when not matched then insert (InsertionID, ChildInsertionID, PropertyMapNameID, UpdateID, UpdateTime, IsDeleted) values (s.InsertionID, s.ChildInsertionID, s.PropertyMapNameID, s.UpdateID, @updatetime, 0);'

		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime

		set @sqlstatement = 'update tblPPObjectChildObject
		set IsDeleted = 1, UpdateTime = @updatetime
		from tblPPObjectChildObject as t
		join (select distinct InsertionID, UpdateID from ' + @temptable + ') as s
		on t.InsertionID = s.InsertionID
		where s.UpdateID != t.UpdateID'

		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime

		exec('drop table ' + @temptable)

	end try
	begin catch

		declare @errormessage varchar(256)

		-- Get the error message
		select @errormessage = ERROR_MESSAGE()

		-- Raise an error and return
		raiserror('Error updating entries in the tblPPObjectChildObject Table. %s', 16, 1, @errormessage)

	end catch

end
GO
CREATE procedure [dbo].[spMergePPObjectChildObjectList](@temptable varchar(max), @updatetime datetime)
as
begin
	
	begin try

		set nocount on
			
		declare @sqlstatement nvarchar(max)
	
		set @sqlstatement = 'merge tblPPObjectChildObjectList as t
		using (select * from ' + @temptable + ') as s
		on t.ChildInsertionID = s.ChildInsertionID
		when matched then update set t.SortIndex = s.SortIndex, t.UpdateID = s.UpdateID, t.InsertionID = s.InsertionID, UpdateTime = @updatetime, IsDeleted = 0
		when not matched then insert (InsertionID, ChildInsertionID, SortIndex, UpdateID, UpdateTime, IsDeleted) values (s.InsertionID, s.ChildInsertionID, s.SortIndex, s.UpdateID, @updatetime, 0);'

		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime

		set @sqlstatement = 'update tblPPObjectChildObjectList
		set IsDeleted = 1, UpdateTime = @updatetime
		from tblPPObjectChildObjectList as t
		join (select distinct InsertionID, UpdateID from ' + @temptable + ') as s
		on t.InsertionID = s.InsertionID
		where s.UpdateID != t.UpdateID'
	
		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime

		exec('drop table ' + @temptable)

	end try
	begin catch

		declare @errormessage varchar(256)

		-- Get the error message
		select @errormessage = ERROR_MESSAGE()

		-- Raise an error and return
		raiserror('Error updating entries in the tblPPObjectChildObjectList Table. %s', 16, 1, @errormessage)

	end catch

end
GO
CREATE procedure [dbo].[spMergePPObjectProperty](@temptable varchar(max), @updatetime datetime)
as
begin
	
	begin try

		set nocount on
		
		declare @sqlstatement nvarchar(max)
				
		set @sqlstatement = 'merge tblPPObjectProperty as t
		using (select * from ' + @temptable + ') as s
		on t.InsertionID = s.InsertionID and t.PropertyMapNameID = s.PropertyMapNameID
		when matched then update set [Value] = s.Value, UpdateID = s.UpdateID, UpdateTime = @updatetime, IsDeleted = 0
		when not matched by target then insert (InsertionID, PropertyMapNameID, Value, UpdateId, UpdateTime, IsDeleted) values (s.InsertionID, s.PropertyMapNameID, s.Value, s.UpdateID, @updatetime, 0);'
	
		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime

		set @sqlstatement = 'update tblPPObjectProperty
		set IsDeleted = 1, UpdateTime = @updatetime
		from tblPPObjectProperty as t
		join (select distinct InsertionID, UpdateID from ' + @temptable + ') as s
		on t.InsertionID = s.InsertionID
		where s.UpdateID != t.UpdateID'
	
		execute sp_executesql @sqlstatement, N'@updatetime datetime', @updatetime = @updatetime
	
		exec('drop table ' + @temptable)

	end try
	begin catch

		declare @errormessage varchar(256)

		-- Get the error message
		select @errormessage = ERROR_MESSAGE()

		-- Raise an error and return
		raiserror('Error updating entries in the tblPPObjectProperty Table. %s', 16, 1, @errormessage)

	end catch

end
GO

Any help would be appreciated.

Thanks
Ian

Answer 17

I don't have any answers for you, and in general I would not expect those statements to cause deadlock, either.  But a couple of questions and thoughts.  If there are a lot of rows being merged in any one call, you might get some lock escalation, but the question is what is the order of locking and holding. 

You say you're in read-commited snapshot isolation which of course we'd all assume would *help*!  You haven't said anything about the calls being wrapped in transactions, and I'm wondering if turning the isolation level UP to repeatable read might help?  You might get more blocking, but no deadlocks.

I wonder if this constitutes a bug, one would not expect one merge to deadlock another merge under any isolation level.

Josh