I blogged 2 years ago about why we need UPDATE locks in SQL Server. Today I want to continue this discussion by talking about Intent Locks in SQL Server, and why they are needed.
The Lock Hierarchy in SQL Server
When I talked about Lock Escalations in SQL Server, I started by briefly mentioning that SQL Server uses a Lock Hierarchy when you read or change your data.
When you read a row, SQL Server always acquires by default a Shared Lock (S), and when you change a row SQL Server acquires an Exclusive Lock (X). Those Locks are incompatible with each other, and that will introduce blocking situations when you want to read/write a row concurrently.
In addition to the row level locks, SQL Server also acquires so-called Intent Locks at higher levels within the Lock Hierarchy: at the page and at the table level. SQL Server acquires the following Intent-Locks based on the requested row level lock:
- Intent Shared Lock (IS), when you have a Shared Lock at the row level
- Intent Update Lock (IU), when you have an Update Lock at the row level
- Intent Exclusive Lock (IX), when you have an Exclusive Lock at the row level
Therefore you always get the Lock Hierarchy as shown above when you read and write your records. But why is SQL Server using these Intent Locks?
Intent Locks in SQL Server
From a technical perspective the Intent Locks are not really needed by SQL Server. They have to do with performance optimization. Let’s have a look on that in more detail. With an Intent Lock SQL Server just indicates at a higher level within the Lock Hierarchy that you have acquired a Lock somewhere else. A Intent Shared Lock tells SQL Server that there is a Shared Lock somewhere else. A Intent Update or Intent Exclusive Lock does the same, but this time SQL Server knows that there is an Update Lock or an Exclusive Lock somewhere. It is just an indication, nothing more.
But how does that indication help SQL Server with performance optimization? Imagine you want to acquire an Exclusive Lock at the table level. In that case, SQL Server has to know if there is an incompatible lock (like a Shared or Update Lock) somewhere else on a record. Without Intent Locks SQL Server would have to check every record to see if an incompatible lock has been granted.
But with an Intent Shared Lock on the table level, SQL Server knows immediately that a Shared Lock has been granted somewhere else, and therefore an Exclusive Lock can’t be granted at the table level. That’s the whole reason why Intent Locks exist in SQL Server: to allow efficient checking if an incompatible lock exists somewhere within the Lock Hierarchy. Quite easy, isn’t it?
Summary
Intent Locks are technically not needed by SQL Server, because they only indicate if there is some other specific Lock Type somewhere else within the Lock Hierarchy. But based on that fact SQL Server can check in a much more efficient way if an incompatible Lock exists somewhere else if you want to acquire a specific lock at the page or table level.
Thanks for your time,
-Klaus
14 thoughts on “Why do we need Intent Locks in SQL Server?”
Nice explanation!!!
Thanks sir!
Intent locks are to provide integrity too. An intent lock is placed at the database level for example as you do not want another process to be able to drop the database while you’re reading\writing
Hello Perry,
That’s not true, because on the database level you have a Shared Lock to prevent others to drop/restore the database while it is in use.
Thanks,
-Klaus
very nice article. precise, simple & effective.
Explained in layman’s language.. Very nice article!
Great explanation, thanks!
IT’S VERY VERY USEFUL FOR ME TO UNDERSTANDING IS & IX LOCK….THANKS
Very nice info
Nice article.
Great Work, Alternative title would be:
Intent Locks for Dummies eheh
Keep it up!
Very clear, Thanks.
Regards,
Great explanation. Thank you!
“how intention locking helps improve efficiency…”
I was scratching my head over this, and your post is an excellent explanation. Thank you!