Concurrency Week: Can SELECTs Win Deadlocks?


Yes, especially when they’re part of a larger transaction.

In this example, I’m going to use a database in Read Committed Snapshot Isolation (RCSI), my favorite isolation level for building new applications from the ground up. Optimistic concurrency (aka MVCC) helps avoid a lot of blocking issues – but not all of them.

In a database with RCSI enabled, start by creating two tables:

Then start a transaction that does a lot of work, but don’t commit:

Now start a new tab or window in SSMS – we’re going to start a separate transaction in the same database, but working on Table #2 instead. We’ll call this Window #2 (because it’s 5AM when I’m writing this, and the caffeine hasn’t kicked in yet.)

That query finishes instantly because it’s a metadata-only change in modern versions (even if you set a default, as Remus explains.) Both transactions are getting along just fine right now, not blocking each other, because they’re working on different tables.

Now, let’s cause a deadlock.

In Window #2, where you did just a little bit of work, try to do much more work – but monkey with the table that’s already locked by Window #1:

Aaaaaand nothing happens – he’s blocked because Window #1 already has a lock on those rows. He won’t make progress, and he doesn’t time out. He just sits there waiting patiently.

Now switch over to Window #1 and run a select:

At first, nothing happens – because he’s blocked by Window #2. When Window #2 added a column to Table2, that schema change is a big deal, and it even blocks SELECTs. After all, the SELECT can’t have some of his rows come back without the new column, and some of them with it.

Within 5 seconds, the SELECT wins.

SQL Server’s deadlock monitor wakes up, sees that we have a Mexican standoff. Neither window can make progress until the other window gives up. SQL Server looks at which session would be the easiest to roll back, and kills that session’s work.

Window #2’s UPDATE sees:

Window #1’s SELECT won because in his transaction, he’d already done a lot of work. Window #2 hadn’t done that much – the alter table was a tiny metadata-only change. Sure, he was trying to run an update that would do a lot of work, but he hadn’t done it yet, so he was trivially easy to roll back.

In the past, I’ve often said, “In RCSI, readers don’t block writers, and writers don’t block readers” – but that’s not exactly true, as this demo indicates. However, I’m…still gonna say that, because it’s mostly true. The problem is that the OTHER writing we’d done previously in the transaction is what killed us here.

Kalen Delaney SQL Server Training

Wanna learn more?

I do – I find this stuff really interesting because it happens so fast. Locking and blocking can just appear and disappear in your servers, and it’s really hard to track down if you can’t reproduce it easily. That’s why I like learning the internals on this stuff so I can recognize the symptoms when they strike.

If you’re like me, check out Kalen Delaney’s upcoming 4-hour class, Locking and Blocking: SQL Server Concurrency Control. It’s really hard to find deep-dive internals lessons on this stuff, and Kalen knows internals. I’ll be there myself!

Previous Post
Building SQL ConstantCare®: Why We Avoided Graphs
Next Post
Concurrency Week: How Entity Framework and NHibernate Can Cause Poison RESOURCE_SEMAPHORE Waits

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.