Intent (IS, IX, SIX) Lock in SQL Server

Intent Locks are hierarchical locks that SQL Server places on higher-level objects (Table, Page) to indicate that lower-level locks (Row, Key, Page) exist or will be acquired. They allow SQL Server to quickly determine whether a table-level lock can be granted without scanning millions of row locks.

Main Intent Locks:

Ø  IS (Intent Shared) → Indicates Shared locks exist below.

Ø  IX (Intent Exclusive) → Indicates Exclusive locks exist below.

Ø  SIX (Shared with Intent Exclusive) → Shared lock on the object plus Exclusive locks on some lower-level resources.

Imagine a table have 100 million Rows

Session 1

UPDATE Employee

SET Salary = Salary + 1000

WHERE EmployeeID = 100;

SQL Server acquires Row X Lock on one row.

Session 2

Requests TABLOCKX SQL Server must know Are there any row locks already? Without Intent Locks SQL Server would need to inspect 100 million Rows which would be extremely expensive.

SQL Server Solution Place a lock at the table level saying, "Someone owns locks underneath me."

This is called Intent Lock.