Learn T-SQL With Erik: Writing Bad Data With NOLOCK Hints
Video Summary
In this video, I delve into the hidden dangers of using the `NOLOCK` hint and the `READ UNCOMMITTED` isolation level in SQL Server. I explore how these settings can lead to unexpected issues, such as dirty reads where your queries might return inconsistent or erroneous data, potentially causing confusion for end users. I walk through a practical example demonstrating how using `NOLOCK` can rewrite uncommitted data into your database, leading to potential data corruption and making it difficult to maintain the integrity of your data. This video is part of my T-SQL course, which now offers 23 hours of beginner content at a pre-sale price of $250, with the advanced material set to release after summer and double in value to $500. If you’re serious about mastering SQL Server, this course is an excellent investment for your skills.
Full Transcript
Hey, Erik Darling here with Darling Data, and in today’s video we are going to talk about sort of an unforeseen peril of the read uncommitted isolation level. Whenever people talk about it, they say, oh, your select queries will do dirty reads. True, they will. Assuming that dirty reads exist, they can and will be done. And you will, your end users will be like, what the hell am I looking at here? I think, I think the database is corrupt. I don’t know what these results are. There are 50 rows of the same thing for some reason. So like, like, you know, not a good idea to use, right? If you, if you truly don’t care about, you know, your end users or what data they see or what reports they get, cool. Like you can, as long as, as long as that is a consenting relationship between you and your end users where they know that they are going to pay you money and you are not going to care, cool. Stick with it. You’re doing great. Otherwise, you should, you should probably think about something else, right? Anyway, this is of course all teaser content from my T-SQL course. 20, all 23 hours of the beginner content is out and available. Still at the pre-sale price of 250 bucks and doubling in value to 500 bucks when the advanced material publishes after the summer. But now let’s go talk about the, the, the nastiness of NOLOC. Now, first, read committed and NOLOC are absolutely synonyms for each other. They are twins. They do the same thing. Read uncommitted is of course the weakest of all the isolation levels with absolutely no protective shared locks taken on data that is being read. Read uncommitted is an isolation level that is defined in the SQL standard and available in SQL Server. And NOLOC is a table hint that indicates reads should obey the read committed locking semantics. You do not need both. You, I mean, really shouldn’t use one or the other, but you know, here we are. Uh, and if SQL Server developers were thinking clearly when they were naming things, they would have named the hint, no respect instead of NOLOC to clearly indicate that it means that your query will not respect locks taken for by other queries. Not that your query takes absolutely no locks.
Because when they name it NOLOC, it makes it sound like your query is just like this fancy lock free lad just running around town, but it’s not. Um, it, you know, it does like, it does make it so that it won’t take shared locks, like to read stuff on data. That’s, that is true. But there are still schema stability locks that get taken so that data can’t change part way through, uh, meaning like, like table structure data rather. Uh, so like, you know, if you were saying select star from table with NOLOC, then someone wanted to like add a column or drop a column or change a column from an integer to a, in VARCAR 4000 and like start typing words in there. Your query, your query, your query, your query that’s currently running wouldn’t be like, wait a minute. Now I gotta get rid of this column or add this column into the results or wait a minute. I’ve been showing the numbers one through a million. And now I have to start like showing letters and stuff. This is weird. So like, like there are locks that get taken to prevent some things from happening.
But, uh, not any of the, uh, like changes to data, uh, that you might like, like to on, on rows and pages that you might, uh, might, might see in other situations. So, uh, like when you use read committed or repeatable read or serializable, those shared locks do get taken and your read queries can both get blocked by modification queries, deadlock with modification queries, and even block modification queries. Um, generally it’s not advisable to use NOLOC hints. Even when people make what they consider to be an informed choice about allowing it, the full consequences of what you’re doing when you use it are rarely understood, at least until incorrect data ends up being written into your database, uh, and also returned to users.
Um, you know, of course your queries won’t generally block or be blocked by concurrent modification queries, but it comes at a very steep cost. Um, it’s the only isolation level that allows dirty reads. Something that a lot of people sort of mess up is thinking that there are optimistic isolation levels that allow for dirty reads, but there, there aren’t, but that’s not true.
And we’re going to talk about that in the very next video. So, uh, what we need, my mouse will actually scroll here are a couple of tables. Uh, we’re going to have one table called no and one table called lock, right?
Uh, aptly named, I think, because we’re talking about NOLOC. In the table called no, we’re going to insert a single row. This is turning into a mother goose thing very quickly, right?
So we have one row with the ID equals one and, uh, the column no set to NULL. So that’s all that’s going in there. In the column lock, we are going to insert one row as well.
Uh, it’s also going to have an ID of one because we’re going to join these things together. And right now the lock column is going to be inserted with a value of 100. So if we put that in there, now we have a table called no and a table called lock.
What I’m going to do in here is open a transaction and I’m going to update the column lock in the table called lock to be the integer maximum, right? So two, one, four, seven, four, eight, three, six, four, seven. Sure.
That’s someone’s phone number. I apologize if it is. And we’re going to run this. And the first and most obvious thing is that if we, you know, run this query, we can see that we read this row, right? We can see this, this change to the data in flight.
When we inserted a row into this table, it had the value of 100. It has now been updated to this number. So if we say, you know, to select from that table with no lock, or we set the transaction isolation level to read uncommitted, we can see that data change in flight. Okay.
So this is just a read query. If I come over here and I run this update query and I, like, we can’t put a no lock hint on this table because this is the table that we’re updating, right? This end here means we’re updating this here, right?
So the table no is aliased as n and that means that’s what we’re updating here. And we’re setting the column no equal to the column lock from the lock table. And we are, of course, joined on those ID columns that were helpfully populated with the number one before.
So if I do this, but I say it’s okay to read dirty data from the lock table. If I say it’s okay to read from this table using the no lock hint, the same thing is going to happen here that happened over here, where we see this number from this in-flight transaction, right? We see one row got affected here where we set lock equal to this number.
So if I run this, and what I’m going to do is I’m going to use the output clause, and I’m going to use the inserted table to show you what values ended up in this table, right? So this inserted shows you the updated values that anything from the deleted would show you the values that got overwritten. If I run this, and we look at the results, we’re going to see that we actually end up updating a row in this table to the value that it sees from the in-flight transaction in the other tab.
This is really not a good thing. You have now rewritten uncommitted data to your database. Right?
So if this query over here happens to change its mind and roll back, or maybe someone says, hey, dummy, you’re not supposed to give anyone that much of a thing, right? 2.14 billion. You’re not supposed to do that.
Like, that’s clearly erroneous. Then all of a sudden, this table will still have bad data in it. So now we’ve written uncommitted data to the table no, and we would now have to find some way to undo this. If all your queries are doing this, and all of your modification queries are saying, it’s okay to read from these other tables with no lock.
Who knows how much uncommitted stuff, rolled back stuff, erroneous stuff, incorrect stuff has ended up being written to other parts of your database. This is why when I say, you know, even if you have made a rather, like you think you’ve made a rather informed choice about using no lock hints in other places, as people start to see the commonality of those no lock hints, they are going to start spreading to other places. And you’re going to start writing garbage data, not only, not only are you going to start returning garbage data to your end users, but you’re going to start writing garbage data to your database.
And now your database is full of garbage. 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’re going to talk about locking versus row versioning and how row versioning isolation levels don’t actually allow dirty reads. 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.