0

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 spblocked

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

and wait_resource is database.dbo.table1.PK_table1

table 1 has col 1 as PK /Clustered index created , which is of data type bigint not null column

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 ?

  • Could you please include details on blocked and blocker process with their code. LCK_M_X meaning locking has escalated to table level. Also, could you please try modifying your code of delete from one set of multiple batches(say 1000 or 5000) and see if there is still blocking. – Learning_DBAdmin Oct 18 at 5:03
  • @Learning_DBAdmin: Due to sensitive information policy i cant share the exact code plus those codes are 5000+ lines so i am just giving the statements as found from our monitoring tool . Thanks i will check on the batch thing. Also i am not sure if LCK_M_X always mean it escalated to table though? – BeginnerDBA Oct 18 at 5:13
  • maybe before running EXEC spleadblocker the session executes other statements touching dbo.table1 and does not commit transaction? I would try tracing one session from beginning to end, to see what is going on. – rois Oct 18 at 9:31
  • Although you didn't elaborate on "app pooling issue", sleeping sessions with open transactions can be a symptom of improper connection management, pooling, or transaction management. Consider a scenario where a query with an explict transaction times out and the connection is returned to the pool with an open connection. tl;dr;, one can specify SET XACT_ABORT ON; to ensure transactions are rolled back after a client timeout. – Dan Guzman Oct 18 at 10:21
  • @DanGuzman: Yes we do have XACT ABORT ON for those procs showing up as lead blocker – BeginnerDBA Oct 19 at 16:50
-1

As stated by a previous user the lock type indicates that your query tries to acquire an Exclusive lock on a resource. Is your predicate (Col1=@col1) supported by in index ?

Some options for a query referencing a specific table to acquire locks on other tables can be: triggers, fks and fk cascading.

More info is required. See this post for more ideas.

Have you tried to capture the blocked process report ? See this post for how to run a server side trace to capture the "Blocked Process Report Event" constantly in the background so that when your issue occurs you do not miss it and have the data needed for analysis

| improve this answer | |
  • As mentioned in question col1 is PK Clustered index and probably why entire table is locked – BeginnerDBA Oct 19 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.