Hi there,
I am currently trying to resolve a sql server deadlock issue. Someone in
the team is adamant that the deadlock is caused by a new index we've added.
I'm not sure I've ever heard of an index causing a deadlock, but I'm
always interested in something new to me, anyway! I have attached a
deadlock graph from SolarWinds Sql Sentry Plan Explorer and I'm hoping
someone else can help me understand it.
There are essentially 2 queries. (I've renamed all objects)
The first query is along the lines of:
SELECT DISTINCT *
FROM dbo.Shipping
WHERE StatusID=@StatusID
AND IsLocked=@IsLocked AND TypeID=@TypeID AND ShipDate < @ShipDate.
The field list is expanded and there are lots of fields. It was generated
inside a third party tool.
The second query is an UPDATE statement in a stored proc, along the lines
of:
UPDATE dbo.Shipping
SET IsLocked=@IsLocked,
LockedBy=CASE WHEN @Locked=1 THEN @PersonWhoLocked ELSE NULL END,
LockedDate=CASE WHEN @Locked=1 THEN GetUtcDate() ELSE ModificationDate END
WHERE ShippingID=@ShippingID
Process 1 (126) is the SELECT, which is the deadlock victim
Process 2 (133) is the UPDATE stored proc
Process 3 (139) is the UPDATE stored proc
[image: deadlock.png]
I'm going to take a stab at what might be happening. Hopefully someone can
fill the gaps in my knowledge.
A deadlock occurs when Process A has a lock on Resource 1 and wants to
acquire a lock on Resource 2, while Process B has a lock on Resource 2 and
wants to acquire a lock on Resource 1.
So reading this graph, it looks like:
The 2 resources are the Page Lock on dbo.Shipping, and the Key Lock on the
shipping Primary Key.
So on the graph it looks to me like Process 1 holds a lock on the Page Lock
resource and wants a lock on the Key Lock. Process 3 holds a lock on the
Key lock resource and wants a lock on the Page Lock resource
So:
(1) The SELECT occurs on the shipping table and it takes a Page lock (S)
(2) The first UPDATE occurs (133) and that requires an exclusive lock, but
it can't get it because there's already a Shared lock, so it puts in an
Intent to get an Exclusive lock (IX)
(3) The second UPDATE occurs and it acquires an Exclusive lock (X) on the
Primary Key Index.
(4) Not sure what happens here and how this is relevant
(5) The second UPDATE requests an exclusive lock on the Page of Shipping
records.
(6) Meanwhile the SELECT tries to get a Shared lock on one of the keys that
are locked by the second UPDATE statement
Can anyone help me understand what is going on here and how this is going
to help me prevent further deadlocks?
Kind regards,
Tony
