Learn T-SQL With Erik: Row Versioning vs Locking Isolation Levels
Video Summary
In this video, I dive into the fascinating world of isolation levels in SQL Server, specifically focusing on the differences between locking and row versioning isolation levels. I share my experience of briefly testing out a Wario voice at the start, which didn’t quite hit the mark as I was still digesting lunch. However, things are about to get lively as we explore how read-committed snapshot isolation and snapshot isolation can offer more efficient query execution compared to traditional locking methods. I also highlight why Microsoft’s decision to default to read committed snapshot isolation in Azure SQL Database is a step forward, encouraging viewers to consider the benefits of row versioning isolation levels for their own databases.
Full Transcript
Eats-a-me, Erik! That was my best Wario voice. I don’t know, I don’t know, I don’t think I did very well. We’re going to talk a little bit about isolation levels in this video. I just want to briefly describe what the difference between walking and row versioning isolation levels.
If I seem a little sleepy, it’s because I just ate lunch and I’m like, just like a bear full of salmon right now. Anyway, I promise it’ll be very lively in a moment. As soon as that fish oil kicks in, you see what it’s like. My skin starts to glow and my hair gets shiny. All 23 hours of the beginner content is up and running and out there for you to start learning from.
It is all available at the presale price of $250. And all this content is going to double in value after the summer when the advanced material drops out of my head. So I would, again, encourage you to buy early and save yourself some money. All right, let’s talk about isolation levels. In SQL Server, isolation levels can be broadly defined as locking, row versioning, and of course, read uncommitted.
The locking isolation levels are read, repeatable read, and serializable. And the row versioning isolation levels are read committed snapshot isolation and snapshot isolation. And then we have this uncategorized mess, this monster, read uncommitted, which is a synonym for no lock, right?
No lock and read uncommitted, same thing. They do not, there’s no difference except where you write them. Still take schema locks and it will read data from uncommitted transactions, commonly referred to as dirty reads. This is the one that you generally want to avoid, though most of you have done quite a poor job failing grades on avoiding read uncommitted slash no lock.
So for the locking isolation levels, the locking isolation levels work directly with data and tables and indexes by acquiring shared or exclusive locks as data is read. That’s an important thing to understand because like the locks don’t occur like in mass, right? Like I guess you could specify like a tab lock or something and have SQL Server just be like, no, but under most circumstances, you read something you like, as you read data, the locks are required.
You know, even a tab lock isn’t guaranteed to be able to be taken the second you say to take it because you might have competing locks that would prevent it. Read queries under locking isolation levels might be blocked by modification queries while waiting to take their shared locks on rows. They may also block modification queries under certain conditions where shared locks are held until the statement has finished executing.
This is particularly true of serializable and repeatable read, but it is also quite definitely true under read committed. Read queries can indeed block write queries. I will have examples of that later.
Depending on the strictness of the locking isolation level, shared locks may either be held again until the statement is finished or very quickly released. Because like read committed, you’re just kind of like chewing along. You take your read lock, you take your little shared lock, you get whatever data you need, whether it’s rows or pages, and then you let that shared lock go and you carry on.
Both repeatable read and serializable will hold those shared locks to prevent modifications from modifying data that they have already read. So that doesn’t mean that they look, they’re not really like looking forward and trying to like, you know, jump ahead to take locks. But as they read data, then that’s when that all goes down.
The row versioning isolation levels are a little bit different. The row versioning isolation levels will read copies or versions of locked rows, which can, of course, result in fewer detrimental blocking and deadlocking scenarios. I, you know, I, well, I am a big proponent of the row versioning isolation levels, you know, especially all of you SQL Server people out there who are fawning over Postgres, who just can’t wait to leave SQL Server and get over to Postgres land.
Postgres uses multi version concurrency control by default, which is a row version, which for that, which means row versioning, right? Except they do it in the worst way possible and they keep all their versions in the table and you have to clean that up. You have to vacuum that mess up. So you’re going to have a whole new set of problems once you get over to your free database.
But the, the, like, you know, just to understand in SQL Server though, the implementation of the row versioning isolation levels, I believe is far superior. It’s still not for free, right? Because you have to version the rows. You have to read from potentially large stores of version rows, even queries that just have to go and check to see if there are like versions of rows that they might have to go read.
That’s not free adding eight by pointers to your rows so that you can follow the version stuff. That’s not free, right? So like, there’s a lot of stuff that’s still not free about it, even though it’s better, it’s still not free. But I think the non locking isolation levels, the row versioning isolation levels are generally better and less troublesome than your, than your locking alternatives.
And I think that the very easy way to tell that this is true, at least in the context of SQL Server, we’re not, we’re not talking about Postgres anymore, is consider that by default, SQL Server uses read committed locking as its, as its isolation level. This is of course different if you use Azure SQL database, Azure SQL database uses read committed snapshot isolation by default, but you can turn it off. But nearly every single application I see, nearly every query that someone sends me to say, hey, what’s going on with this thing is just absolutely festooned with no lock hints, right?
No lock everywhere. It’s like the, like people have such a hard time with read committed, the locking default isolation level that they’re like, screw it. I’d rather have wrong results than deal with this.
Right. Just no lock hints every year. There’s years of warnings about the perils of no lock. Like, you know, everything that people have, like all the blog ink and blood that has been spilled and drank and spilled again. Uh, you can’t keep people away from it.
It’s like, it’s, it is the, one of the most toxic database relationships that I have ever seen in my life. And, you know, like I said, also like consider that when it came time for Microsoft to offer a SQL Server to the world, like, but they were hosting it, they were managing it. They’re like, we’re in charge.
We can do this. Right. We have separate subject, but, uh, they at least got this part, right. And they were like, no, like, like we’re, we’re hosting that. You know what?
We don’t want to deal with a bunch of stupid reader writer blocking and deadlocking. You know, like, this is so annoying. Like, why would anyone want to deal with this? And like, meanwhile, they’re like, that’s, they’ve been forcing the general public to deal with for the last like 30 something years. Right.
When Microsoft got their hands, they’re like, nah, we got better things to do. So they made the default isolation level read committed snapshot isolation. So a lot of this is just to set the stage for some somewhat simpler statements. And, uh, we’re actually going to close this one out here.
Uh, under row versioning isolation levels, read queries are not blocked by write queries. They go read, uh, versions of the rows, copies of the rows that are locked. Uh, also read queries do not block write queries.
Another good thing, right? It’s, it’s almost, it’s like using no lock, but better because there are no dirty reads. Uh, and also deadlocks do not occur between read queries and write queries. There are of course, some edge cases and caveats to that where, uh, that like complicate those statements a little bit.
Like things like, you know, uh, far and key cascading, uh, maintaining, uh, index views, uh, that have multiple tables and then do weird stuff to the row versioning isolation levels. But those are like kind of out there edge cases. They’re not like everyday stuff that’s going to be happening to you.
Like every three seconds in your database that it’s going to make you use no lock hints. Under the locking isolation levels, read queries can and are quite frequently blocked by write queries. Read queries can also, uh, be blocked by write queries and deadlocks can occur between read queries and write queries.
So there is, uh, there is a lot bad to be said about the, um, about the default situation in SQL Server. I think that head Microsoft made a better choice, uh, back in say 2005 when the, uh, the row versioning isolation levels were introduced to the world. Perhaps not to say that all of your databases are now going to be using a row version isolation level, but perhaps to say that any new databases will by default be using a row versioning isolation level.
But you can turn it off if you want, sort of like with Azure, like Azure SQL database. You, it’s on by default, but you can opt out of it. I think SQL Server would be in a much shinier, happier place than it is today where people still have all sorts of unwarranted fears and misconceptions about row versioning isolation levels.
Perhaps from reading terrible blog posts about them that involve marbles and whatnot. Anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something and I will see you in the next video where we will talk about, uh, I forget, but it’s still transactions and isolation level. So it’ll be a good time because that’s what we do here. We have a good time.
You can’t have a good time. What can you have? Nothing. All right. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.