Why do we need intent lock (Intent Locks) in SQL Server?

1 years ago, I wrote an article in SQL Server why we need to update the lock. Today I want to continue this discussion, talk about intent locks in SQL Server, and why they are needed.

lock hierarchy in SQL Server

When I talk about lock escalation in SQL Server, I start with the lock level that SQL Server uses, when you read or modify your data.

When you read a record, SQL Server requests a shared lock (S)by default, and when you modify a record, SQL Server requests an exclusive lock (X). These 2 locks are incompatible with each other, and blocking occurs when you read and write a record at the same time.

Also for row-level locks, at the lock level, SQL Server requests the so-called intent Lock (Intent Locks)at a higher level: at the page and surface level. SQL Server requests the following intent locks based on the requested row-level locks:

• Intent shared Lock (Intent shared Lock) When you have a shared lock (S) at the row level
• Intent update lock (Intent update lock (IU)) when you have an update lock (U) at the row level
• Intent exclusive Lock (Intent Exclusive Lock (IX)) when you have an exclusive lock (X) at the row level

So when you read or write your records, you always get the lock level as shown. When does SQL Server use these intent locks?

intent Lock in SQL Server

Technically, SQL Server does not really need an intent lock. This is related to performance optimization. Let's take a concrete look. With intent locks, SQL Server indicates that you need to request additional locks at a higher level in the lock hierarchy. The Intent shared lock (Intent shared Lock) tells SQL Server that there is a shared lock (S)somewhere. The same is true for Intent update lock (Intent update lock) or Intent exclusive lock (Intent Exclusive Lock) , but this time SQL Server knows that there is an update lock somewhere (update Lock) or exclusive locks (Exclusive Lock). It's just a sign, nothing else.

But how does this identity help with SQL Server performance optimizations? Let's say you request an exclusive lock (X) on the surface level. In this case, SQL Server needs to know if there is an incompatible lock on a record (like a shared lock (S) or an update lock (U)). Without intent locks, SQL Server needs to check each record to see if there is an incompatible lock granted.

However, in the case of a surface level intentional shared lock (IS), SQL Server immediately knows that a shared lock (S) has been granted somewhere, so it cannot request an exclusive lock (X) at the surface level. This is the reason why there is an intent lock in SQL Server: At the lock level, if there is an incompatible lock in place, you can have SQL Server quickly check it out. It's simple, isn't it?

Summary

Technically, SQL Server does not require an intent lock because it only represents a lock level where there are some other specific types of locks. When based on if you want to request a specific lock on a page or table, SQL Server can more efficiently check whether an incompatible lock exists or an intent lock is required.

Thanks for your attention!

Original link:

https://www.sqlpassion.at/archive/2016/05/16/why-do-we-need-intent-locks-in-sql-server/

Why do we need intent lock (Intent Locks) in SQL Server?