Learn T-SQL With Erik: Optimistic Isolation Levels Don’t Allow Dirty Reads
Video Summary
In this video, I delve into the nuances of locking versus row versioning and isolation levels in SQL Server, addressing a common misconception that optimistic isolation levels like read committed snapshot isolation allow dirty reads. I explain how no lock hints permit dirty reads while optimistic isolation levels do not, using practical examples to illustrate these concepts. The discussion is aimed at dispelling myths perpetuated by third-party vendors who often rely on outdated or incorrect information, and it serves as a reminder that modern SQL Server features like read committed snapshot isolation offer robust solutions for managing data consistency without the blocking issues associated with traditional locking mechanisms.
Full Transcript
All right, Erik Darling here with Darling Data. In today’s video, we’re going to talk about locking versus row versioning, isolation levels a little bit. Because I find that even to this day, I still need to make a big clarifying point to people. One of the most common things, so I mean, just to frame this up a little bit, I work with a lot of people who use a lot of third party vendor tools. Cool. You know, someone out there for everyone. And what I run into with a lot of these third party vendors is, of course, a lot of no lock hints. And of course, still a lot of blocking and deadlocking problems. And so I’ll make suggestions around using a row version in isolation level. And one of the most common responses that I get when I do this is that someone from the vendor team will, I don’t know, maybe they’ll, maybe they’ll, maybe they’ll, maybe they’ll, maybe they’ll be able to do this. They’ll Google something or maybe, maybe they’ll have just heard something from some point in the distant past. And they’ll say, no, no, no.
We can’t use read committed snapshot isolation. We can’t use snapshot isolation because they allow dirty reads. Meanwhile, there are, there are no lock hints every here, everywhere, which do allow dirty reads. And there is no sign of an optimistic isolation level, which do not allow dirty reads. So at that point, I often just feel like giving up, right? Because how many times can you hear the same, the same person from the same stupid blog post and repeating the same stupid information back to you?
So, yeah. So I end up having to do a lot of work to prove that optimistic isolation levels, in fact, do not allow dirty reads. No lock hints. Do allow dirty reads. And it’s, it’s seemingly always a losing battle. Anyway, all 23 hours of the beginner content is available up on the website. Now, the link is down in the video description. It is at still at the presale price of 250 bucks, and that will double in value to 500 bucks when the advanced material comes out after the summer.
So let us, without further ado, dispel any notions that optimistic isolation levels allow dirty reads. Because apparently this is something that I still have to do in 2025. Thanks, other bloggers. So to recap a little bit, locking read committed is the default isolation level for SQL Server, aside from an Azure SQL database, which uses read committed snapshot isolation by default.
Lucky Microsoft that you get to make that choice. Lucky you. Locking read committed steps things up a bit from read uncommitted and specifically disallows dirty reads. However, this comes with a couple tough trade-offs.
Your selects can block modifications. Your selects can be blocked by modifications. All right, zoom it.
There we go. And your selects can deadlock with modifications. And this is all under read committed. The locking isolation locking read committed. Locking read committed does not give you a point-in-time view of data because the shared locks that it acquires are released as soon as possible.
Under most normal circumstances, we do have to cover a circumstance where those locks are not released and where lock escalation of shared locks to the object level does occur. Now, locking read committed can absolutely, and we will cover this as well, show you deleted rows, missed rows twice, and missed rows entirely as it is reading through data. But that will, again, be something that we cover later, not today.
Row versioning read committed, which is not locking read committed. It is read committed snapshot isolation, is an alternative to locking read committed. It does alleviate the blocking and deadlocking downsides that happen under row versioning, that happen under locking read committed.
So read committed snapshot isolation uses row versions, gets you around locking problems that locking read committed allows for. The row versions are copies of locked rows with the values that are currently committed at the time that the statement began reading rows and not when an explicit transaction began. But these are not dirty reads.
Row versioning read committed can, just like locking read committed, show deleted rows, miss updates to rows, and miss new rows. So locking read committed, you get all that stuff, plus blocking. Row versioning read committed, you get all that stuff, but you have fewer blocking problems.
It’s up to you to figure out which ones are better. But this is only, this part here, this is the important part, this is only from modifications that have not been committed yet. This cannot happen with committed ones, unlike with locking read committed.
So what I’m going to do is I’ve already, I’ve already set snapshot on for stack overflow. This one, I don’t need the whole row back immediate because we’ve already got it turned on. I’m going to create a table called five rows.
And I am going to put five rows into it. The numbers, one, two, three, four, six. And we have no row five in here. Note that it goes right from four to six here.
And then just a get date for the date column that’s in there. Now, over in this tab, I have a select query. So this database has read committed snapshot isolation enabled.
If I add a locking hint to a query, like read committed lock, no lock, whatever, that locking hint will direct reads from that table to obey the locking semantics of the isolation level of the locking hint that I put in there. So for read committed lock, this will still obey the semantics of read committed locking. If I just run this query under, in the stack overflow database, this one is going to read row versions should row versions exist, right?
So if I run this query over and over again, we just keep getting back the same rows. If I come over here and I begin a transaction and I delete from the table five rows where ID equals one. And I come back over to the read committed lock query.
This will just get blocked, right? Okay, fine. If I come over here to the read committed snapshot isolation query, I get back results immediately. But what I get back includes a result from an uncommitted delete, right?
So the delete that I began a transaction for has deleted row one, but it has not committed yet. So because we can see only the committed values, we do not do a dirty read. We see row one, but that’s okay because this delete hasn’t committed yet.
This delete could change its mind. We could also do this and we could begin a transaction and we could add 1,000 years to the date column for IDs two and three. If I come over here and I run this, we will get back results immediately, but we will not see uncommitted results for these rows.
These are still happening in 2025, right? So we see these rows because this still hasn’t committed. So we don’t see 3,025.
We see 2025 still. We can even come over here. Oops, that’s not it. We can even come over here. We can begin a transaction and we can insert a row with ID five into the table, but we have not committed this yet. So when we come over here and we run this, we still see 1, 2, 3, 4, 6.
We do not see data from uncommitted transactions. We have seen potentially out-of-date data, but not really because the transactions have been committed. So this is still perfectly valid data.
So we have just seen a bunch of, right now we just see rows as they existed before any of these modifications started doing stuff. If I come over here and I commit this transaction to show the delete, and then we come over here and we look at this. Now, because that delete committed, we see that row 1 has been removed.
If I come over to this tab and I commit this transaction for the updates, that’ll go just fine. We can come over here. We can run this.
And now we see that rows 2 and 3 have indeed updated to be the year 3,025 from the year 2025. So we have indeed seen committed data. We have not seen uncommitted data yet.
And if I come over here and I commit this transaction, this all goes fine. And I can run this and I see 2, 3, 4, 5, 6, right? Because now I see that row 1 got deleted.
I see the updates to row 2 and 3, and I see the insert of row 5. So this is all fine. We have not seen a dirty read. If I come over here and look at this, well, we see what the end result.
We got 2, 3, 4, 5, 6 with no row 1. But how long did we wait? We waited the entire time I was doing all that other stuff.
So like I said, optimistic isolation levels do not allow dirty reads. We did not see one dirty read that entire time. We saw the correct versions of rows, versions of those rows from before the modifications, like, fully committed.
They were in flight. We read a row version. We didn’t commit them yet.
So those changes weren’t, like, fully permanent to the database. We did not commit them. They were not quite consistent in a consistent state in the database because they weren’t committed. So we did not see those changes reflected yet.
As soon as those transactions committed, we saw the changes reflected in our data. But we didn’t get blocked once, right? Only with the read committed lock, we waited, well, I mean, in this case, about two and a half minutes for me to do all the other stuff.
And then we got back this. So it’s up to you how you want your data to look. You can either see, uncommit, you can see the, like, you can either see the changes, right, or not see the changes until they commit, which is row versioning.
Or you can wait a long time and then see the changes after they commit when you use the locking isolation level. How would you, I don’t know about you. I’m an impatient person.
I would much rather get back my results quickly and then see committed data once it fully commits. But not once did this give us a dirty read. We did not once see data from an uncommitted transaction.
We only saw data once those transactions fully committed. Anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I will see you over in the next video. We have a few more things to talk about when it comes to actually read committed specifically being one of my least favorite isolation levels. So we’re going to do those next.
But anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.