Isolation Levels – Page 2 – Erik Darling Data

Locking Hints Make Troubleshooting Blocking And Deadlocks Confusing In SQL Server

Posted on December 22, 2020May 16, 2022 by Erik Darling

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.

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

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;

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!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Why Read Queries Deadlock With Write Queries In SQL Server

Posted on November 17, 2020September 20, 2022 by Erik Darling

You Come And Go

I go back and forth when it comes to Lookups.

On the one hand, I don’t think the optimizer uses them enough. There are times when hinting a nonclustered index, or re-writing a query to get it to use a nonclustered index can really help performance.

On the other hand, they can really exacerbate parameter sniffing problems, and can even lead to read queries blocking write queries. And quite often, they lead to people creating very wide indexes to make sure particular queries are covered.

It’s quite a tedious dilemma, and in the case of blocking and, as we’ll see, deadlocks, one that can be avoided with an optimistic isolation level Read Committed Snapshot Isolation, or Snapshot Isolation.

Bigger Deal

There are ways to repro this sort of deadlock that rely mostly on luck, but the brute force approach is easiest.

First, create an index that will only partially help out some of our queries:

CREATE INDEX dethklok ON dbo.Votes(VoteTypeId);

Next, get a couple queries that should be able to co-exist ready to run in a loop.

A select:

/* Selecter */
SET NOCOUNT ON;
DECLARE @i INT, @PostId INT; 
SET @i = 0;
WHILE @i < 10000
BEGIN
    SELECT 
        @PostId = v.PostId,
        @i += 1
    FROM   dbo.Votes AS v
    WHERE v.VoteTypeId = 8;
END;

An update:

/* Updater */
SET NOCOUNT ON;
DECLARE @i INT = 0;   
WHILE @i < 10000
BEGIN
    UPDATE v
        SET v.VoteTypeId = 8 - v.VoteTypeId,
        @i += 1
    FROM dbo.Votes AS v
    WHERE v.Id = 55537618;
END;

After several seconds, the select query will hit a deadlock.

But Why?

The reason, of course, if that these two queries compete for the same indexes:

The update query needs to update both indexes on the table, the read query needs to read from both indexes on the table, and they end up blocking each other:

We could fix this by expanding the index to also have PostId in it:

CREATE INDEX dethklok ON dbo.Votes(VoteTypeId, PostId);

Using an optimistic isolation level:

ALTER DATABASE StackOverflow2013 
    SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Or rewriting the select query to use a hash or merge join:

/* Selecter */
SET NOCOUNT ON;
DECLARE @i INT, @PostId INT; 
SET @i = 0;
WHILE @i < 10000
BEGIN

SELECT @PostId = v2.PostId,
       @i += 1
FROM dbo.Votes AS v
INNER /*MERGE OR HASH*/ JOIN dbo.Votes AS v2
    ON v.Id = v2.Id
WHERE v.VoteTypeId = 8

END;

Thanks for reading!

Going Further

Help! My SQL Server Query Got Slower Without A NOLOCK Hint!

Posted on February 28, 2020May 14, 2022 by Erik Darling

(NOSERIOUSLY)

Going Further

In SQL Server, Isolation Levels Don’t Control Locking

Posted on October 22, 2019May 16, 2022 by Erik Darling

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!

Going Further

When Read Queries Block Write Queries In SQL Server

Posted on October 4, 2019May 16, 2022 by Erik Darling

Gritty

You may occasionally see in your favorite monitoring tool, or Most Fantastic And Glorious Script For Determining Whom Activity Belongs To™ that a read query, perhaps even a long running one, is blocking modification queries.

Under most circumstances, this won’t happen. Most.

Shared locks taken by read queries will let go pretty quickly.

Under most circumstances.

Out, Liars

To hold onto Shared locks, you’d need to use an isolation level escalation hint, like REPEATABLE READ.

I could do that here if I were a lazy cheater.

Instead, I’m going to show you a more common and interesting scenario.

You see, like a lot of important specks of knowledge, this one comes from Craig Freedman:

Note the “WITH UNORDERED PREFETCH” keywords on the nested loops join.

I am not going to demontrate it , but when SQL Server executes this query, it holds S locks on the rows returned by the index seek until the query finishes executing.

And to generalize a bit from the source of the rest of the important knowledge in the world: These types of key lookups are common in plans with Star Join optimizations.

On To The Text

I’m going to be a little bit of a lazy cheater here, and rather than show you where this can happen with parameter sniffing or some other weird optimizer choice, I’m going to use an index hint to use this index:

CREATE INDEX whatever ON dbo.Votes(CreationDate, VoteTypeId);

Then I’m going to run this query, which’ll take about 10 seconds:

DECLARE @i INT
SELECT   @i = v.PostId
FROM     dbo.Votes AS v WITH (INDEX = whatever)
WHERE    v.CreationDate >= '20080101'
AND      v.VoteTypeId > 5
GROUP BY v.PostId
ORDER BY v.PostId;

Here’s what the query plan looks like:

And when we get the properties of the Nested Loops Join, we’ll see the Unordered Prefetch property set to true.

If I kick that query off and look at the results of sp_WhoIsActive @get_locks = 1, I’ll see this:

    <Object name="Votes" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="S" request_status="GRANT" request_count="4" />
      </Locks>

Which is exactly what I want — a Shared object lock on Votes that has been GRANTed. That’ll get held onto for the duration of the query.

Now when I try to run this update, it’ll get blocked:

BEGIN TRAN
UPDATE dbo.Votes SET UserId = 2147483647 
ROLLBACK

Note that I’m only wrapping it in a transaction here so it’ll roll back. It will still get blocked without that, but then I’d have to reverse the update on my own.

See, everyone’s kind of a lazy cheater.

The locks that the update wants look like this:

    <Object name="Votes" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="WAIT" request_count="1" />
      </Locks>

We see the IX lock has a request status of WAIT.

And Who Is Active is showing us that the read query has been blocking the write query for around 4 seconds.

Capper

If you’re out there in the world and you see a read query that blocked a write query, take a close look at the query plan for a Nested Loops Join with the Unordered Prefetch property set to true.

I bet you’ll find one. And I’ll bet your query wasn’t fast.

Fixing the Key Lookup may not make your query faster, but it should alleviate the blocking because of long-held shared locks.

Thanks for reading!

Going Further

SQL Server Index Key Column Order And Locking

Posted on May 30, 2019May 14, 2022 by Erik Darling

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;

SQL Server sp_WhoIsActive Locks — 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);

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!

Going Further

Should An Optimistic Isolation Level Be The New Default For SQL Server Databases?

Posted on May 15, 2019May 16, 2022 by Erik Darling

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!

Going Further

The Real Value Of Optimistic Isolation Levels In SQL Server

Posted on May 14, 2019May 16, 2022 by Erik Darling

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;

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.

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

Thanks for reading!

Going Further

Isolation Levels Are Inherited In SQL Server Queries

Posted on April 29, 2019May 16, 2022 by Erik Darling

Client Yell

While working with a client who has a nasty blocking problem involving triggers, we were talking about using Snapshot Isolation to help fix the issue.

The thing is, there’s more than just stored procedures causing problems. Some of the issue is with triggers.

The prospect of having to modify a bunch of objects was a rightfully frightful one.

Funboy & Funboy & Funboy

In my database, I have Snapshot Isolation enabled.

I have a table with a trigger on it. The trigger doesn’t do anything except show me what my session’s isolation level is

CREATE TABLE dbo.t ( id INT NULL, dimdate DATETIME NULL );

CREATE TRIGGER dbo.dim_trigger
ON dbo.t
AFTER INSERT
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @UserOptions TABLE ( SetOption VARCHAR(100), Value VARCHAR(100));
        DECLARE @IsolationLevel VARCHAR(100);

        INSERT @UserOptions
        EXEC ( 'DBCC USEROPTIONS WITH NO_INFOMSGS' );

        SELECT @IsolationLevel = 'Trigger:' + Value
        FROM   @UserOptions
        WHERE  SetOption = 'isolation level';

        PRINT @IsolationLevel;

    END;

I also have a stored procedure that checks the isolation level, and inserts into my table so that the trigger fires off.

CREATE OR ALTER PROCEDURE dbo.insert_t
AS
    BEGIN
        SET NOCOUNT ON;

        SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

        DECLARE @UserOptions TABLE ( SetOption VARCHAR(100), Value VARCHAR(100));
        DECLARE @IsolationLevel VARCHAR(100);

        INSERT @UserOptions
        EXEC ( 'DBCC USEROPTIONS WITH NO_INFOMSGS' );

        SELECT @IsolationLevel = 'Procedure:' + Value
        FROM   @UserOptions
        WHERE  SetOption = 'isolation level';

        PRINT @IsolationLevel;

        INSERT dbo.t ( id, dimdate )
        VALUES ( 0, GETDATE());

    END;

On Notice

I’m only setting the isolation level in the stored procedure here.

When I run it, both will return the same isolation level no matter what you set it to.

Thanks to Scott Ivey, whose code I borrowed for this.

And thank you for reading!