Sql server® 2012 t-sql fundamentals

Lockable resource Types

SQL Server can lock different types of resources. The types of resources that can be locked include 

RIDs or keys (row), pages, objects (for example, tables), databases, and others. Rows reside within 

pages, and pages are the physical data blocks that contain table or index data. You should first famil-

iarize yourself with these resource types, and at a more advanced stage, you might want to familiarize 

yourself with other lockable resource types such as extents, allocation units, and heaps or B-trees. 

To obtain a lock on a certain resource type, your transaction must first obtain intent locks of 

the same mode on higher levels of granularity. For example, to get an exclusive lock on a row, your 

transaction must first acquire an intent exclusive lock on the page where the row resides and an in-

tent exclusive lock on the object that owns the page. Similarly, to get a shared lock on a certain level 

of granularity, your transaction first needs to acquire intent shared locks on higher levels of granular-

ity. The purpose of intent locks is to efficiently detect incompatible lock requests on higher levels of 

granularity and prevent the granting of those. For example, if one transaction holds a lock on a row 

and another asks for an incompatible lock mode on the whole page or table where that row resides, 

it is easy for SQL Server to identify the conflict because of the intent locks that the first transaction 

acquired on the page and table. Intent locks do not interfere with requests for locks on lower levels 

of granularity. For example, an intent lock on a page doesn’t prevent other transactions from acquir-

ing incompatible lock modes on rows within the page. Table 9-2 expands on the lock compatibility 

table shown in Table 9-1, adding intent exclusive and intent shared locks.