What is an Intent Lock in SQL Server?
Here is my query
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN UPDATE c SET c.Score = 2147483647 FROM dbo.Comments AS c WHERE c.Id BETWEEN 1 AND 5000;
Which have these stats
+--------------+---------------+---------------+-------------+ | request_mode | locked_object | resource_type | total_locks | +--------------+---------------+---------------+-------------+ | RangeX-X | Comments | KEY | 2429 | | IX | Comments | OBJECT | 1 | | IX | Comments | PAGE | 97 | +--------------+---------------+---------------+-------------+
I wonder about the IX, which is an Intent Lock. What does that mean, and why does there exist one on the table itself? As I understand, it is not a true lock but more something SQL Server use (or is set by the transaction?) to indicate that a lock might occur.
Is the above right? Please explain the intent lock in SQL server?
An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared (S) locks on pages or rows within that table.
You are correct, it is a signal to the engine that you wish to put a lock on an object. To over-simplify it, imagine you want to obtain a book from the library, if somebody has the book checked out you cannot obtain it, so you put your name on a list of people who intend to check that book out. When everybody else is done with it, and it is your turn, then you are allowed to check out the book.
If you haven't done so already I highly recommend reading Transaction Locking and Row Versioning Guide on MSDN which provides some great detail on how it all works. The below is a snippet from that page:
Intent Locks
The SQL Server Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level. Intent locks serve two purposes:
- To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
- To improve the efficiency of the SQL Server Database Engine in detecting lock conflicts at the higher level of granularity.
For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because the SQL Server Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.