Deadlock In Transaction With 2 tables

Question

Hi All,

I have two tables TABLE1 and TABLE2 which have same partition schemes

I have multiple threads adding new partition to TABLE1 and TABLE2
but pass different partition values

I am observing deadlocks even though i have acquired TABLOCKX on both the tables

Please advise

CREATE STORED PROC CREATEPARTITION
@PartitionId int
AS
BEGIN TRANSACTION

SELECT TOP 1 1 FROM TABLE1(TABLOCKX)
SELECT TOP 1 1 FROM TABLE2(TABLOCKX)

--Add new partition to table 1
--Add new partition to table 1

COMMIT TRANSACTION

Answer 1

Since you did not share the deadlock, we cannot tell what is going on.

I would think that a better way to serialise the operation is to use an application lock:

EXEC sp_getapplock 'CreatePartition', 'Exclusive'

This would replace the two SELECT statements. I encourage you to read more about sp_getapplock in Books Online.

If this does not resolve the problem, you may be clashing with user processes. You need to capture the deadlock information. The easiest to do that is to enable trace flag 1222 by making it a startup parameter. This will write deadlock traces to the SQL Server errorlog.

Since deadlocks are captured by the System Health session, they should in theory be available there. However, I found this to be very unreliable.

Answer 2

Thank You! Erland for your valuable suggestion.