create temp table sql Archives | Page 18 of 139

Learn T-SQL With Erik: Read Committed, Not So Committed

Posted on September 12, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Read Committed, Not So Committed

Video Summary

In this video, I delve into a scenario where a query running under the read committed locking isolation level in SQL Server appears to return impossible results, challenging common misconceptions about read committed. I explain that read committed only ensures that your query reads data as it was at the time of taking shared locks, not that other queries cannot modify this data before or after your query runs. Through a detailed example involving a query to find top-scoring posts and their answers on Stack Overflow, I demonstrate how concurrent updates can cause unexpected results due to the timing of lock acquisitions during execution. This video is part of my ongoing effort to clarify complex SQL Server concepts for beginners and experienced users alike.

Full Transcript

Erik Darling here with Darling Data. You’re welcome. In this video, we’re going to continue. We’re going to look at a situation where a query running under the read committed locking isolation level appears to return impossible results to us, the end user. And we’re going to do this because some people still have very, very strange misgivings and misunderstandings about read committed locking isolation. And what exactly read committed means. When you get down to it, read committed only means that your query at the time that it attempted to take the shared locks that it requires in order to read rows and pages and whatnot, the data at the time that it was read was what was committed in the database. That does not mean that other queries cannot interact with that data and change that data before and after your query has been submitted. And that’s the data has released those shared locks. All 23 hours of the beginner content is available to you. Currently, there’s a link down in the video description for you to buy it. The presale price is still 250 bucks with the value doubling, skyrocketing to $500 after the summer months when my brain has officially stopped sweating and we can start thinking again. So anyway, let’s take a look at this query. Now, the goal here is to find the top score of the query.

So we’re going to find the top scoring post that is a post type ID to his questions. And what we want to find are all the other answers. We want to find all the answers for the question that has the top scoring answer to it. So we’re selecting star from the post table. We’re going to have an index hint in here just for demo stability. And we’re saying where the parent ID of whatever we find, right, or the parent ID of the things that we want to find equals the parent ID for the highest scoring answer to a question. So all the other answers to the question essentially. Now, because we have a sub query in this query and this, right, we have the post table referenced once here and once here.

This really means that the post table is going to be read from twice. Under locking read committed, that means that there are, I mean, many opportunities for reads to happen at all different points in time because the shared read locks are going to happen for one reference to the post table, right? And we’re going to take those and quickly release them. And then we are going to join one reference to the post table to another reference to the post table on the parent ID column. And that’s going to be another opportunity for shared read locks to slowly be taken and released as they get the data that they need.

So let’s look at the execution plan for this thing. I’ve already run it ahead of time. The first thing I actually want to do though, is show you over here that when we run this unfettered without anything happening around it, without anything else interacting with the data, all the results that get returned over here have a post type ID of two.

Okay. Everything, everything next to me is a two. This whole column is all twos because that’s what we were looking for here. So if you, your, if your post has a parent ID, that means it was an answer to a question. If your post does not have a parent ID, that means it was the question, right?

Questions belong to answer. If your post, let’s try that again. If your post has a parent ID, that means it was an answer. So it was like, it’s like, it’s like the parent ID is the question ID that ties your answer to the question.

There we go. So the execution plan for this features two things. Well, I mean, I guess we have three opportunities in here to read strange data from the post table, don’t we? We have a clustered index scan here and this takes about five seconds.

So a single thread scans the clustered index and gets all the rows. The next thing we do is sort all of those rows. After we sort all of those rows, which we get, we have one of, there’s one row that comes out of this because we find the top one scoring answer to a question here.

Right. So we do that. We scan things, we find the top scoring thing, and then we join what comes out of this to a nonclustered index on the post table. And we find the nine, in this case, there are nine other answers to the question that we found with the top score here. So we go find the nine rows here that we care about.

And then over here, we touch the post table a third time because we need to do a lookup in order to get the other columns that we need to satisfy the presentation section of the query. So we have this whole output list here, right? This is all things that we’re going to do. We’re going back to the clustered index to get these columns after we’ve done a seek to find the parent IDs that we care about here.

So there are really three opportunities to read data from the post table. And they all happen at slightly different times. Like because there’s a sort, all the 11 million rows that we need to find in the post table, right?

We have to get all those rows to the sort operator. Then the sort can happen. Then we do a nested loops join to the lookup.

And then we have another sort here, which means all the rows have to show up here. And then we can start doing a lookup here. So really, there’s like one, two, three different points in time that we’re reading data from anyway. And during these three different points in time, all sorts of other things can interact with the data underneath you.

So let’s do this. Let’s go over what we’re going to do in the other windows. Let me scroll down here first.

So this is the one that we’re going to run when we start doing things. I’m just going to tuck this down here so it’s a little tidier on the screen in case I need to zoom in again. Over here, what we’re going to do is we are going to, this is the last row in the post table.

This is the highest post ID. And what we’re going to do is start a transaction and update the post table. And all we’re doing is adding one millisecond to the last activity date for this last row in the post table.

Right? There are no rows after this. This is the highest assigned ID. The ID is the clustered primary key.

So this is the end of the table. And then what we’re going to do is run this. So let’s pretend that this is all happening very quickly. Like in the database, it’s not me running things in separate tabs. This is a database with a fair amount of concurrency.

Right? And so we have things running at all different times. And those things need to be able to run and do things. And we don’t want to like have like a train schedule for all these queries where it’s like, you go. All right.

You go. Update. Yep. You go now. Right? Like that’s not happening. These queries are all just getting sent in and run and trying to do their thing while other queries are getting sent in and trying to do their thing. So let’s begin this transaction and run this update.

And now let’s run this query. Now, this query took about five seconds before. This query will not take five seconds this time.

Right now, this query is blocked by that update. Right? Because like one of the things that we have to do in this query plan is scan. We have to scan.

We have to touch the clustered index twice. We have like the first thing that we did was scan through the clustered index and then sort all that data. Right now, this query has read everything in the clustered index except that last row because that last row has a lock on it. Right?

So everything got read except that last row. So this select query running under recommitted locking is blocked from taking a shared lock on that last row by the update in the other window. So while this update is holding this lock open, let’s come over here.

And what we’re going to do is we are going to update a very coincidental nine rows, nine or ten anyway. And we’re going to set their post type ID to one. Coincidentally, these are the other rows that we need to, or rather the other rows that get returned by our query is the other answers to the question that have, that belong, they’ll belong to the question with the highest scoring answer and all of stack overflow.

This query runs successfully. Now, if we come over here and we roll this back, we don’t have to commit this. We can just say, oops, I didn’t mean to add it.

I didn’t mean to add that millisecond there. We can roll this back. And as soon as we roll this back, we get results immediately. If we look at the execution plan, well, things look a little bit different. This scan of the cluster index now took one minute and 28 seconds.

It took a minute and 28 seconds because it was like the five seconds that it normally ran for, plus the like 25 or so seconds that I was trying to explain things to you. What’s neat now is if we go to the properties of the select and we go to the weight stats, the top weight that we’re going to see is LCKMS. Right.

So we can see that this query waited a long time on a lock. That lock, that shared lock, LCKMS, it’s trying to take a shared lock, was blocked by the update query that started a transaction and said, go outside and smoke a cigarette. I don’t know.

Maybe grab a beer for lunch. I don’t know. We’ll see what happens. There’s a halal cart. I don’t know. Get crazy out there. But what’s very interesting now is if we look at the results of this query, even though we specifically requested that only things with a matching parent ID, right?

So only, only, again, only answers have a parent ID. That’s the questions don’t have a parent ID, right? And post type ID is answers and post type, post type ID two is answers and post type ID one is questions. Even though this is what we asked for.

And even though the first time we ran this query, we got all post type ID two back. If we look over here now, magically, mystically, all of these post type IDs were one, which seems impossible. Because a post type ID of one, it cannot happen for an answer.

Post type ID two is answer. We looked for other answers and we found them. They all have the same parent ID too. We were not, oh boy, oh boy, oh boy.

Questions do not have a parent ID. Questions are the parent. So something terrible has happened here. And this sort of stuff can happen all the time under any sort of application concurrency using locking read committed. So be careful out there.

Before you go and you start thinking that read committed locking is some sort of snapshot point in time, amazing, consistent, confident. Doesn’t it sound confident, right? Read committed.

What do you read? I read committed data. I don’t read uncommitted data. I’m committed data only. It sounds like an LLM level of confidence from read committed, but we do not get that. Read committed has very weak guarantees.

Those guarantees do not coincide with the start of a statement or the start of a transaction. They are just a mishmash of various points in time when your query was allowed to take shared locks. So again, be careful out there.

Your queries might be returning weird stuff all the time. This isn’t even NOLOC, which is the surprising thing. Like you would expect this sort of thing using NOLOC or read uncommitted, but not read committed.

It’s committed to reading committed data. Well, maybe not so much. 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. 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.

Learn T-SQL With Erik: Reads Deadlocking Writes Under Read Committed

Posted on September 11, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Reads Deadlocking Writes Under Read Committed

Video Summary

In this video, I delve into the intricacies of SQL Server isolation levels, focusing primarily on the notorious “read committed” level and its potential pitfalls. Specifically, we explore a scenario where a read query deadlocks with a write query due to their interaction with indexes. By walking through these examples, I aim to provide you with a deeper understanding of how read committed can lead to unexpected issues like deadlocks, especially when multiple nonclustered indexes are involved. I also discuss potential solutions and highlight the benefits of using optimistic isolation levels such as Read Committed Snapshot Isolation (RCSI) or snapshot isolation, which offer more robust protection against these types of problems without requiring extensive query or index modifications.

Full Transcript

Erik Darling here with Darling Data, continuing on to talk about isolation levels, mainly focusing on what a turd of an isolation level read committed is. And in this video, we’ll be focused in on seeing a read query deadlock with a write query. There will be very similar circumstances to the previous demo, just with a little bit more involved because it is a deadlock, and not just blocking. Again, if you’ve been watching my videos and you know, seeing stuff that I do here, you may have seen this demo before, but you know, we’re gonna go into a bit of detail on it. So if you’ve perhaps forgotten or never knew in the first place, or perhaps you knew about this, didn’t you? It may be good for a refresher. All the same stuff over here, 23 hours of the beginner content is out there and available for you. The course is still at the presale price of $200,000. And that price will be going up to $250,000. And that price will be going up to $500,000 after the summer, when the advanced material is complete. So with that out of the way, let’s look at this situation. Now, what we have going on here is, well, we have a select query. And this select query is going to do a couple neat things.

It’s going to start a while loop. And while the I local variable is less than 10, 100,000, we are going to assign the post ID to this one. And we are also going to thank you, zoom it. We are also going to increment the I parameter in here, we have an index hint for a little bit of demo stability here, no big no nothing, nothing too interesting. And then over in this window here, we have an update query. And the update query is going to do something rather similar, except what it is going to do is basically flip this value for vote type ID back and forth between eight and zero. So we’re saying vote type ID equals set vote type ID equals eight minus vote type ID. So this will either be eight or zero because the vote type ID for this is currently eight. Let’s see, let’s see, let’s see, do we get an estimated plan for this one we do. So if you remember the last video we did on read queries blocking right queries, it’s going to be a sort of similar situation here.

We have an index seek that finds the rows that we care about. And since SQL Server estimates that that more than 25 rows are going to leave this index seek, SQL Server chooses to use the unordered prefetch optimization here. And it does not in this time around opt to choose to use an optimized nested loops, just the unordered prefetch portion. But same deal as before, because we’re using the unordered prefetch every time a row comes out of here and goes into the loop, we come down here to go get a column out of the clustered index.

And for SQL Server will hold on to locks the entire time that we are doing the lookup. The lookup in this case is just to output the column post ID. Oh, I guess that’s the same thing as before. And again, because the prefetch optimization is in play, the locks on the clustered index will be held until this statement completes.

Now, over in the update window, this is where things get a little bit different. Now, if you recall the last one, the last demo on just the blocking, it was only the clustered index that was getting updated. This time, in order for there to be a deadlock, we also need to have a second nonclustered index get updated.

This is what causes the sort of deadlock embrace scenario rather than just a plain blocking scenario. Because now we have two queries that one is holding the object level shared lock. One is trying to update both the clustered index on the table and the nonclustered index on the table.

So we have this sort of circular dependency going on, which causes the deadlock this time around. So if we look at this, you’ll see that we are updating the primary key on the votes table. And we are also updating this other, I mean, we’re updating actually two other nonclustered indexes here.

But we only need one of these nonclustered indexes in order for this to happen. So what I’m going to do is I’m going to start running this and start running this. And then I’m going to hit SP who is active.

And we missed it that time around. There we go. We got it.

Sweet. So here is what this looks like. Now let’s kill this one off. That got a little jumbled, didn’t it? These SSMS tabs get all weird on me when one of them starts, one of them, you hit one and it starts saying executing. And then all of a sudden that tab gets bigger and switching tabs around gets somehow confusing.

But very professional. So here we have our update query right here. And it is trying to take an intent exclusive lock here.

And here we have our select query. And it is attempting to take that object level shared lock here. And they are blocking each other.

You’ll note that session ID 68 is here. And session ID 61 is here. And if we move the blocking session stuff over a little bit closer so we can frame this all up at once, you’ll see that session 61 is blocked by 68.

That’s right there. And session 68 is blocked by 61. Now, of course, the choice of deadlock here is very easy.

SQL Server will always choose the select query as the deadlock victim. That’s what the error message that we get here is. And it does that because the select query is very easy to roll back.

The select query hasn’t done any writes. There’s no log. There’s no version store. There’s no accelerated database recovery persistent version store, rather. There’s no work to do to kill this query off, aside from to say, pow, you’re dead.

But, like, this query over here just runs and does the update until I cancel it. So, like, the update never loses the deadlock battle. It’s only ever the select that loses it.

But we can also take a look at the locks that want to happen here. And you can see for the… This was for the update, right?

Yeah, the update. The update query was granted a whole bunch of locks, right? Intent exclusive. It was granted an X lock here. It was granted an X lock here.

But then there was a couple down here that… No, sorry. There’s just one over here that it is waiting on, right? And it is waiting on this intent exclusive lock on the vote type ID index. And then for the locks for the select query, this one has been granted a few different types of locks, right?

Like, all these shared and intent shared locks have happened there. But then this one shared lock on the clustered primary key, right, where the lookup is happening to, this one is being forced to wait.

So this one is waiting because of the modification query. And this one is waiting because of the select query. And neither one can make progress, right? So they are just stuck waiting for each other.

And that’s why the deadlock occurs. So again, sort of the same situation here. We could, of course, extend the non-cluster… Sorry, we could, in this case, we could extend the nonclustered index so that there is no lookup in the select query.

That would be one way of taking care of this. Another way of taking care of this would be to, of course, use the no-lock hint, which I am, you know, pretty avidly against doing.

But, you know, like I said in the last video, it’s really not no-lock or read uncommitted that’s the problem. It’s the fact that you have to deal with all this crap under read committed that’s really the problem. Read committed is the enemy.

Right. And so a better solution would be to use an optimistic row versioning isolation level, like either read committed snapshot isolation or snapshot isolation, in order to prevent this sort of deadlocking at a grander scale.

The same way you could use that to prevent the sort of blocking we saw in the last video on a grander scale, without having to go through each and every one of your block, like reader, writer, blocking and deadlocking things and saying, okay, well, can I, like, what’s the query plan?

Can I fix the query plan? Can I do this? Can I do that? Can I do the other thing? Can I change indexes? What can I do? The optimistic isolation level thing just fixes all of it in one big go. So you don’t have to go through and nitpick every single individual query.

If you just have a couple queries that this is happening for, then you can probably pretty easily fix this without going and changing any database settings, assuming you’re allowed to change indexes.

But if you have this happening across a wide variety of queries, your life gets very, very difficult when it comes time to fixing these things, because you have to go through a lot of different stuff and fix a lot of different things. And then, you know, you’re maybe not even 100% guaranteed to fix everything.

Not every type of deadlock and not every deadlock situation is entirely unavoidable. You might need to also, you know, introduce, like, you know, lock timeouts, or you might need to introduce deadlock priority, or you might need to introduce some sort of retry mechanism for your queries that end up in deadlock situations.

So there’s a lot more that goes on. Like, you can make your best effort at fixing all this stuff with the things that I’ve talked about, making changes to the queries, indexes, etc.

But at the end of the day, not every blocking and deadlocking situation is entirely avoidable, unless you get to an optimistic isolation level, which resolves things in a far, like, far more, like, far more specific way, let’s say.

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 continue to pick on Read Committed, because too many people think that it is too good of an isolation level.

And, uh-uh, not on my watch, pal. All right. Thank you for watching.

Going Further

Learn T-SQL With Erik: Reads Blocking Writes Under Read Committed

Posted on September 10, 2025July 11, 2025 by Erik Darling

Learn T-SQL With Erik: Reads Blocking Writes Under Read Committed

Going Further

Learn T-SQL With Erik: What Does Read Committed Guarantee?

Posted on September 9, 2025July 11, 2025 by Erik Darling

Learn T-SQL With Erik: What Does Read Committed Guarantee?

Going Further

SQL Server Performance Office Hours Episode 28

Posted on September 8, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 28

To ask your questions, head over here.

How often have you dealt with corruption? When is the last time you saw it?

Have you ever met a query you couldn’t tune?

Which database will win the database wars?

What are the hardest kinds of queries to write?

When will Joe Obbish be back for a podcast?

Video Summary

In this video, I dive into answering five of your most pressing questions about SQL Server and beyond during another exciting Office Hours episode. I cover a range of topics from dealing with corruption to the future of database management systems like DuckDB and Postgres. Whether you’re curious about tuning queries or wondering which database might win in an imaginary “database wars,” this video has got you covered. Plus, I share some upcoming tour dates for my consulting services, including stops in New York City, Dallas, Utrecht, and the eagerly anticipated Past Data Community Summit in Seattle. If you have any questions of your own or want to support the channel, there are links in the description to get involved.

Full Transcript

Erik Darling here with Darling Data, and we are in the midst of another wonderful Office Hours episode coming from me to you, in which I answer five of your most pertinent, important, burning questions about SQL Server, life, love, the world around you, mysteries of the universe, anything that you’re interested in. If you would like to ask your own questions for Office Hours, there is a link right there to do that. If you would like to support the the channel, there is a link, well actually both of these links are down in the video description. If you’re feeling very kind, and you want to ask me a question, or you want to sign up for a membership, you can do either one. If you need help with your SQL Server, if performance is just not what you always dreamed it would be, I am available for consulting as a consultant to help you with those problems. Health Checks, Performance Analysis, Hands-On Tuning, Dealing with Performance Emergencies, and of course, Developer Training. And as always, my rates are reasonable. Performance Tuning Training, about 150 US bucks, about 75% off there. That link down in the video description, and you will get all of that content for life. Speaking of for life, my T-SQL course is, well, all the beginner content is done, which means it is somewhere near half done. The advanced material, of course, will be bigger than the beginner material, because there is much more, to say in the advanced material, but 23 hours of beginner content is out there now. It is 250 bucks at the pre-sale price, and that price will be going up, doubling the value of the course after the summer months. The summer months, of course, will be busy times for your friend, Erik Darling from Darling Data. Red Gate is taking me on tour across several points of interest across the globe. New York City, August 18th and 19th.

Dallas, September 15th and 16th. And Utrecht, October 1st and 2nd. And then, of course, the event that we’ve all been waiting for, Past Data Community Summit in Seattle, November 17th to 21st. But with that out of the way, let’s do these office-y, hour-y questions here. Let’s see. Let’s make sure that’s framed up nice for everyone. We don’t want anyone to have to struggle to read these things, do we? More importantly, we don’t want me to struggle to read these things.

How often have you dealt with corruption? When is the last time you saw it? Hmm, geez. So, I don’t deal with corruption a lot. It’s not really up my alley. You know, I am a performance-tuning person. I enjoy performance-tuning.

I do not enjoy things like corruption, high availability, disaster recovery, backup, stuff like that. I can, you know, I can work with it a bit, but it’s not, those are not things that I care deeply about. So, I have dealt with corruption a few times while consulting.

You know, most people are surprised when we come across it. You know, some of the initial analysis that I do when I work with people does, like, look at things where, you know, corruption might be popping up, cropping up, hiding, and where they just might not, like, be able to see it. So, you know, like, use SP Log Hunter and look at the error log.

Use SP Health Parser and go through the System Health Extended event. And then, also, I look at the Memory Dumps DMV. And sometimes, you look in the Memory Dumps, and the Memory Dumps will also indicate data corruption.

But it has been a while since I saw it. I don’t see people hitting data corruption issues so much. I think, you know, disks have become, at least to my mind, sufficiently resilient that corruption is not as common of an issue as it used to be.

I, you know, I don’t have any experience with that aside from the work that I do. But, you know, I do run across people who have not run DBCC CheckDB ever. And, you know, maybe they have gotten at least, I don’t know, maybe very lucky that they have no corruption.

But it’s just not something that I run into a lot. Let’s see here. Have you ever met a query you couldn’t tune?

Well, I mean, yes and no. There are certain, let’s just call them pathological issues with an environment that make some queries much more difficult to tune than others. You know, a lot of the times, if you are not allowed to add indexes, then tuning a query becomes a little bit more difficult because, you know, like a lot of the sort of logical rewrites that you do would depend on having some sort of reasonable index around that would allow you to access data in an efficient way.

If you are dealing with tables that are very big and don’t really have indexes on them, that becomes a little bit more difficult. But, you know, as far as just like, like, have I ever looked at a query and been like, I have no idea how to fix this? No, that’s, you know, that it’s been a very long time since I’ve had that happen to me.

But I’m trying to think. Yeah, I mean, really, it’s just, you know, dealing with, it’s usually not like, like, like local to just the query that’s in front of me. Usually there are like extenuating circumstances with either the hardware or the database or something that that that make tuning efforts a lot more difficult.

But as far as just like, you know, could I, if given unlimited resource or not even unlimited, just given like some more time and some more resources, I could I could I could really do this. But for now, we can we can take some shortcuts and like, you know, try to get batch mode happening or something to to speed that up or like crank maxed up up a little higher. So we have more CPU is working on on on on all those rows or something.

But there’s never been a query that I’ve looked at and been like, well, I just can’t do anything. We’re we’re we’re stuck. Let’s see here.

Which database will win the database wars? I mean, obviously, DuckDB. No, I don’t know. I mean, look, Postgres is having its moment.

I and I don’t think that’s a bad thing. I do hope that Postgres having this moment will force other database vendors to perhaps realign their priorities and perhaps think a little bit more about their pricing models for things and what they offer along with those pricing models. It is, you know, you know, I joke that like everyone who uses SQL Server and has never used Postgres thinks that using Postgres will solve all of their problems.

But in general, you’re you’re sort of just trading for a different set of problems there. There it’s it is amusing to me also that, you know, doing development work on Postgres is like working with SQL Server 2050. But doing like monitoring Postgres stuff is like working with SQL Server seven.

Right. It’s like there is just not not a lot built in that’s terribly useful or helpful. And I know that there are plugins and extensions and things that you can add to it that surface more.

But yeah, like out of the out of the box, if you don’t have like some some someone who like knows and cares and pays attention, getting getting meaningful information out is a lot more difficult. Postgres query plans specifically are not as friendly and graphically inclined as SQL Server ones are, nor do they surface. I think some of some of some of the more interesting details that the SQL Server plans give you.

So, you know, you know, do I think SQL Server is going to win? Probably not. I mean, SQL Server, to my mind, does have the best query optimizer.

Postgres’s query optimizer is not nearly as good as Microsoft’s. Not to say that it isn’t capable of doing some things better than what Microsoft SQL Server does. But I think like just generally speaking, I would I would much rather have Microsoft’s query optimizer than than Postgres’s.

But, you know, like I said, I think that Postgres having its moment now is a good opportunity for other big relational database vendors to perhaps have a moment of clarity about some of the things that they’ve been doing with the product over the years. Perhaps correct their courses in a way that will make the specter of a free database perhaps a little bit less appealing or something. Anyway, let’s see here.

Oh, I got a good question next, but you got to get through this one first. What are the hardest kinds of queries to write? I answered a similar question, I think, recently. Anything that involves like a lot of math, any sort like I don’t I don’t have any groundings or foundations in math.

So if there’s like, you know, some advanced data analysis formula that you need to apply to stuff, you know, I would have to, you know, go look at that and figure out how to, you know, write the T-SQL version of that, which I am not particularly good at. And I would not be able to necessarily like, like, like, validate easily. Like, well, I wrote all the math out.

It looks right. Can someone tell me if it’s right? So those are the hardest kinds of queries to write for me are the ones that involve like equations and algorithms and things like that, because like, I just don’t have any grounding in those areas. So here’s the question.

Here’s a good question. When will Joe Obish be back for a podcast? Well, I’m hoping that Joe will be feeling in the mood for recording in the, you know, I don’t know, reasonably near future. I don’t have an exact date, but I have at least gotten an email from him expressing interest in recording again.

So hopefully that is on the horizon. Anyway, that’s five questions. They’re short ones again this week.

All right. Well, I don’t know. I think, I guess it’s, it’s nice when they’re not overly long and involved because then I don’t have to think too, too much. But anyway, thank you for watching. I hope you enjoyed yourselves.

I hope you learned something and I will see you in the next video, which will be, I don’t know, something suitably SQL Server-y. All right. Thank you for watching.

Going Further

Learn T-SQL With Erik: Optimistic Isolation Levels Don’t Allow Dirty Reads

Posted on September 5, 2025January 23, 2026 by Erik Darling

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

Learn T-SQL With Erik: Writing Bad Data With NOLOCK Hints

Posted on September 4, 2025January 23, 2026 by Erik Darling

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

Learn T-SQL With Erik: Unfortunate Deadlocks Under Read Committed Locking

Posted on September 3, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Unfortunate Deadlocks Under Read Committed Locking

Video Summary

In this video, I dive into the world of deadlocks in SQL Server, specifically focusing on a unique example that showcases how read queries can deadlock with write queries under the default Read Committed isolation level. This is part of my 24-hour T-SQL course, which includes beginner-level content designed to help you understand and manage these complex issues. I demonstrate this through a simple yet illustrative scenario involving two tables, `deadlock_one` and `deadlock_two`, each with 5,000 rows. By running transactions that update one table while selecting from the other, we observe how SQL Server resolves deadlocks by terminating the transaction that has done less work. This example highlights the importance of understanding isolation levels beyond just read committed, as they can significantly impact performance and stability in your database systems.

Full Transcript

Hey, Erik Darling here with Darling Data. And we are going to continue on with some of the teaser material from my T-SQL course, this one about deadlocks, in which I’m going to talk a little bit about them and show you a fun example of one that is a little bit different from the type of deadlock that you would maybe see in other example, like deadlock examples for beginners. This is, of course, part of the 24 hour, or so, 23, 24 hours of content of beginner material that I have published already. The course is still with a pre-sale price of $250, and we are going to double in value to $500 when the advanced stuff drops after the summer, so, buy now, I guess is your call to action there. Anyway, let’s go talk a little bit about deadlocks. Now, man, you know, one of my least favorite parts of the job is dealing with deadlocks because, well, many of them are very simple and just like, we didn’t have an index there, did we? Like deadlocks can get quite complicated very quickly. Most examples of them, including the one that we’re going to look at today, are part of, like there’s like a two query deadlock, but I’ve run into plenty of situations where there’s a deadlock with a single query on a single table, and you know, you can, either see that from a parallel execution plan or something, and you can even run into them with like a single query, like two, sorry, two queries on a single table, depending on what the query plans look like and what the indexes are doing.

We’ll talk about that in a couple of videos when we talk about, talk a little bit more about how crummy of an isolation level read committed is. But deadlocks are different from blocking because blocking does not represent a situation with a permanent impasse, right? Like, like as soon as whatever query is like leading the blocking finishes or is committed or is rolled back, everything else can make progress.

In deadlocking situations, you have reached this sort of like circular embrace, this like Mobius strip infinity symbol of queries that cannot make, like one or more queries that cannot make progress until something else, like until something intervenes, right? Like there, like there is no hope for this traffic jam until SQL Server wakes up, looks around and says, kaboom, and knocks one of them out of the, the, the executing query queue. So, uh, blocking does beget deadlocks though.

So if you are dealing with deadlocks on your system, you should not just focus on deadlocks. You should focus more, you should also focus more broadly on blocking. Uh, if you’re going to set up, uh, like, you know, uh, an extended event to get deadlocks, you should also set up an extended event to get the block process report because you will, you will capture not like with, like you not only have the deadlocks from the XML deadlock report, but you’ll have like the sort of wider view of all the stuff that’s blocking.

And maybe why things are leading to deadlocks and not just, you know, like staying as like being like, Oh, well, like, you know, we just had this blocking chain and now this thing comes along and screwed everything up. So just sort of generally, uh, SQL Server will choose whatever transaction has done the least amount of work and to kill off when, uh, it wakes up and detects a deadlock. You can, of course, set deadlock priority to a number from negative 10 to positive 10.

If you want to give queries higher or lower priority, as far as who, uh, you know, who will survive the great deadlock wars. So, uh, I’m going to create two tables. One is called deadlock one and one is called deadlock two.

And I’m going to put about 5,000 rows into both of them. And this is just what the tables look like. Now there’s a, you know, pseudo random date and value data in here. And, uh, what we have is just IDs from one to 5,000 in both of them.

That is the, that is the not random part of this at all. Now, what I’m going to show you as far as the deadlocks go is a little bit different from what a lot of other people will show you when they want to show you your first deadlock. Most of the time it’ll be between like, you know, uh, updates or deletes or inserts like modification queries, which is fine.

Modification queries can absolutely deadlock with each other, but under SQL servers, default read committed locking isolation level, which is, uh, as far as I’m concerned, um, one of the most garbage isolation levels to ever exist. Uh, you can see deadlocks just between read queries and write queries. So what I’m going to do is, uh, I’m going to start a transaction.

And these are the two examples that we’re going to use. We have a transaction called T1 that is going to, uh, update, uh, deadlock one where ID equals one, right? So we have a, we have the clustered primary key on ID equals one.

So we seek to this immediately and then we are going to get a count of just from like join both of the tables together on that ID column. Uh, the second query is part of the deadlock is just going to be in a transaction called T2. I didn’t name these for any particular reason other than to differentiate them in the windows.

Uh, and then in this one, we are going to update deadlock two and then get a count from both tables. So we’re kind of doing the same thing in both of these. So in T1, if I come over here and begin the transaction and run the update and in, uh, this window for T2, I just try to run the whole thing.

And then I come back over to T1 and I run the select portion of this. Eventually one of these queries will get knocked off. Which one?

I don’t know. Hey, it was this one. I don’t have to switch windows. Right. But now this is a deadlock that occurred between read queries and write queries. Again, this can happen under, uh, isolation levels, not just read committed. Of course they can, if we were under serializable repeatable read, we would, we could have the same situation occur.

Because the shared locks that this query is that, that the read queries are trying to take are also going to interfere with the exclusive locks that the update queries are trying to take. So not only do we have the updates blocking the reads, but now we have the reads blocking the updates. And now we have a sort of, uh, little, little X shape thing where our shared locks and our, and our X locks are messing with each other.

And they are not having a good time. Someone had to die at this party. And that’s, that is not a good party.

As far as I’m concerned, there are, there are far better ways to throw a fun time than killing someone. Right. It’s not, not, not pleasant. So, uh, this is again, behavior that will happen under isolation levels where, uh, where, uh, read queries and write queries compete with each other. So read committed locking, uh, repeatable read and serializable.

These kinds of deadlocks don’t occur. If you use a real version and isolation level, like read committed snapshot isolation or snapshot isolation, or if you use, uh, an uncategorized isolation level, like read uncommitted. Of course, if you use read uncommitted, you deserve all the pain that you get in life.

And maybe you should go to that kind of party. I don’t know. I’m, I’m, I have mixed feelings about you. Anyway, uh, just a little bit about deadlocks here. Thank you for watching.

I hope you enjoyed yourselves and I will see you over in the next video where we will talk about, uh, exactly why, uh, I have very mixed feelings about this read uncommitted isolation level. So, all right, thank you for watching.

Going Further

Learn T-SQL With Erik: Lock Compatibility

Posted on September 2, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Lock Compatibility

Video Summary

In this video, I dive into the basics of locking in SQL Server, covering essential concepts for beginners while also touching on some interesting nuances that might surprise even experienced DBAs. Starting off with a brief overview of my Learn T-SQL with Erik course, which is currently available at a presale price of $250 before it increases to $500 post-summer, I then delve into the fundamental types of locks—exclusive (X) and shared (S)—and how they interact within different isolation levels. I explain lock compatibility in detail using practical examples, such as how SQL Server handles row-level deletions and subsequent reads from the same table. The video concludes with a teaser on deadlocks, setting up for an upcoming discussion that will explore this complex topic further.

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we are going to talk about locking a little bit. Again, you know, sort of basic beginner concepts, but there are some interesting things that we’re going to go over along the way. This is, of course, all teaser material from my Learn T-SQL with Erik course. There’s about 23 hours of it available now. All the beginner material is out. It’s at the presale price of $250, and that will go up to $500 when you’re going to get it. So, of course, when the advanced material has popped after the summer. But for now, let’s go talk about a little about locking in SQL Server. Now, broadly, you can categorize locks into two categories. You have exclusive or x-locks, which occur when your queries are going to modify data. You can, of course, add locking hints that will change the locking behavior of your queries. Select queries. You can add an upd lock hint to your select query. In other databases, it’s more common when you see the select for update pattern where you’re going to select some rows or a row or something and then do something to change that row later. And, of course, there are exclusive locks when you’re going to change data and shared locks, s-locks, when you’re going to read data. There are many, many different lock subcategories, too.

So, if you ever looked at the sys.dm.os.waitstats view and just searched for weight types that start with LCK, at least in SQL Server 2022, there are 64 different subtypes that you can run into as far as locks go, which is a pretty fair amount of subtypes. But they all mean the same thing, that something has taken a lock, right? So, something wants to take a lock. Waited on taking a lock. There we go. That’s probably the most more accurate way of saying it.

Exclusive locks are quite aptly named, of course, because their access is taken in a way that will prevent other exclusive locks from being taken. There are some caveats to this with, I guess, you know, the snapshot isolation level, but we’re going to talk about those way later on. Exclusive locks will also block shared locks, depending on, like, assuming that the locks are taken at a granularity that prevents the shared lock from reading the row or rows that it cares about.

There are, of course, caveats to this as well when it comes to isolation levels, not just snapshot, but also read committed snapshot isolation and, of course, the read uncommitted isolation level, which, you know, you can just read any old thing that comes along. Shared locks are a bit different. Shared locks are a bit different.

Shared locks are a bit different. But, you know, in general, shared locks don’t accumulate in a way that lock escalation occurs. SQL Server cares about lock escalation because locks are managed with memory.

SQL Server tries to be courteous and respectful about your memory. Sort of. You know, we all know SQL Server is a memory pig.

But because of that, locks may escalate from the row or page up to the object, table, or partition level. Locks do not escalate from row to page. That intermediate step does not exist.

You go from row to object, table, or partition, or page to object, table, partition. You do not go from row to page. Right?

I just want to make that very, very clear. In SQL Server, the ability for locks to be acquired when other locks exist is called lock compatibility. I’ve tried to put together a table here to show you if a lock can be taken.

That’s the request mode. If another lock has been granted on that data. Right?

So this table is, I don’t know, for me, it’s pretty useful to figure out, hey, if this lock exists, can I take this other lock? SQL Server kind of does this pincer movement when it’s trying to figure out if the locks that it wants to take are available to be taken. So, you know, if you’ve ever looked at, you know, block process or lock acquired stuff, you might see a lot of, like, database level shared locks because that’s just queries going into the database to say, hey, I need to use this.

We can all share it. And then as queries get down to very specifically what data they actually want to work with, that’s when they start checking to make sure that, like, whatever locks they want to take are, like, available to be taken. They’re not blocked.

Like, nothing else has that lock or nothing else has a competing lock from this table that would prevent it from going through. Just a very simple example of that is if we have a table called, well, we have a table called two rows. It’s not an if.

We definitely have one called two rows, and there are definitely two rows in it. If we come over here and we just run this query, we’ll see that there are two rows and one column in the entire table, IDs one and two. And if we run, if we, like, I talked about it in the last video, this is sort of like just a reinforcement point here, is if you are going to begin a transaction, right, if you’re going to have, like, call an explicit transaction, or if you are going to modify data in your database, you need to do everything you can to make that as short a window of time as possible.

I realize that not everything can be made as quickly as you would like it, but there are lots of things you can do to make these queries go as quickly as possible. The reason for that is because you want to reduce the surface area of your modification queries as much as you can, particularly if you’re using the default locking read committed isolation level. The longer you hold locks for, right, which can be exacerbated by long-running, like, explicit transactions, or just long-running auto-commit transactions from a single query trying to change data, the bigger that surface area is, the more chance there is of it causing problems for other things trying to work with that same data.

It doesn’t matter if it’s read queries or write queries because they can both be interfered with. Of course, you know, NOLAkins can get you around a lot of that, but that’s not what you want. And row versioning isolation levels can help with that, but they are not a free ride.

So even if, you know, you’re aware of things that you can do to sort of, like, remediate blocking situations that have negative performance impacts for you, like, there still might not be, like, a great answer aside from, like, you know, like, working to make those locks last for a shorter amount of time so that fewer things mess with it. Anyway, just a very simple, like, example of the sort of pincering movement of can I take this lock or not. Let’s say that we open a transaction and we delete the row with ID 1 from our table called 2 rows.

We haven’t committed this transaction yet, which, you know, you can, that can lower your opinion of me if you’d like. We are, right now, we are being bad SQL Server practitioners and we are leaving this transaction open. But if we try to select from the table where ID equals 2, because we have an index on this table that allows us to seek precisely to the row that we care about, we are able to still read ID equals 2.

Because ID equals 2 is a different row than ID equals 1. Right now, over here, if we go come back to the delete query, the execution plan for this is very simple. If we look at, hover over the clustered index delete, we’ll see the seek predicate where we have deleted the row where ID equals 1.

Right? That’s this thing here. So, because we have, because we are able to seek to a value in this unique index, SQL Server is like, well, great.

Right? I can read stuff around that. So, this is the way that, like, you know, the lock compatibility stuff works where, you know, even though the row for ID 1 has that exclusive lock on it, the row for ID 2 has nothing on it.

And SQL Server is like, well, cool. I can go take that. Of course, if we try to select the row for ID 1, this will immediately be hung up.

We are not able to read this. Right? We can’t do anything with this right now because, guess what, that delete still has that transaction open and the lock still held. We can, of course, get around this with the no lock hint.

But is this what we want to see? Is this correct? What if we come over here and we change our mind? Are we okay with the fact that this thing read a row that we thought we might want to delete but is now back in the table?

And these are the sort of questions that we have to start asking ourselves when we start, you know, festooning our queries with no lock hints is, are we okay with, like, you know, like, granted, if a transaction is going to succeed, this might be fine. This might be correct. But if there’s any chance that that transaction might change its mind or fail or any number of other things, is the fact that we saw no row here for that okay?

Is this really the behavior that we’re after? In the next video, we’re going to talk about deadlocks, which is like locking plus. Right?

It’s just like, not only is it blocking, but we also have this now problem where two queries get stuck because they both want to do things that the other one has a lock on. So thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. And I will see you over in the next video where we’ll talk about deadlocks. All right. Goodbye. Goodbye.

Going Further

SQL Server Performance Office Hours Episode 27

Posted on September 1, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 27

To ask your questions, head over here.

What are stupid things to monitor in SQL Server?

INTERVIEW QUESTION: what’s your sql weakness?

If you were a sandwich, what kind of sandwich would you be? Would you eat yourself?

I am not an expert and have a hard time telling when what someone is saying is BS. What are some signs?

Is there a way to tell if rows are locked before trying to modify them?

Video Summary

In this video, I had a fantastic Office Hours session with the Darling Data community! We dove into some fascinating topics, including what to monitor in SQL Server and which metrics are actually useful for troubleshooting. I shared my insights on avoiding meaningless stats like page life expectancy and context switches, advocating instead for more actionable metrics such as wait stats and query store data. We also tackled a few fun interview questions—my weakness is clearly not math, but rather the complex algorithms that others can craft so effortlessly! The sandwich question was a highlight, with roast beef and Swiss on rye being my go-to choice. Lastly, we discussed how to spot misleading information in technical content, particularly when dealing with large language models. Overall, it was an engaging session full of great questions and lively discussions—thanks for tuning in!

Full Transcript

Erik Darling here with Darling Data, and I have a very, very exciting Office Hours episode for you, I’ll tell you what. It’s going to be twice as exciting as the last one. I have no substantive facts to back that up. I just have a feeling in my bones. My bones feel excited. But anyway, if you would like to ask your own questions for these Office Hours episodes, there’s a link right there. It’s down in the video description. You click on it, you ask the question. It’s wonderful. Everyone gets a chance. If you like this channel content and you want to sign up for a membership, that is also down in the video description. It’s a grand thing to do. For as few as $4 a month, you can make me happy. If you need SQL Server consulting help, health checks, performance analysis, hands-on tuning, dealing with performance emergencies, and of course, training your developers so they stop giving you heart attacks and other problems, then I am the man for this. The job. And of course, as always, my rates are reasonable. My performance tuning training, again, that link, that discount code there, the everything bundle becomes yours for life for about $150 US. And of course, the thing that I have been working so very, very hard on to the point where I barely sleep anymore is my, I’m kidding, I sleep very well, is my new T-SQL course. All 23 hours of the beginner content is out available. You can go grab it, watch it, to your heart’s content. And of course, it is available at the pre-sale price of $250. And that will double in value to $500 when everything is done.

Red Gate. Sweethearts at Red Gate taking me all over the world this summer. New York City, August 18th to 19th. Dallas, September 15th to 16th. And Utrecht, the Netherlands, October 1st and 2nd. This is all part of the Pass on Tour series of events leading up to Pass Data Community Summit, not Comumity Summit. It’s a funny thing that happened once upon a time. And that is in Seattle, November 17th to 21st. But with that out of the way, let’s do the office hours voodoo that we do so well. We got some real brain acres today here. This is going to be a purse swinger. Let’s see. What are stupid things to monitor in SQL Server?

Anything that you would look at and think, what do I fix? Page life expectancy? Stupid. Beat it. Anything that’s a queue length, like bin it. Context switches. What are you going to do? Stick to stuff that is, I guess, things that when you see them, you’re like, oh, I can tie that to something. Wait stats are great. You know, like having like query store on is great. Look at query store. It will tell like you can feel like I have a very good store procedure called SP Quickie Store that you can look in there and see your worst queries and query store. It’s wonderful. Block process and deadlock reports. Wonderful things to look at so that you can monitor what’s actually going wrong on your server and not just have this wall of numbers and wonder, oh, okay. Well, what caused the context switch?

Why did PLE drop? Who cares? Like look at things that give you something to fix and work on. Like, like, like if you want to like gather a bunch of numbers and just be like, hey, I guess I made them go down. Cool. Right. That’s, that’s something you can do. Otherwise, you need something tangible to go work on. Right. You need a, like, you need something to do. Right. Fill your day with joy.

All right. Interview question. What’s your SQL weakness? Oh God. Math. I am not a math person. Uh, I, I read, uh, articles that other people write where they do like all sorts of crazy math algorithm calculation stuff in their queries. And I’m like, I don’t know. How do you know how to do that? Did you go to school? What? These, these numbers are foreign to me. There’s, there’s like a, like Greek letters and stuff. And I’m like, oh, well, smarty pants over here. I just make the queries go faster. You write the math. I’ll make them, I’ll make it fast. That’s, that’s my SQL weakness. All right. Oh, this is, this is the, this is the best question that’s ever been asked in office hours. If you were a sandwich, what kind of sandwich would you be? Would you eat yourself? Oh, any day of the week. Uh, so I mean, my, my favorite sandwich in the world is of course, uh, the, the roast beef and Swiss on rye. Uh, you gotta have the deli mustard on there.

No weird mustard, uh, prefer a Dijon of some variety and, uh, we are good to go. And boy, I tell you, I take a bite of that right now. All right. Uh, oh boy, this is, this could be a long one. I am not an expert and have a hard time telling when someone is saying BS. I assume we’re talking about, about SQL Server. Uh, what are some signs? Um, I don’t know. I mean, obviously if, if you detect, uh, through your advanced knowledge of what LLM output looks like, uh, that would be the first sign.

Um, you know, uh, like anything that is, you know, a bunch of emojis, um, you know, like random percentages, like, and numbers, like, uh, like I made my CPU 38% happier last week. Like, Oh, okay. Yeah. Great. Um, but like, if you’re, I don’t know, like if you’re reading a blog, like, I don’t know, stuff that sticks out to me is like, especially when someone is like, start, like starts off with like a problem statement. And it tells you they’re going to solve this. Like I have this query that runs slow, uh, and we’re going to make it faster in this blog post.

And then like the blog post drags on and like, like you can tell the query is not getting any faster, but like they start talking about other metrics and like start like just like, like referring to things like, like, oh, this query runs for 1.3 seconds. So now we’re going to do this thing. And, and look, we reduced logical reads and look, the query cost is lower. And you’re like, the query didn’t get any faster. What happened? Hey, where are you going? And like, stuff like that. Um, but you know, I honestly, like most of the BS comes from LLMs these days.

So really just if, if someone like, if like something like, like emoji filled nonsense shows up, just skip over it, right? Just leave it alone. It’s not, it’s, it’s like commodity information that you can get like anywhere. Like you could go ask an LLM for this thing and it would give it to you. Like this, this person did nothing to provide it to you.

Uh, so like, I don’t know that that’s, that’s, that’s kind of what I’m on the lookout for these days. Um, you know, like I suppose it’s different if someone is like presenting something and saying stuff out loud. Cause you know, I’ve, I’ve certainly sat in presentations where like the person presenting was completely wrong about something.

But like, I mean, I don’t know, like, like, like either someone else wrote the presentation and they’re just reading it or like, like whatever, like wherever they got this thing from was, was wrong in the first place. Like their source of information was bad and you’re just sitting there, but like, they are very, but like it’s on the slide and they are very confident that it’s true. So, uh, that, that situation is a lot harder, uh, you know, cause you walk away from that, like, you know, this person was presenting live at a conference.

Like how they had to know what they were talking about, but there’s not a lot of like the conference tech review of like the materials beforehand. A lot of that’s just like, yeah, go like you really, you’ll talk for free. Come on, do something.

Uh, so, you know, that, that situation is harder, but, um, you know, just in general, you know, like pay it, like kind of like pay attention, like try to follow along. If they jump around a lot and there’s a lot of like, but look over here now. Oh, look at this.

Look, that Santa’s way too fat for that chimney. Uh, that’s when you can kind of get a sense that like, you know, they’re, they are misleading you in some way. Uh, oh, whoa, boy. All right.

Technical question here. Uh, if, is there a way to tell you? If rows are locked before trying to modify them. So, uh, you could, of course, like, you know, interrogate various DMVs and look for, uh, unfriendly locks. But at like the, at the row level, that would be tremendously difficult, uh, if not impossible.

And, um, you know, like it would come at some expense trying to like interrogate these views all the time. Uh, probably what I would do is just set a lock timeout. And like, like throw a retry loop around the code.

Like you don’t want your code sitting there. Like, you know, like if exists like this. Oh, wait, hang on a second. Like doing that.

Uh, or like while exists, wait for something. Um, like you, like sending, sending the lock timeout, you know, set it for like, I don’t know, like a second or two. And if you can’t get a lock in that time, like go into some sort of retry thing.

Uh, cause like the lock timeout sends, sends back a very specific error number. So you can use the error number function to say, oh, like if like the lock timeout, like expired, I hit this error number. It’s like 12 something.

Uh, and then you get that error. And if you like, that’s the error that you got from it, it wasn’t like a primary key violation or some other failure, then you would just like, like, oh, cool. It’s, it’s like my lock, my lock expired, my lock timeout expired.

Like I’ll wait for like, you know, a second, maybe some exponential back off math in there and then retry it. So I wouldn’t, I wouldn’t try to check if rows are locked before, um, before trying to modify a query. I would, I would just, you know, I would just go for it and try it.

And then set like with the lock timeout, let your query expire and go do something. Anyway, uh, that is five questions. Count them two, three, four, five, six.

Uh, I think we’re good here. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and please keep these great questions coming. Uh, I particularly enjoy ones about sandwiches. So more sandwich questions are better.

All right. Thank you for watching.