Intent locks, one of those things in SQL Server that seem to be very often misunderstood. They’re something that I’ve found myself explaining time and time again, I’ve even been on calls with customer DBAs who don’t fully understand why they’re there and what they’re doing.
So what exactly is an intent lock and why do we need them?
Lock Heirachy
Before we can really get our heads around what intent locks are doing, we’ll remind ourselves of the lock hierarchy and how that works.
SQL Server can put down locks at different levels, row level, page level, table level and database level. Depending on what it’s doing, SQL will figure out the most efficient level to put it’s lock at.
A World Without Intent Locks
Let’s just imagine a World without intent locks for a moment. In that World, a user has just decided to select a row from our database. SQL at that point is going to put down a shared lock against the row.
Now what’s going to happen when another user decides to modify a bunch of rows? Now because of the number or rows involved in this modification, SQL is going to want to take out an exclusive page lock. Where’s the issue here?
Well, SQL can’t take that page lock because we’ve already got a shared lock taken out at a lower (row) level. At this moment in time, SQL’s got no idea about that shared lock so before it can take out the page lock, it’s going to have to check every single row to make sure that there are no incompatible locks at a lower level. That’s going to take some time.
Intent Locks For Performance
So let’s now introduce intent locks.
The difference now is, when our user runs their select and takes out a shared lock at row level, they also take out an intent shared lock at page and table level (they also take out a shared lock at database level, but let’s not worry about that).
This time, when that data modification comes in and SQL wants to take out an exclusive lock at the page level, it’ll see that there’s already an intent shared lock on that page. That tells SQL Server that at some lower level, someone’s holding a shared lock and its going to have to wait before it can take out it’s exclusive lock.
There’s now no need to go off and check all the rows to see if any locks have been taken out. This way, intent locks can help us to improve performance.
Intent Locks Let SQL Server Know That There’s a Lock Taken Out At a Lower Level in the Lock Heirachy
In short, an intent lock is a way of letting SQL Server know that someone has taken out a lock at some lower level in the lock hierarchy, this is going to mean that SQL doesn’t need to go and check all the lower levels for an incompatible lock type and speeds up performance.
Thanks for reading