Locking, Blocking, and Isolation Levels

I’m kinda weird. I get excited when I’m troubleshooting a SQL Server problem, and I keep hitting walls. I’ll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense: sp_BlitzFirst @SinceStartup = 1 showed very clearly that their…

When you find yourself using NOLOCK, consider NOSQL instead. NOLOCK has 4 big problems: Your query can see the same rows twice Your query can skip rows altogether Your query can see data that was never committed Your query can fail altogether If you’re okay with all 4 of those, and you find yourself REGULARLY…

Hey, this seems harmless. Seriously, don’t SET DEADLOCK_PRIORITY HIGH. Even setting it to low can be a bad idea. I’ve heard people say, “Well, I’ve got a big process that runs nightly, but if it gets in anybody’s way, I would rather just kill my process than slow someone else down.” Keep in mind that…

I’m waiting for the YOLOck option. When you put NOLOCK in your query, SQL Server will: Read rows twice Skip rows altogether Show you changes that never actually got committed Let your query fail with an error This is not a bug. This is by design. To understand what happens, read these blog posts in order:…

Some of y’all, bless your hearts, are really, really, really in love with NOLOCK. I’ve shown you how you get incorrect results when someone’s updating the rows, and I’ve shown how you get wrong-o results when someone’s updating unrelated rows. It doesn’t matter – there’s always one of you out there who believes NOLOCK is okay…

No, not NOLOCK. You get the wrong query results, even when your query results aren’t supposed to be changing. I’m talking about when you need to do writes, but you want your query to get along with others in high concurrency environments, without creating a blocking firestorm. SET LOCK_TIMEOUT 1000 – if you run this…

Say for some reason you can’t use identity columns or sequences, or maybe your application was built a long, long time ago – and you’ve got a table with a list of IDs to use next: DROP TABLE IF EXISTS dbo.Ids; CREATE TABLE dbo.Ids (TableName NVARCHAR(255) PRIMARY KEY CLUSTERED, CurrentId BIGINT); INSERT INTO dbo.Ids (TableName,…

When I’m troubleshooting a blocking emergency, the culprit is usually the query at the head of a blocking chain. Somebody did something ill-advised like starting a transaction and then locking a whole bunch of tables. But sometimes, the lead blocker isn’t the real problem. It’s query #2. Here’s a sample scenario: A long-running select with…

To fix blocking & deadlocks, you have 3 tools: Have enough indexes to make your queries fast, but not so many that they slow down delete/update/insert operations. (I cover that in the Mastering Index Tuning class.) Use the right isolation level for your app’s needs. (I cover that in the Mastering Server Tuning class.) Tuning…

Slapping WITH (NOLOCK) on your query seems to make it go faster – but what’s the drawback? Let’s take a look. We’ll start with the free StackOverflow.com public database – any one of them will do, even the 10GB mini one – and run this query: Transact-SQL UPDATE dbo.Users SET WebsiteUrl = 'https://www.brentozar.com/'; 12 UPDATE…

Say you’ve got a table with millions or billions of rows, and you need to delete some rows. Deleting ALL of them is fast and easy – just do TRUNCATE TABLE – but things get much harder when you need to delete a small percentage of them, say 5%. It’s especially painful if you need…

The short story: when you add a computed column that references another table, like with a scalar user-defined function, you can end up causing concurrency problems even when people didn’t really want to go see that other table, and that table is locked by someone else. Here’s my query: Transact-SQL SELECT Id, u.DisplayName, u.BadgeCount FROM…

“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…