Things SQL Server vNext Should Address: Default Isolation Level

Posted on by Erik Darling

You’re Gonna Miss Me

I deal with blocking problems all the time. All the damn time. Deadlocks, too.

Why do I have to deal with these problems? Read Committed is the default isolation level in SQL Server.

It is an utterly broken isolation level, and it shouldn’t be the default anymore.

Can’t Fix It

Any isolation level that lets

Shouldn’t be the default in a major database. No self-respecting database would do that to itself, or to end users.

Imagine you’re some poor developer with no idea what SQL Server is doing, just trying to get your application to work correctly, and your queries end up in asinine blocking chains because of this dimwitted default.

I’d switch to Postgres, too. Adding in NOLOCK hints and maybe getting wrong data is probably the least of their concerns.

Ahzooray

In Azure SQL DB, the default isolation level is Read Committed Snapshot Isolation (RCSI). Optimism for me but not for thee is the message, here.

Imagine a problem so goofy that Microsoft didn’t want to deal with it in its cloud product? You’ve got it right here.

For the next version of SQL Server, the default isolation level for new databases should also be RCSI.

Especially because databases can have a local version store via Accelerated Database Recovery. Why not make the most of it?

And solve the dumbest problem that most databases I see deal with.

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 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.

Related Posts

10 thoughts on “Things SQL Server vNext Should Address: Default Isolation Level”

  1. what about default isolation level for .NET? I am working with a behemoth that started in ’99, and back in the days no one really knew (or cared) about isolation levels. And for better or worse, the default isolation level created by .NET was… SERIALIZABLE. Which overrides anything you set at the SQL Server (database) level.
    You guessed it, no one has the time or patience to go through billions of line of code to fix this issue.

    2. To be clear, the default isolation level when accessing SQL Server from .NET depends on the API / driver used. If you use regular ADO.NET via SqlClient (SqlConnection.BeginTransaction) without specifying an isolation level, the default is “read committed.” If you use TransactionScope, the default is indeed “serializable.” Different versions of Entity Framework have had different defaults (prior to EF6 and EF Core it would use TransactionScope, thus “serializable,” the latest versions use “read committed”).

      You mentioned the project started in ’99, which is before the first version of .NET was released – so perhaps this was on some kind of beta or preview release that had different defaults ?

  2. RCSI on by default definitely would be the better default. You can tell because Microsoft chose it for Azure Sql DB.

    But I think this counts as a breaking change. And Microsoft has been reluctant to introduce any breaking changes. (I believe 2019 introduced *no* new breaking changes). So I think it’s going to be a bit of an uphill battle.

    I know you want this sensible default to target folks who don’t know better. But there is a consolation prize *if* you’re a DBA who installs servers, but doesn’t control devs who create databases willy-nilly. In that case a DBA can change the default setting on those servers with:

    use master;
    alter database model
    set read_committed_snapshot on

    1. Read carefully: I want it to be the default for *new* databases, the way it’s the default for Azure SQL DB. It wouldn’t be a breaking change in that regard, though legacy databases that go to Azure SQL DB certainly experience the transition. Anyway, I don’t think it would be breaking for most applications. I’ve seen very few that rely on blocking for correctness in queues or what have you, and the ones that do can use local locking hints to achieve their goals.

      As for breaking changes in 2019, well… Maybe no intentional ones ?

      1. I read carefully.
        Even only new databases would count I think. I’m thinking of our own processes where we create our databases new from scratch daily and run tests on them. The databases are new, but the project and processes are old.

        I’m imagining a world where new databases had the new default. If we didn’t use RCSI yet and we adopted SQL Server 2021, we’d have to remember to change our processes so that we turned off RCSI. Technically, we adopt a new version but have to adjust our processes to accommodate the new behavior.

        Microsoft’s advice about “For new development work” doesn’t necessarily match “newly created databases”. So I think if they were to make RCSI the new default, I’d expect it to show up in the list of breaking changes.

        But I’m not Microsoft and their definition of breaking changes might be different than mine 🙂

          1. For sure. And we personally could deal no problem.
            But the thing we’re talking about is Microsoft changing a default. And I think the reason they can’t is because of backward compatibility issues. And as Microsoft deals with that, they have to deal with a stricter criteria than covering just most people.

            RCSI = ON should be the default no question. It’s just that I can totally see Microsoft’s point of view. That company (more than any other company I can think of) are restricted to what they can change based on limitations imposed by backwards compatibility.

Comments are closed.