PAG Lock in SQL Server

A PAGE Lock is a lock applied to an entire 8 KB SQL Server data page rather than individual rows. SQL Server uses page locking as a compromise between row-level locking and table-level locking. The primary goal is to reduce lock management overhead, memory consumption, and CPU costs associated with maintaining large numbers of row locks. When multiple rows on the same page are accessed or modified, SQL Server may choose a PAGE lock instead of many row locks. While this improves efficiency, it can reduce concurrency because other rows on the same page may become inaccessible even if they are unrelated to the transaction. PAGE locks can be Shared, Update, or Exclusive depending on the operation being performed. They are commonly observed during large updates, scans, batch operations, and ETL workloads. PAGE locks can be monitored using sys.dm_tran_locks, where the resource type appears as PAGE. In production systems, PAGE locks often represent a trade-off between scalability and concurrency, and understanding them is important when troubleshooting blocking, lock escalation, and performance issues.

Suppose page number 100 contains:

Row 1  EmployeeID = 1

Row 2  EmployeeID = 2

Row 3  EmployeeID = 3

Row 4  EmployeeID = 4

Row 5  EmployeeID = 5

If SQL Server locks the page 100 locked then all rows inside that page are protected. If this page is having 500 rows. Instead of 500 row lock we can used 1 page lock.

Types of PAGE Locks

PAGE locks can be:

Lock Type	Meaning
S Page Lock	Read Page
U Page Lock	Update Preparation
X Page Lock	Modify Page
IU Page Lock	Intent Update
IX Page Lock	Intent Exclusive

 For easy understanding we can imagine a library.

• Row Lock = Locking a single line in a book.
• Page Lock = Locking an entire page of the book.
• Table Lock = Locking the entire book.

If we need to update many lines on the same page, locking the whole page is more efficient than locking each line individually.

Row Lock vs Page Lock

Feature	Row Lock	Page Lock
Scope	One Row	Entire Page
Concurrency	High	Medium
Memory Usage	Higher	Lower
Blocking	Less	More
Performance	More Overhead	Less Overhead

 Page Lock vs Table Lock

Feature	PAGE Lock	TABLE Lock
Scope	8 KB Page	Entire Table
Blocking	Moderate	High
Memory Usage	Medium	Lowest
Concurrency	Better	Worst