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
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.