Lock Types

Lock Types:

Microsoft SQL Server 2000 has multigranular locking that allows different types of resources to be locked by a transaction. To minimize the cost of locking,SQL Server locks resources automatically at a level appropriate to the task. Locking at a smaller granularity, such as rows, increases concurrency, but has a higher overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions, but has a lower overhead because fewer locks are being maintained.

SQL Server can lock these resources (listed in order of increasing granularity).

Resource	Description
RID	Row identifier. Used to lock a single row within a table.
Key	Row lock within an index. Used to protect key ranges in serializable transactions.
Page	8 kilobyte –(KB) data page or index page.
Extent	Contiguous group of eight data pages or index pages.
Table	Entire table, including all data and indexes.
DB	Database.

In addition to tables, pages, rows and keys, the following listing provides information on the other common types of resources that are targets for locks. These will crop up from time to time in your queries against sys.dm_tran_locks.

Key ranges – only occur under the SERIALIZABLE isolation level where protection against phantom reads requires locking of a range of values to ensure that no one can insert records into a range that is scanned.

Extents – locked when tables and indexes grow, and extents are added as a result.

Databases – processes will be issued a shared (S) lock against a database if it locks a resource within a database. This occurs in any database on a SQL instance with the exception of master and tempdb, which allows the SQL engine to perform a check for locks prior to dropping a database or taking it offline, for instance. You will see a DATABASE lock for each request. I filter out DATABASE locks in my locking / blocking queries and consider them noise results.

Allocation units – locked when they are in the process of being de-allocated.

Metadata – occur when a transaction is attempting to change the definition for a given object. For example, altering the recovery model on a database would register an exclusive lock on the Metadata object type for the affected database.

The resource targeted by a given lock is exposed by the resource_type column in the sys.dm_tran_locks DMV.

So let’s start with the lock types. What is the lock? In short, this is in-memory structure (64 bytes on 32 bit OS or 128 bytes on 64 bit OS). The structure has the owner, type and resource hash that links it to the resource it protects (row, page, table, file, database, etc). Obviously it’s more complicated and has quite a few other attributes, but for our practical purposes that level of details is enough.

SQL Server has more than 20 different lock types but for now let’s focus on the most important ones.

Shared locks (S)

Shared locks are held on data being read under the pessimistic concurrency model. While a shared lock is being held other transactions can read but can't modify locked data.

Multiple shared locks can be issued for the same resource, hence the term "shared."

Usually, SQL Server releases S locks as soon as it has finished reading the data. However,use of a higher transaction isolation level, either REPEATABLE READ or SERIALIZABLE,changes this behavior, so that SQL Server holds S locks until the end of the transaction.

In the sys.dm_tran_locks view, a request_mode of 'S' indicates a shared lock.

In the example you can't see the shared locks because they're taken for the duration of the select statement and are already released when we would select data from sys.dm_tran_locks. That is why an addition of WITH (HOLDLOCK) is needed to see the locks.

BEGIN TRAN

USE AdventureWorks2008

SELECT * FROM Person.Address WITH (HOLDLOCK)

WHERE AddressId = 2

SELECT resource_type, request_mode, resource_description

FROM sys.dm_tran_locks

WHERE resource_type <> 'DATABASE'

ROLLBACK

Update locks (U)

UPDATE locks are not really a separate kind of lock, but rather are a hybrid of SHARED and EXCLUSIVE locks. And contrary to what you might think, UPDATE locks are not just acquired for UPDATE operations.  A transaction acquires this kind of lock when SQL Server executes a data modification operation but first needs to perform a search to find the resource to modify.

While SQL Server is searching, it shouldn’t need to acquire an EXCLUSIVE lock; it only needs the EXCLUSIVE lock when the data to be changed is found. Normally, if a SQL Server process was just searching for data, it would acquire a SHARED lock on each resource it encounters and then determines whether it has found the data it is searching for. However, if SQL Server started out with a SHARED lock while searching for the data to modify, there are potential problems. A situation could occur where two processes were both searching for the same resource to modify (for example, the same customer row in the Customers table), using different access paths, and they could both reach the desired resource at the same time. If they both were acquiring SHARED locks on the data they were examining, they could both lock the resource they wanted to change, but before they made the modification they would have to convert their lock to an EXCLUSIVE lock. Since the other process would have a SHARED lock, no EXCLUSIVE lock could be granted. Each process would have a SHARED lock, and each would try to change it to an EXCLUSIVE lock, but neither could proceed because of the presence of the other. This is a deadlock situation, called a ‘conversion deadlock’. 

UPDATE locks are really a deadlock avoidance mechanism. If SQL Server uses UPDATE locks, a deadlock will NOT occur. If a SQL Server process begins a search operation with the intention of eventually modifying data, it acquires UPDATE locks until it finds the data to modify. UPDATE locks are compatible with SHARED locks, but are not compatible with EXCLUSIVE locks or other UPDATE locks. So if two processes were searching for the same data resource, the first one to reach it would acquire an UPDATE lock, and then the second process could not get any lock and would wait for the first process to be done. Since the first process was not blocked, it could convert its UPDATE lock to an EXCLUSIVE lock, make the data modification, and finish its transaction and release its locks. Then the second process could make its change.

In the sys.dm_tran_locks view, a request_mode of 'U' indicates an update lock.

Exclusive locks (X)

Exclusive (X) locks prevent access to a resource by concurrent transactions. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.

Data modification statements, such as INSERT, UPDATE, and DELETE combine both modification and read operations. The statement first performs read operations to acquire data before performing the required modification operations. Data modification statements, therefore, typically request both shared locks and exclusive locks.

In the sys.dm_tran_locks view, a request_mode of 'X' indicates an exclusive lock.

For example, an UPDATE statement might modify rows in one table based on a join with another table. In this case, the UPDATE statement requests shared locks on the rows read in the join table in addition to requesting exclusive locks on the updated rows.

BEGIN TRAN

USE AdventureWorks2008

UPDATE Person.Address

SET AddressLine2 = 'Test Address 2'

WHERE AddressId = 5

SELECT resource_type, request_mode, resource_description

FROM sys.dm_tran_locks

WHERE resource_type <> 'DATABASE'

ROLLBACK

Intent locks (I)

The 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 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 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.
Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Lock mode	Description
Intent shared (IS)	Protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.
Intent exclusive (IX)	Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. IX is a superset of IS, and it also protects requesting shared locks on lower level resources.
Shared with intent exclusive (SIX)	Protects requested or acquired shared locks on all resources lower in the hierarchy and intent exclusive locks on some (but not all) of the lower level resources. Concurrent IS locks at the top-level resource are allowed. For example, acquiring a SIX lock on a table also acquires intent exclusive locks on the pages being modified and exclusive locks on the modified rows. There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.
Intent update (IU)	Protects requested or acquired update locks on all resources lower in the hierachy. IU locks are used only on page resources. IU locks are converted to IX locks if an update operation takes place.
Shared intent update (SIU)	A combination of S and IU locks, as a result of acquiring these locks separately and simultaneously holding both locks. For example, a transaction executes a query with the PAGLOCK hint and then executes an update operation. The query with the PAGLOCK hint acquires the S lock, and the update operation acquires the IU lock.
Update intent exclusive (UIX)	A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks.

The request_mode column of the sys.dm_tran_locks view by IS, IX and IU, respectively.

BEGIN TRAN

USE AdventureWorks2008

UPDATE TOP (5) Person.Address

SET AddressLine2 = 'Test Address 2'

WHERE AddressId = 5

SELECT resource_type, request_mode, resource_description

FROM sys.dm_tran_locks

WHERE resource_type <> 'DATABASE'

ROLLBACK

Schema locks (Sch)

There are two types of schema locks:

Schema stability lock (Sch-S): Used while generating execution plans. These locks don't block access to the object data.

Schema modification lock (Sch-M): Used while executing a DDL statement. Blocks access to the object data since its structure is being changed.

In the example we can see the Sch-S and Sch-M locks being taken on the system tables and the TestTable plus a lot of other locks on the system tables.

BEGIN TRAN

USE AdventureWorks2008

CREATE TABLE TestTable (TestColumn INT)

SELECT resource_type, request_mode, resource_description

FROM sys.dm_tran_locks

WHERE resource_type <> 'DATABASE'

ROLLBACK

Bulk Update locks (BU)

Bulk Update locks are used by bulk operations when TABLOCK hint is used by the import. This allows for multiple fast concurrent inserts by disallowing data reading to other transactions.

Conversion locks:

Conversion locks are locks resulting from converting one type of lock to another. There are 3 types of conversion locks:

Shared with Intent Exclusive (SIX). A transaction that holds a Shared lock also has some pages/rows locked with an Exclusive lock

Shared with Intent Update (SIU). A transaction that holds a Shared lock also has some pages/rows locked with an Update lock.

Update with Intent Exclusive (UIX). A transaction that holds an Update lock also has some pages/rows locked with an Exclusive lock.

In the example you can see the UIX conversion lock being taken on the page:

BEGIN TRAN

USE AdventureWorks2008

UPDATE TOP(5) Person.Address

SET AddressLine2 = 'Test Address 2'

WHERE PostalCode = '98011'

SELECT resource_type, request_mode, resource_description

FROM sys.dm_tran_locks

WHERE resource_type <> 'DATABASE'

ROLLBACK

Key - Range locks:

Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction. In the example we can see that there are two types of key-range locks taken:

RangeX-X - exclusive lock on the interval between the keys and exclusive lock on the last key in the range

RangeS-U – shared lock on the interval between the keys and update lock on the last key in the range

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRAN

USE AdventureWorks2008

UPDATE Person.Address

SET AddressLine2 = 'Test Address 2'

WHERE AddressLine1 LIKE '987 %'

SELECT resource_type, request_mode, resource_description

FROM sys.dm_tran_locks

WHERE resource_type <> 'DATABASE'

ROLLBACK

Lock duration:

The length of time that SQL Server holds a lock depends primarily on the mode of the lock and the transaction isolation level that is in effect.

Read Uncommited won’t holds Shared Locks

READ COMMITTED is SQL Server's default isolation level.

At this level, SQL Server releases S locks as soon as it has read and processed the locked data.

It holds an X lock until the end of the transaction, whether the transaction is committed or rolled back.

It holds a U lock until the end of the transaction,unless it promoted the U lock to an X lock, in which case the X lock, as with all X locks,

remains for the duration of the transaction.

If the transaction isolation level is REPEATABLE READ or SERIALIZABLE, S locks have the same duration as X locks. That is, SQL Server does not release them until the transaction is over.

In addition to changing the transaction isolation level, we can control the lock duration by using lock hints.

Lock ownership:

We can think of lock ownership as the scope of the lock, and it can affect lock duration. There are three default values for the lock owner, and two additional types of lock ownership that must be explicitly requested. We can observe lock ownership values in the request_owner_type column of the sys.dm_tran_locks DMV. The default lock owner values are below.

TRANSACTION – Most of the locks discussed in this book are transaction-owned locks. Most of the locks that are involved in blocking and troubleshooting are transactionowned locks. The duration of transaction-owned locks is as discussed in the previous

section.

SHARED_TRANSACTION_WORKSPACE – Every connection in any database (other than master or tempdb) acquires a lock with this owner by. By observing these locks, SQL Server can tell when a database is in use. SHARED_TRANSACTION_WORKSPACE locks are held as long as a connection is using a database.

EXCLUSIVE_TRANSACTION_WORKSPACE – SQL Server acquires a lock with this owner whenever it needs exclusive access to the database. This includes activities such as dropping the database, restoring the database, or changing certain database properties, such as the READ_ONLY status. A connection cannot acquire a lock with an owner of EXCLUSIVE_TRANSACTION_WORKSPACE if any other connections have a lock owned by a SHARED_TRANSACTION_WORKSPACE and, in such cases, SQL Server generates an error message. SQL Server will hold a lock with this owner until the operation needing this lock (dropping, restoring or changing status) is completed.

The purpose of the SHARED_TRANSACTION_WORKSPACE lock owner is to prevent SQL Server from acquiring EXCLUSIVE_TRANSACTION_WORKSPACE locks, that is, to prevent a process from dropping, restoring, or changing readability status for a database, while the database is in use. The reason SQL Server does not acquire these locks for the master and tempdb databases is that these databases cannot be dropped, or have their readability status changed. In addition, we never restore tempdb, and to restore the master database, we must start the entire server in single-user mode so, again, SHARED_TRANSACTION_WORKSPACE locks are unnecessary.

The additional lock owner values are CURSOR and SESSION. We can request the former in a cursor declaration, but we will not discuss this topic further in this book. A SESSION lock is available only through user-defined locks, created with the sp_getapplock stored procedure, as will be discussed in Chapter 4.

Resource columns

Six of the columns in sys.dm_tran_locks have the resource_ prefix, and of these resource_type and resource_description are probably the most useful, providing the target resource for the requested lock (key, page, and so on) and the identity of the actual resource locked.

There is also a column called resource_database_id whose meaning should be obvious, so I won't include it in the subsequent discussion. I will mention, however, that this column returns a numerical value, and we have to translate that number to a database name, using the conversion: SELECT db_name(<resource_database_id>).

Table 2-1 shows many of the possible values for resource_type, as well as describing the information returned in the corresponding resource_description column.

DATABASE

None; the database is always indicated in the resource_database_ID column for every locked resource.

OBJECT

The object ID (which can be any database object, not necessarily a table) is reported in the resource_associated_entity_id column.

HOBT (a partition of a table or index)

None; the partition_id is reported in the resource_associated_entity_id column.

EXTENT File number:

page number of the first page of the extent.

PAGE File number:

page number of the actual table or index page.

KEY (a row of an index, either clustered or non-clustered):

A hashed value derived from all the key components and the locator. For a non-clustered index on a heap, where columns c1 and c2 are

indexed, the hash will contain contributions from c1, c2, and the RID.

RID (a row in a heap)

File number:pagenumber:slot number of the actual row.

APPLICATION

A concatenation of the database principal with access to this lock, the first 32 characters of the name given to the lock, and a hashed value

derived from the full name given to the lock.

Note that key locks and key-range locks both use KEY as the resource description because key range is considered a mode of locking, not a locking resource. However, in the output from the sys.dm_tran_locks view, we can distinguish between these types of locks by the value in the request_mode column.

For locked resources that are part of a larger entity, the resource_associated_ entity_id column in sys.dm_tran_locks displays the ID of that associated entity in the database.

The value in this column depends on the resource type:

ObjectID – The value given in this column for OBJECT resources

PartitionID – The value provided for resource types PAGE, KEY, RID, and HOBT (note that HOBT is just another way of referring to one partition of a table or index)

AllocationUnitID – Given for ALLOCATION_UNIT resources.

Of course, for some resources, such as DATABASE and EXTENT, there is no resource_associated_entity_id.

Request columns:

There are 13 columns in sys.dm_tran_locks used to identify information about the request for the lock, but two of them are documented as being for informational purposes only, not supported. Another two are only useful for DTC transactions or transactions using the MARS protocol, we won't discuss them further. Below is a list of the other nine with a basic explanation of their meaning.

request_mode – This is the lock mode discussed earlier, and indicates whether the granted or requested lock is shared (S), exclusive (X), intent shared (IX), update (U), and so on. Key-range locks, used for SERIALIZABLE isolation, appear as RangeS-U, RangeS-S and so on (see Chapter 3). For granted requests, this is the granted mode; for waiting requests, this is the mode being requested.

request_type – In SQL Server 2008, the only type of resource request tracked in sys.dm_tran_locks is for a LOCK. Future versions will include other types of resources that can be requested.

request_status – Status can be one of three values: GRANT, CONVERT, or WAIT. A status of CONVERT indicates that the requestor has already been granted a request for the same resource in a different mode and is currently waiting for an upgrade (convert) from the current lock mode to be granted. (For example, SQL Server can convert a U lock to X.) A status of WAIT indicates that the requestor does not currently hold a granted request on the resource.

request_session_id – This value is the ID of the session that has requested the lock. The owning session ID can change for distributed (DTC) and bound transactions.

request_reference_count – This value is a rough count of the number of times the same requestor has requested this resource, and applies only to resources that are not automatically released at the end of a transaction.

request_exec_context_id – This value is the execution context ID of the process that currently owns this request. A value greater than 0 indicates that this is a sub-thread used to execute a parallel query.

request_owner_type – This value refers to the owner discussed earlier, which indicates the scope of the lock. The five possible values are: TRANSACTION, SHARED_TRANSACTION_WORKSPACE, EXCLUSIVE_TRANSACTION_WORKSPACE, CURSOR and SESSION.

request_owner_id – This value is currently used only for requests with an owner of TRANSACTION, and the owner ID is the transaction ID. This column can be joined with the transaction_id column in the sys.dm_tran_active_transactions view.

lock_owner_address – This value is the memory address of the internal data structure that is used to track this request. This column can be joined with the resource_address column in sys.dm_os_waiting_tasks if this request is in the WAIT or CONVERT state.