Lock Granularity: Table Locking, Page Locking, and Row Locking

There are five different types of lock granularity used by the server. Two of these, the intent and extent lock, are special cases.

Lock granularity refers to the size of the object affected by a lock.

Page Locks

Page locking is the level most often used by the server. A page of information is 2KB in size. After subtracting room for overhead, 1962 bytes per page are available for data. Rows must fit completely on a page. If a row of data takes up 200 bytes, nine of them can fit on a page of data. If one of those rows is updated, the server needs to place a page-level lock to alter the row. The other eight rows are also locked and unavailable for use by other connections, because the entire page is unavailable.

Row-Level Insert Locks

Row-level locking is available for insert only. Insert row-level locking is useful when you see a lot of locking contention on inserts (for instance, users waiting a perceptible number of seconds to insert a row of data). I won’t go into great detail about how to implement row-level locking, or the implications it has. To enable it on a table, execute this command:

sp_tableoption   “titles”,   “insert   row lock”,   true

The first argument is a wildcard that matches table names in the form owner.object.

If row locking is on, two new locks will come into play. Insert_page locks are acquired by connections inserting rows on a page. Link_page locks are acquired by the first process to get an insert page lock. Insert page locks allow only other insert page locks on the same page.

Table Locks

If a query affects a lot of data in a table, rather than acquire thousands of page locks, the server will try for a table lock. Some queries that the server knows will affect the whole table (such as an unrestricted UPDATE AUTHORS...) will cause the server to try for a table-level lock right away. For other queries, which affect a third or half the table, the server might initially request page locks and then try for a table lock when it notices how many page locks it needs.

You can have exclusive or shared table locks. Exclusive table locks can be bad because they prevent all other access to the table by other users.

Intent Locks

One intent lock is placed at the table level by a process that is holding page locks. This is used to alert other processes that the table has something going on, and no one should attempt a table lock. A process with an intent lock and several page locks on a table may elect to escalate its page locks to a single table lock. But if more than one process is accessing a table and has intent locks, no one is able to acquire a table lock. This is a common and fairly healthy consequence of active systems.

Extent Locks

The server organizes data into 2KB pages of information. Those 2KB pages are further organized into groups of eight. These 16KB blocks are called extents. When an object is created, the smallest amount of space it is allocated is one extent. Each extent belongs to an object, or is unallocated. Extents aren’t shared. When an object grows and needs more pages to hold data, the server gives it a page from one of its allocated extents. If there are no available pages, it allocates a new extent and gives it a fresh page from the new extent.

When you see extent locks, the server is allocating space. Most often, this is a result of a new object being created in tempdb. When you create worktables by using an ORDER BY in a query, the server must allocate an extent to make room for the worktable. Extent locks also occur when indexes are being built or rebuilt.

Locks Blocking Processes

If you see a lock in sp_lock with _blk after it, this indicates that the lock is holding a page that has been requested by another process. You can use the sp_who stored procedure to find out which user has which process ID.