Locking, Blocking, and Isolation Levels

The Many Levels Of Concurrency

Last Updated February 12, 2018

“Concurrency is hard” Construction begins on the Tower of Babel, 610 BCE (colorized) When designing for high concurrency, most people look to the hardware for answers. And while it’s true that it plays an important role, there’s a heck of a lot more to it. Start Optimistic Like, every other major database vendor is optimistic…

Partition Level Locking: Explanations From Outer Space

Last Updated November 22, 2017

Erik Darling

Development, Locking, Blocking, and Isolation Levels, Partitioning

It’s not that I don’t like partitioning It’s just that most of my time talking about it is convincing people not to use it. They always wanna use it for the wrong reasons, and I can sort of understand why. Microsoft says you can partition for performance. Partitioning large tables or indexes can have the…

Live Query Plans and Blocking

Last Updated September 21, 2017

Erik Darling

Execution Plans, Indexing, Locking, Blocking, and Isolation Levels, SQL Server

You’d think this would be cool After all, you’ve got a query plan being executed LIVE ON NATIONAL TELEVISION! But it’s not. It’s actually one of those weird reasons why you still need other ways of looking at DMVs for issues. Demo Library Let’s take a closer look! First, we need an index. Indexes are…

Locking When There’s Nothing To Lock

Last Updated July 27, 2017

Erik Darling

Development, Execution Plans, Indexing, Locking, Blocking, and Isolation Levels

Demo Day We use StackOverflow for demos a lot. For all the reasons Brent mentions in his Great Post, Brent©, it’s pretty awesome. Where things get tricky is with locking demos. Sometimes the modifications can take a long time. This may be by design if you need to show long-held locks by sessions that aren’t…

Is NOLOCK Ever The Right Choice?

Last Updated February 13, 2017

Erik Darling

Locking, Blocking, and Isolation Levels, SQL Server

I’ve poked a lot of fun at NOLOCK I mean, it sucks. You can get incorrect data, and it can throw errors if your data is particularly volatile. Generally, outside of testing a query or grabbing some sample data, I’m going to avoid it. Crappy in every language But what if… What if it’s about…

Query Tuning Week: What’s the Difference Between Locking and Blocking and Deadlocking?

Last Updated April 9, 2017

Brent Ozar

Locking, Blocking, and Isolation Levels, SQL Server

If Erik takes out a lock on a row, and no one else is around to hear it, that’s just locking. There’s nothing wrong with locking by itself. It’s perfectly fine. If you’re the only person running a query, and you have a lot of work to do, you probably want to take out the…

Does Creating an Indexed View Require Exclusive Locks on an Underlying Table?

Last Updated May 16, 2019

Kendra Little

Indexing, Locking, Blocking, and Isolation Levels, SQL Server

An interesting question came up in our SQL Server Performance Tuning course in Chicago: when creating an indexed view, does it require an exclusive lock on the underlying table or tables? Let’s test it out with a simple indexed view run against a non-production environment. (AKA, a VM on my laptop running SQL Server 2014.)…

Staging Data: Locking Danger with ALTER SCHEMA TRANSFER

Last Updated February 13, 2017

Kendra Little

Locking, Blocking, and Isolation Levels, SQL Server

Developers have struggled with a problem for a long time: how do I load up a new table, then quickly switch it in and replace it, to make it visible to users? There’s a few different approaches to reloading data and switching it in, and unfortunately most of them have big problems involving locking. One…

Can DBCC SHRINKFILE Cause Blocking in SQL Server?

Last Updated January 11, 2018

Kendra Little

Locking, Blocking, and Isolation Levels, SQL Server

It sure can. The lock risks of shrinking data files in SQL Server aren’t very well documented. Many people have written about shrinking files being a bad regular practice— and that’s totally true. But sometimes you may need to run a one-time operation if you’ve been able to clear out or archive a lot of…

Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

Last Updated August 15, 2024

Kendra Little

Index Maintenance, Locking, Blocking, and Isolation Levels, SQL Server

One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M). Here’s one way this can become a big problem: An online index rebuild starts against a large index A…

Read Committed Snapshot Isolation: Writers Block Writers (RCSI)

Last Updated February 13, 2017

Kendra Little

Locking, Blocking, and Isolation Levels, SQL Server

When learning how Read Committed Snapshot Isolation works in SQL Server, it can be a little tricky to understand how writes behave. The basic way I remember this is “Readers don’t block writers, writers don’t block readers, but writers still block writers.” But that’s not so easy to understand. Let’s take a look at a simple test…

Capturing Deadlocks in SQL Server

Last Updated February 13, 2017

Jes Schultz

Locking, Blocking, and Isolation Levels, SQL Server

What’s a deadlock? Well, let’s say there’s a fight going on between Wonder Woman and Cheetah, and, in the same room, a fight between Batman and Mr. Freeze. Wonder Woman decides to help Batman by also attempting to throw her lasso around Mr. Freeze; Batman tries to help Wonder Woman by unleashing a rope from…

Finding Blocked Processes and Deadlocks using SQL Server Extended Events

Last Updated March 14, 2019

Jeremiah Peschka

Locking, Blocking, and Isolation Levels, SQL Server

A lot of folks would have you think that Extended Events need to be complicated and involve copious amounts of XML shredding and throwing things across the office. I’m here to tell you that it doesn’t have to be so bad. Collecting Blocked Process Reports and Deadlocks Using Extended Events When you want to find…

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide

Last Updated July 9, 2024

Kendra Little

Locking, Blocking, and Isolation Levels, SQL Server

154

How to change isolation levels without losing your marbles. A client said the coolest thing to me the other day. He said, “We talked before about why we would want to start using optimistic locking in our code. How do we get there?” If you’re not a SQL Server geek, that comment probably doesn’t even…

Five Ways to Fight Blocking Video

Last Updated February 13, 2017

Kendra Little

Locking, Blocking, and Isolation Levels, SQL Server

What do *you* do when your database has a problem with blocking? Most people reach for the “NOLOCK” hint. Bad news: that may introduce other problems, and it doesn’t truly resolve the root problem in your application code. In this video Kendra Little summarizes five big-picture application changes that fight blocking long term. This 300…

The Difficulty with Deadlocks

Last Updated February 13, 2017

Jeremiah Peschka

Locking, Blocking, and Isolation Levels

Deadlocks can kill an application’s performance. Users will complain about the app being slow or broken. Developers will ask the DBA to fix the problem, DBAs will push the problem back on developers. The next thing you know, the office looks like Lord of the Flies. What is a Deadlock? A deadlock occurs when two…

SQL Server Locking and You!

Last Updated February 13, 2017

Jeremiah Peschka

Locking, Blocking, and Isolation Levels, SQL Server

Did you know that SQL Server’s locking has a name? It’s called two-phase locking. If we’re really getting specific about it, SQL Server uses what’s called strong strict two-phase locking or SS2PL. We’ll get there in a few minutes, right now we’re going to take a look at what makes up two-phase locking. But First,…

It’s a Lock: Due Diligence, Schema Changes, and You

Last Updated February 13, 2017

Kendra Little

Locking, Blocking, and Isolation Levels

It’s morning standup, and someone says, “It’s no big deal, we just need to add a couple of columns. It’s already in the build, it works fine.” The next time this happens, stop and say, “Let’s take a closer look at that.” When you write schema changes for a relational database, take the time to…

Tuning SQL Server Transactions – and Meetings

Last Updated February 13, 2017

Brent Ozar

Locking, Blocking, and Isolation Levels

Meetings are like bad blocking queries – they tie up precious resources and stop other people from getting work done. Today, let’s talk about how to optimize both of ’em by doing homework ahead of time and minimizing the number of objects in the transaction.