question

agepeder-8878 avatar image
0 Votes"
agepeder-8878 asked MelissaMa-msft edited

Need help with resolving deadlock

Hi,

I have started getting deadlocks in one of my batch processed and have difficulties figuring out how to resolve it.

Deadlock graph and table structure is attached to the post.
For a start I dont understand why a pocess can have an active lock on a page and at the same time request what seems like an additional lock on the same page which cannot be granted.

122837-image.png

I would be gratefull if anyone could point me in a direction :)

122883-ht-mainitemtable.txt
122838-rename-xml-to-xdl-before-opening-deadlock-modified.xml


sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I did not analyse that deadlock in detail. It looks quite complicated, as it both includes parallelism and locks.

What I found is that there are two processes involved, spids 91 and 92. I see that both of them are Agent jobs step. One of them runs

EXEC dbo.[Out_04.01.ItemLocation2] @db = N'webfi'

The other process has webes instead of webfi.

The obvious question is: why are there two jobs, and not one to run both things? (Maybe there is an equally obvious answer, but not knowing you system, it is not obvious to me.)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.