Category: Locks, Blocks, and Deadlocks

Jana Sattainathan diagnoses a deadlocking issue:

We know what deadlocks are and some of the common reasons they happen. If you need a refresher, I recommend this good article. I am not going to rehash what has already been said although these high level points are worth noting to resolve them:

1) Examine known Parallelism (where you have parallelized jobs)
2) Examine unknown Parallelism (unknown jobs or users interfere with your jobs in parallel)
3) Arrange order of tables doing DML to be the same across all code. E.g., Always Customers first, Orders second, OrderDetails third.
4) Examine the indexes on the affected tables to eliminate full-table scans
5) Reduce the amount of time spent in a transaction
6) Update in chunks especially if updating/deleting across sessions
7) Avoid RBAR (Row By Agonizing Row) CRUD operations! Do statement based mass changes.

Read on to understand Jana’s situation and solution.

Andy Mallon shows us that the OBJECT_NAME() function does not care about your READ UNCOMMITTED transaction isolation level:

That’s just going to sit there and wait, and wait, and wait, and wait. Because the table was created inside a transaction, the metadata about the table is uncommitted, and thus unavailable to the second session. The result is that my second session waits & waits & waits until the first session is committed (or rolled back).

OK, so it’s being blocked due to an uncommitted transaction. We could try doing dirty reads, right? I’ll kill my second session that’s been blocked, and I’ll throw in SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;. That’s like just like using a nolock hint:

USE AM2_WTF;
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT OBJECT_NAME(581577110);

What the… It’s still being blocked by the uncommitted CREATE TABLE. 

Read on to understand why, and what you can do about it.

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 going to happen when another user decides to modify a bunch of rows? Now because of the number or rows involved in this modification, SQL is going to want to take out an exclusive page lock. Where’s the issue here?

Read on to learn what the issue is.

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.

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 SQL Server database engine steps in, it is frequently searching the lock manager looking for deadlocks.

Click through for the answer.

Max Vernon has a couple scripts to analyze deadlocks in SQL Server:

Deadlock detection and analysis in SQL Server is important for the overall health of affected applications. This post shows how to setup an Extended Events session to capture deadlock details. It also provides a stored procedure to capture details from the Extended Events session. The stored procedure enables simplified permanent storage of those deadlock detection events. Next, we’ll set up a SQL Server Agent Job to run the stored procedure on an ongoing basis. Finally, we’ll see several examples of how to query the captured events. These queries support making the necessary changes to both the application and database design.

Click through for a description of what a deadlock is as well as scripts to help find and fix them.