A Little About Locking And Isolation Levels In SQL Server

A Little About Locking And Isolation Levels In SQL Server

---

Video Summary

In this video, I delve into the intricate world of locks, blocking, and isolation levels in SQL Server, sharing insights gained from years of consulting work that often involves troubleshooting store procedures for blocking and deadlocking issues. I explain how not all locking leads to blocking and discuss various isolation levels—read committed, snapshot, repeatable read, serializable—and their implications on query execution. Additionally, I cover different types of locks SQL Server can take, such as row identifier, key or row level, page, extent, heap or b-tree, object, application, metadata, allocation unit, and database-level locks, providing practical examples to help you better understand these concepts in action.

Full Transcript

Erik Darling here with Darling Data. And I just slammed a shot of espresso, so I am quite jazzed to do this video. What I wanted to talk about in this video is a little bit about sort of locks, blocking, and isolation levels in SQL Server, because I get a lot of questions about it. I suppose that’s the peril of working on store procedures that help folks sort out blocking and deadlocking issues. SQL Server, SP Blitzlock, do a ton of work on that, SP Human Events Block Viewer. SP Blitzlock will look at deadlocks, SP Human Events Block Viewer will look at blocking chain stuff. Both very useful store procedures to me in the consulting work that I do. But the thing that we’re going to talk about off the bat is that not all locking leads to blocking. Many modification queries under various circumstances can push through without impacting any other user queries at all. With optimistic isolation levels, you can, of course, avoid a lot of the most basic reader on writer and writer on reader locking and blocking issues that SQL Server offers you by design under the pessimistic read committed isolation level that it uses by default, which was a terrible mistake.

And you can also, under the snapshot isolation level, to some extent, have modification queries not block each other. But I don’t run into a lot of people using that very happily or very successfully. All right, so let’s start at the top. There are different types of locks that can be taken. If you have a table that does not have a clustered index, and you have row identifiers in it, SQL Server can take row identifier locks. It can also take key or row level locks on clustered and nonclustered indexes.

I use key and row interchangeably because a lot of people in the database universe do that. I do not want to make sure that everyone understands me when I’m talking about these things until someone comes down from Mount Database and has tablets in hand that tells me which term to use for those types of locks. From here on out, I’m going to use them both and use them interchangeably. SQL Server can lock pages, it can lock extents, it can lock heap or b trees, it can lock objects, it can lock application. If you use sp-get-app-lock, you can sort of take a logical lock via a portion of code that disallows other portions of other code to execute until that lock is released.

SPF app-lock is great. I have an old video about that. I might update that in the future, I don’t know. It can take metadata, allocation unit and database level locks. All right, so going back up the list a little bit, I think the extent locking thing was a bit more common when TempDB had a lot of mixed extents in it.

SQL Server would lock extents, that’s why there used to be a lot more. They used to have to use trace flags to avoid a lot of TempDB contention stuff. You can further avoid metadata contention in TempDB by using the in-memory TempDB system tables that popped out with SQL Server 2019.

Got some improvements in 2022. A lot of metadata locks that you might see otherwise would be, you know, if you’re, well, I mean, like, so reading from TempDB system tables while there’s a lot of TempDB activity can cause blocking in there. You know, some of the views that describe tables, indexes, identity columns, things like that, those can get blocked if there’s, like, active inserts to, big inserts to a table or something.

Those are sort of typical metadata locks that I’ve seen around. Also, if you’re updating stats, and, like, queries and store procedures are trying to compile, I’ve seen metadata contention around that. Allocation unit locks I don’t think I’ve seen in the wild.

I would imagine those would have something to do with files, file groups, partition switching, something along those lines. But, again, I don’t, that’s all speculative. I don’t, like I said, I’ve never actually seen allocation units get locked or cause blocking in my travels through SQL Server.

And, of course, database level locks, stuff like, you know, altering the database, alter database commands. You might also see metadata locks if you alter objects within the database. And then you have lock.

And so those types of locks, a lot of those locks, especially, like, the key, page, and object level locks. And, well, at the query level, you can affect those with various hints. You can instruct the storage engine how you think it should go.

But if you don’t do that, then the storage engine really decides both whether to start with the rid or key slash row locks or page locks and then escalate up to object level locks. You can instruct the query to start with one of those, but it’s still, escalation is still possible. You can also, of course, use, like, tab lock, tab lock X to lock down an object entirely.

I do that a lot with inserts into temp tables in order to influence a parallel execution plan. You do need compat level 130 and up to get that going, or you select into and compat level 130 and up. Then you have lock modes.

So shared update, intent exclusive, exclusive, schema stability, schema modification, bulk update, key range. Key range is pretty specific to the repeatable read and serializable isolation levels. You can, to some extent, affect the lock modes that queries use with query hints.

I think probably the most common one you would see is instructing select queries to use upd lock hints. Maybe in combination with some other hints to lock specific rows via select for, like, queuing processes, other stuff like that. Then you have isolation levels.

Now, isolation levels are not decided by the storage engine at all. Isolation levels are decided by queries and database settings and stuff like that, so to some extent, connection string settings. But if left to its own devices, the storage engine, together with the query optimizer via cardinality estimation, decides locks and lock modes.

Now, depending on the intent of the query, the lock mode might be sort of unavoidable. You know, if you’re updating, deleting, inserting data, you’re going to need exclusivity at some point in those processes. And if you’re altering something about the database, you’re going to need exclusivity via, like, alter table, alter column, alter database, stuff like that.

So that stuff gets decided kind of for you, kind of depending on what the query is and what hints you provide. Also, indexes have a lot of impact on the types of locks that the storage engine will be influenced to use. Isolation levels are completely decided by you, the query writer, or you, the database administrator.

Now, you can choose read uncommitted or no lock. You can choose, well, again, the database default is read committed. You can enable read committed snapshot isolation to help your reader and writer queries not step all over each other.

You can enable snapshot isolation to do the same thing, but that does require you to request that isolation level either at the query or at the store procedure level for that to happen. You can also choose repeatable read or serializable. Certain things in the SQL Server will escalate to the serializable isolation level to complete.

Probably the most common one of those would be foreign keys with cascading actions. So if you’re foreign keys, cascade, updates, deletes, or anything like that, then those will be promoted to serializable to respect referential integrity and all that good stuff. You can also, if you so choose, use what’s called the read past hint.

Read past is often sort of confused with the no lock read uncommitted hint, but it is different. Rather than reading in-flight changes to rows, if rows are locked in a table or index, it just says, we’re just not going to read those. We’re going to go on to the next thing.

That’s also very helpful with queuing processes where you don’t want the query trying to find the next thing to work on in the queue to get stuck waiting for something else to release a lock on a row so that it can proceed. The hints in the indexing for those types of processes are really important. So the important thing to remember here is that isolation levels are decided by you, by database level settings, and by certain conventions that you use in the database.

Again, cascading foreign keys, probably the most common thing that you’ll see get escalated to the serializable isolation level. But the isolation level is largely dictated by you. Lock mode is largely dictated by the intent of the query or command that you’re sending over to SQL Server.

You can impact this a bit via various locking hints, but for the most part, again, like I said, some of these lock modes are unavoidable depending on the intent of the query, especially insert, update, delete, or the most unfortunate knuckleball in the world, the merge syntax. And then locks are typically decided by the storage engine and cardinality optimization from the query optimization process. But you can also, to some extent, send directives and hints to the storage engine about what type of locks you want to take.

It is, I guess, a fairly common practice to request row locks, but that can be ignored and those can be escalated up to object level locks. Page level locks are typically not ignored, but those can also escalate up to object level locks. And then, you know, everyone has a weird day.

But of course, like I said before, indexing also will have a bit to do with the types of locks that SQL Server takes, especially for modification queries. It’s very important to index appropriately for the where clause and join conditions of your modification queries. The easier it is for SQL Server to find the data that it needs to modify, typically the better choices it will make as far as which locks to use when those queries execute.

So, a little bit of fun there. Anyway, that’s about enough about that. A lot of abouts in there.

People think I’m Canadian when they hear me say about, because I don’t know why. Maybe I’ve just got a Canadian tongue. Never can tell.

I don’t have any other Canadian mannerisms, though. I can tell by my lack of politeness and… I don’t know.

I don’t want to besmirch any Canadians. My Canadian friend Michael really helped me out with SB Human Events Block Viewer, which I just recorded a video about. So, I’m going to leave the Canadians alone on this one.

Unless they start any more wildfires on me. Turn New York into Mars again. Anyway. Thank you for watching. I hope you enjoyed the video.

If you did, thumbs up. It’s greatly appreciated. I like watching those numbers go up. If you like this type of content, hit the little bell button to subscribe to the channel. I’ll produce a lot of stuff.

Though I am going to be on vacation for a little bit in August. So, video production will necessarily grind to a screeching halt then, most likely. But, you know.

I’ll be back at it someday. And, of course, I hope you learned something. I hope you enjoyed yourselves. And I will see you in another video sometime. Thank you.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.