Locking Hints Make Everything Confusing

King Of The DMV


Many people will go their entire lives without using or seeing a lock hint other than NOLOCK.

Thankfully, NOLOCK only ever leads to weird errors and incorrect results. You’ll probably never have to deal with the stuff I’m about to talk about here.

But that’s okay, you’re probably busy with the weird errors and incorrect results.

Fill The Void


It doesn’t matter who you are, or which Who you use, they all look at the same stuff.

If I run a query with a locking hint to use the serializable isolation level, it won’t be reflected anywhere.

SELECT 
    u.*
FROM dbo.Users AS u WITH(HOLDLOCK)
WHERE u.Reputation = 2;
GO 100

Both WhoIsActive and BlitzWho will show the query as using Read Commited.

EXEC sp_WhoIsActive 
    @get_task_info = 2,
    @get_additional_info = 1;

EXEC sp_BlitzWho 
    @ExpertMode = 1;

This isn’t to say that either of the tools is broken, or wrong necessarily. They just use the information available to them.

ah well

Higher Ground


If you set the isolation level at a higher level, they both pick things up correctly.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT 
    u.*
FROM dbo.Users AS u WITH(HOLDLOCK)
WHERE u.Reputation = 2;
GO 100
gratz

Deadlocks, Too


If we set up a deadlock situation — and look, I know, these would deadlock anyway, that’s not the point — we’ll see the same isolation level incorrectness in the deadlock XML.

BEGIN TRAN

UPDATE u
    SET u.Age = 1
FROM dbo.Users AS u WITH(HOLDLOCK)
WHERE u.Reputation = 2;

UPDATE b
    SET b.Name = N'Totally Tot'
FROM dbo.Badges AS b WITH(HOLDLOCK)
WHERE b.Date >= '20140101'

ROLLBACK

Running sp_BlitzLock:

EXEC sp_BlitzLock;
grousin’

 

Again, it’s not like the tool is wrong. It’s just parsing out information from the deadlock XML. The deadlock XML isn’t technically wrong either. The isolation level for the transaction is read committed, but the query is asking for more.

The problem is obvious when the query hints are right in front of you, but sometimes people will bury hints down in things like views or functions, and it makes life a little bit more interesting.

Thanks for reading!

A Word From Our Sponsors


First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.

Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.

So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.

Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.

I’m hoping that I can make enough in training bucks to make that possible.

Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.

From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.

Everything

Everything

Everything

Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.

Thank for reading, and for your support.

Isolation Level <> Locking

Different Engines


I seem to have gotten quite a few questions about this lately, so I’m going to write down some thoughts here.

It’s probably crappy form for a blog post, but what the heck.

Queries Decide The Isolation Level


They can choose anything from read uncommitted to serializable. Read uncommitted is the more honest term for what’s going on. When people see the “nolock” hint, they often assume that means their query won’t take any locks. What it really means is that it will ignore locks taken by other queries. The more accurate term would be “norespect”. This is probably what leads to confusion: reading uncommitted data sounds different than not locking data. But they’re both the same.

The Storage Engine Decides Which Locks Are Used


And if lock escalation is attempted. The storage engine will respect the query’s isolation level, and any table-level settings related to lock granularity, like not allowing row or page locks. It may not fully respect any query level hints regarding lock granularity.

One thing that helps reduce the chance of lock escalation is having a good index to help your modification query find rows. Though if you need to find a million rows, don’t expect SQL Server to happily take a million row locks, just because of an index.

Batching modifications is one way to avoid lock escalation when you need to modify a lot of rows, though it isn’t always possible to do this. If for some reason you need to roll the entire change back, you’d have to keep track of all the committed batches somewhere, or wrap the entire thing in a transaction (which would defeat the purpose, largely).

One thing that increases the chance of lock escalation is having many indexes present on a table. For inserts and deletes, all of those indexes will get touched (unless they’re filtered around the rows to be inserted or deleted. For updates, any indexes containing the column(s) to be modified will need to be touched (again, barring filtering around the updated portion). Lock counts are cumulative across objects.

Not All “Blocking” Is “Locking”


In other words, when you see queries being blocked, there may not be an LCK wait involved. Some “blocking” can happen with resource contention, whether it’s physical (CPU, memory, disk), logical (like if there’s latch or spinlock contention), or even programmatic (if you’re lucky enough to see the source code).

This can happen in tempdb if you’re creating a lot of objects rapid-fire, even if you’re using table variables. Table variables can avoid some of the overhead that temp tables incur under high frequency execution, but not all of it.

Thanks for reading!

Index Key Column Order And Locking

Fall Guy


Let’s say we have a super important query. It’s not really important.

None of this is important.

SELECT   u.DisplayName, u.Reputation, u.CreationDate
FROM     dbo.Users AS u
WHERE    u.CreationDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND      u.Reputation < 6
ORDER BY u.CreationDate DESC;

Maybe it’ll find users who created accounts in the last day who haven’t gotten any upvotes.

Shocking find, I know.

An okay index to help us find data and avoid sorting data would look like this:

CREATE INDEX ix_apathy 
    ON dbo.Users(CreationDate DESC, Reputation);

So now we know whose fault it is that we have this index, and we know who to blame when this happens.

Blocko


UPDATE u
SET u.LastAccessDate = GETDATE()
FROM dbo.Users AS u
WHERE u.Reputation = 147;
Scanno
Objectified

What’s going on here is that the optimizer chooses our narrower index to find data to update.

It’s helpful because we read far less pages than we would if we just scanned the clustered index, but the Reputation column being second means we can’t seek to rows we want.

The optimizer isn’t asking for a missing index here, either (okay, I don’t blame it for a query that runs in 145ms, but stick with me).

Switcheroo


If we change our index to have Reputation first, something nice happens.

To this query.

CREATE INDEX ix_whatever 
    ON dbo.Users(Reputation, CreationDate DESC);
Sought
Only Keys Now

With index order switched, we take more fine-grained locks, and we take them for a shorter period of time.

All That For This


If you have a locking problem, here’s what you should do:

  • Look at your modification queries that have WHERE clauses, and make sure they have the right indexes
  • Look at your modification queries that modify lots of rows, and try batching them
  • If your modification queries are horror shows, see if you can separate them into parts
  • If your critical read and write queries are at odds with each other, look into an optimistic isolation level

Thanks for reading!

Should Optimistic Be The Default Isolation Level?

Two Words


Alright, I’ve lied to you already. There are way more than two words involved, here.

I recently had breakfast (JUST BREAKFAST, GOD) with Microsoft’s Most Handsome Man™, and the topic came up.

Partially because there are such fundamental changes coming to SQL Server, and also because it’s already the default in Azure SQL DB.

If I had to name a redeeming quality of Azure SQL DB, that would be it.

Unboxing


I’ve seen pessimistic locking causing problems and heartache on many, many servers.

While it wasn’t totally the fault of the isolation level (query and index tuning was needed, and there was some other silliness), it shouldn’t take that kind of dedication to make It Just Run Faster©.

Possibly the worst side effect is people leaning heavily on reading dirty data (via read uncommitted/nolock) to avoid issues.

You can’t preach about the evils of dirty reads without offering ways to avoid blocking.

Yes, I’m looking at you.

You all cackle at seeing NOLOCK everywhere, but I don’t hear much about solving blocking problems without it.

Standards and Futures


Right now “other major database platforms” offer optimistic locking as the default.There are implementation differences, but the net result is the same.

Readers and writers don’t suffer locking contention, and only fully committed data is read.

SQL Server currently offers optimistic locking via Read Committed Snapshot Isolation (RCSI from here), and Snapshot Isolation (SI from here).

Right now, they both send row versions to tempdb. But in the works for SQL Server 2019 is a feature called Accelerated Database Recovery (ADR from here). The mechanism that makes this possible is a Persistent Version Store (PVS from here) that’s local to the database, not tempdb.

While RCSI and SI could end up sending quite a bit of data to tempdb, which could cause contention there without a lot of precautions and setup work, any potential contention from ADR would be localized.

I know, Microsoft has been addressing tempdb, too. Setup now configures tempdb in a mostly sane way, and trace flags 1117 and 1118 are the default behavior in 2016+. That should make optimistic locking a more comfortable setting for people, but no. No. I hardly ever see it in use.

Dependencies


Breaking changes are hard. I know, there are people out there who depend on pessimistic locking for certain patterns to maintain correctness.

The thing is, it’s a lot easier to have just these processes use pessimistic locking while allowing the rest of the us to not have to sprinkle NOLOCK hints every which where to get a report to run.

An example of a process that requires some locking hints is Paul White’s post about Sequence Tables.

At this point in time, if you’re implementing a queue in SQL Server, you should be the one learning about how to get the kind of locking you need to get it working. Normal people who just want their app to work shouldn’t be the ones staring at articles about isolation levels, locking hints, lock escalation, etc.

Get It, Get It


This is a good problem to get in front of. I’d be quite happy to not have to talk about the reader/writer aspects of locking anymore.

In the same way, it would be nice to not have to talk to users who are endlessly frustrated by locking problems, explain dirty reads, explain optimistic isolation levels, caution them against certain issues, and then tell them to have fun removing all those old lock hints.

Thanks for reading!

The Real Value Of Optimistic Locking

Erroneous


While a lot of people speak highly of optimistic locking because it cuts down on blocking issues, I think it’s of equal or higher value that dirty reads are prevented.

“Imagine” a situation where someone ran a query they shouldn’t have.

Either forgetting a join, a where, or some other limiting condition.

You know, while they’re continuously integrating something.

Out, damn spot


    BEGIN TRAN;
    UPDATE h
    SET h.Score = 2147483647
    FROM dbo.HighQuestionScores AS h
    WHERE 1=1;

This will update every row in the table. I know this because once upon a time, I wrote a similar query and learned the importance of backups.

I also learned that tools that throw a warning when modification queries are missing WHERE clauses think this one is just fine.

But now if I go into another window, this query will return wrong results.

    SELECT TOP 10 *
    FROM dbo.HighQuestionScores AS hqs WITH(NOLOCK)
    ORDER BY hqs.Id DESC;
BIGINT FOR U

In fact, any query that runs with a nolock hint will show incorrect values here.

  • Boss runs a report? Wrong.
  • Trigger fires based on a score change? Wrong.
  • User checks their account? Wrong.

Fully Correct


    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
    
    SELECT TOP 10 *
    FROM dbo.HighQuestionScores AS hqs
    ORDER BY hqs.Id DESC;

This query uses the snapshot isolation level.

It would do the same thing under read committed snapshot isolation, but without needing to set the isolation level.

But now we don’t get someone’s accident in our query results.

Big Baby

Remember folks: yeah, blocking sucks, but incorrect data is usually way, way worse.

Thanks for reading!