I am having a strange blocking problem which i am not able to understand.
During a slighter more volume due to some app pooling issues we see sleeping spids with open transactions, so those SP's tend to be lead blocker and blocks few delete statements.
Yes we are aware on sleeping SPID and working with app team to fix the issue, but i am not able to understand why on below
SP's which leave their session open say
EXEC spleadblocker does not access any of the table underneath within its code i.e
it blocks a simple statement like below within an SP say
DELETE from dbo.table1 WHERE Col1=@col1
OPTION (QUERYTRACEON 9481)
spleadblocker does not have any thing to do with table "table1" and should not blocking therefore above statement
Waittype is LCK_M_X
wait_resource is database.dbo.table1.PK_table1
table 1 has
PK /Clustered index created, which is of data type
Why an SP with no correlation to this table will create blocking and is there a way i can avoid that PK scan from that delete statement to avoid being blocked?
UPDATE After more research below is what we found, say the very first SP which comes
SP1leablocker for spid 66 and had its transaction open with status sleeping for about a min. This session block the delete statement above and yes they have table 1 in SP1leablocker code, so explains the blocking
Later, on above spid 66, multiple times we see different SP's running and creates a blocking chain for that delete statement of around 10 mins because that SPID 66 gets connection from various other SP's with status sleeping and open tran
Is there a way to avoid blocking on DELETE statement ?