Sleeping Sessions Holding Locks

Jonathan Kehayias walks us through an interesting scenario:

A recent consulting engagement was focused on blocking issues inside of SQL Server that were causing delays in processing user requests from the application. As we started to dig into the issues being experienced, it became clear that from a SQL Server standpoint, the problem revolved around sessions in a Sleeping status that were holding locks inside of the Engine. This is not a typical behavior for SQL Server, so my first thought was that there was some sort of application design flaw that was leaving a transaction active on a session that had been reset for connection pooling in the application, but this was quickly proven not to be the case since the locks were later being released automatically, there was just a delay in this occurring. So, we had to dig in further.

Click through to learn what the root cause was.

Related Posts

Defining Intent Locks

David Fowler explains what an intent lock is and why it’s useful: Let’s just imagine a World without intent locks for a moment. In that World, a user has just decided to select a row from our database. SQL at that point is going to put down a shared lock against the row. Now what’s […]

Read More

Deadlock Check Frequency

Dave Bland clarifies how frequently deadlock checks occur: Because deadlocks happen when two task permanently block each other, without a deadlock, both process will simply block forever. Of course this could never be good in a production system.  It is important that these situations be identified and dealt with in some manner.  This is where […]

Read More

Categories

July 2019
MTWTFSS
« Jun Aug »
1234567
891011121314
15161718192021
22232425262728
293031