Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

Blocking Trees in sp_HumanEventsBlockViewer

Published 2023-08-04 by Kevin Feasel

Erik Darling talks up a famous Canadian:

I know there’s been a lot of blog content about the creation of and updates to my stored procedures lately. I try to consolidate as much as possible, but this one comes from Valued Contributor©.

The one, the only, the Canadian: Michael J Swart (b|t) recently contributed a really cool piece of code that I’ve been dying to have since I created sp_HumanEventsBlockViewer: the entire blocking chain is visualized in the results.

Click through to see what it looks like, and next time you see Michael, give him a thumbs up.

Comments closed

Fending off Sessions while in Single-User Mode

Published 2023-04-03 by Kevin Feasel

Eitan Blumin just wants to switch the database type:

Today we had an interesting use case where a customer reported that one of the databases they just restored from a backup got stuck in “Single-User” mode in one of their environments.

To resolve it, I first tried running the following command:

ALTER DATABASE MyDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;

In response, I got deadlocked with the dreaded error 1205:

There were a few different attempts with no success until Eitan came up with the final script. Eitan’s analogy was to curling, though the first thing I thought of was Odysseus fighting off his wife’s suitors as he came back to claim his home.

1 Comment

Finding Blockers in Azure SQL DB + MI

Published 2022-12-28 by Kevin Feasel

Jose Manuel Jurado Diaz writes a program:

Today, I worked on a service request that our customer is looking for all blocking issues that is happening in their database. We have many articles about it Lesson Learned #22: How to identify blocking issues? – Microsoft Community Hub and in Diagnostics Settings and QDS we can collect this information but all points to that we cannot see the TSQL that is blocking and TSQL command that is blocked in an easy way. In the following script that I share as a script example we could take this one. 

First of all, please, remember that a blocking issue is normal and fundamental for any RDBMS. This script is basically when you need to understand what is happening to improve or reduce this.

I was a little surprised the answer wasn’t to use Extended Events, though this does work if you simply need something to run in ad hoc scenarios.

Comments closed

RCSI and Blocking

Published 2022-11-28 by Kevin Feasel

Michael J. Swart says don’t worry, be happy:

What’s the best way to avoid most blocking issues in SQL Server? Turn on Read Committed Snapshot Isolation (RCSI). That’s it.

Do check out Erik Darling’s comment as well for one thing to keep in mind if you turn on RCSI.

The other thing to keep in mind is that, if you have WITH(NOLOCK) hanging around everywhere in your code, you won’t get as much of a benefit with RCSI until you remove them.

Comments closed

Lock Escalation outside of Repeatable Read

Published 2022-10-21 by Kevin Feasel

Paul White continues a series on lock escalation:

When SQL Server reads data under locking read committed isolation (the default for non-cloud offerings) and chooses row-level locking granularity, it normally only locks one row at a time. The engine processes the row through parent operators before releasing the shared lock prior to locking and reading the next row.

When the engine chooses page-level locking granularity, it takes and holds a shared page lock while processing rows on the current page and then releases the lock right before acquiring a lock on the next page.

In general, SQL Server is careful to take and hold locks only when needed and only long enough to guarantee correct results, taking account of the current isolation level. When locking at row granularity, the engine can skip row locks entirely when it’s safe to do.

Read on to understand when SQL Server either gets it wrong or when exigent factors alter the story.

Comments closed

Lock Escalation Thresholds and Parallelism

Published 2022-10-06 by Kevin Feasel

Paul White complicates matters:

In part one of this series, I explained how the lock manager maintains a count of held locks per transaction, and individual access methods keep a separate lock count per HoBt for each plan operator performing data access.

Parallelism adds an extra layer of complexity because both lock counts are maintained per thread.

There are two underlying reasons for this:

Paul explains the reasons and shows off plenty of tests along the way.

Comments closed

Lock Escalation Thresholds

Published 2022-09-08 by Kevin Feasel

Paul White gets into the weeds:

This article isn’t about the act of lock escalation itself, which is already well documented and generally well understood. Some myths (like row locks escalating to page locks) persist, but challenging those yet again probably wouldn’t change much.

Instead, the question I’ll address here is exactly how and when lock escalation is triggered. Much of the documentation is incorrect or at least imprecise about this and I’ve been unable to find a correct description in other writings.

There are good reasons you haven’t seen a simple demo of lock escalation taking place at 5000 locks. I’ve seen suggestions such as lock escalation isn’t deterministic, or some types of locks don’t count toward the 5000 lock threshold. Neither of those assertions is true, but the details are interesting, as I’ll explain.

As always, Paul brings clarity to a difficult topic.

Comments closed

Blocking during Creation of Indexed Views

Published 2022-09-07 by Kevin Feasel

Tom Zika runs into a problem:

There was a need to make changes to a table with an Indexed View. Since Indexed Views must be created with SCHEMABINDING, the View must be dropped and recreated.

From past experience, I knew that this operation blocked all queries (Read/Write) that referenced any table from the View’s definition for the duration of the Clustered index creation, even under the RCSI level.

Because the index might be large and the maintenance window small, I want to do that as fast as possible.

Tom does a good job of taking us through the problem as well as the solution. Definitely worth a read if you’re creating indexed views with Enterprise Edition.

Comments closed

NOLOCK and Inserts

Published 2022-08-25 by Kevin Feasel

William Assaf shows us the bad idea of the day:

Error 1065 states “The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.”  However, NOLOCK can still dangerously be used as the source of the write. (NOLOCK can also fail with error 601 on even basic SELECT statements, but coders just aren’t always scared off by that.)

Here’s a very elementary example to share at parties in order to scare developers away from using NOLOCKs adventurously:

Read on for an example which shows the level of pain you can find yourself in with NOLOCK.

Comments closed