Deadlock In Transaction With 2 tables

SKG 66 Reputation points
2021-02-07T03:16:12.273+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.2K Reputation points
    2021-02-07T11:25:18.24+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. SKG 66 Reputation points
    2021-02-07T13:15:33.737+00:00

    Thank You! Erland for your valuable suggestion.

    0 comments No comments