Microsoft SQL Server 2008 Internals: Transactions and Concurrency
- 3/11/2009
Locking
Locking is a crucial function of any multiuser database system, including SQL Server. Locks are applied in both the pessimistic and optimistic concurrency models, although the way other processes deal with locked data is different in each. The reason I refer to the pessimistic variation of Read Committed isolation as Read Committed (locking) is because locking allows concurrent transactions to maintain consistency. In the pessimistic model, writers always block readers and writers, and readers can block writers. In the optimistic model, the only blocking that occurs is that writers block other writers. But to really understand what these simplified behavior summaries mean, we need to look at the details of SQL Server locking.
Locking Basics
SQL Server can lock data using several different modes. For example, read operations acquire shared locks, and write operations acquire exclusive locks. Update locks are acquired during the initial portion of an update operation, while SQL Server is searching for the data to update. SQL Server acquires and releases all these types of locks automatically. It also manages compatibility between lock modes, resolves deadlocks, and escalates locks if necessary. It controls locks on tables, on the pages of a table, on index keys, and on individual rows of data. Locks can also be held on system data—data that’s private to the database system, such as page headers and indexes.
SQL Server provides two separate locking systems. The first system affects all fully shared data and provides row locks, page locks, and table locks for tables, data pages, Large Object (LOB) pages, and leaf-level index pages. The second system is used internally for index concurrency control, controlling access to internal data structures and retrieving individual rows of data pages. This second system uses latches, which are less resource-intensive than locks and provide performance optimizations. You could use full-blown locks for all locking, but because of their complexity, they would slow down the system if you used them for all internal needs. If you examine locks using the sp_lock system stored procedure or a similar mechanism that gets information from the sys.dm_tran_locks view, you cannot see latches—you see only information about locks.
Another way to look at the difference between locks and latches is that locks ensure the logical consistency of the data and latches ensure the physical consistency. Latching happens when you place a row physically on a page or move data in other ways, such as compressing the space on a page. SQL Server must guarantee that this data movement can happen without interference.
Spinlocks
For shorter-term needs, SQL Server achieves mutual exclusion with a spinlock. Spinlocks are used purely for mutual exclusion and never to lock user data. They are even more lightweight than latches, which are lighter than the full locks used for data and index leaf pages. The requester of a spinlock repeats its request if the lock is not immediately available. (That is, the requester “spins” on the lock until it is free.)
Spinlocks are often used as mutexes within SQL Server for resources that are usually not busy. If a resource is busy, the duration of a spinlock is short enough that retrying is better than waiting and then being rescheduled by the operating system, which results in context switching between threads. The savings in context switches more than offsets the cost of spinning as long as you don’t have to spin too long. Spinlocks are used for situations in which the wait for a resource is expected to be brief (or if no wait is expected). The sys.dm_os_tasks dynamic management view (DMV) shows a status of SPINLOOP for any task that is currently using a spinlock.
Lock Types for User Data
We examine four aspects of locking user data. First we look at the mode of locking (the type of lock). I already mentioned shared, exclusive, and update locks, and I go into more detail about these modes as well as others. Next we look at the granularity of the lock, which specifies how much data is covered by a single lock. This can be a row, a page, an index key, a range of index keys, an extent, a partition, or an entire table. The third aspect of locking is the duration of the lock. As mentioned earlier, some locks are released as soon as the data has been accessed, and some locks are held until the transaction commits or rolls back. The fourth aspect of locking concerns the ownership of the lock (the scope of the lock). Locks can be owned by a session, a transaction, or a cursor.
Lock Modes
SQL Server uses several locking modes, including shared locks, exclusive locks, update locks, and intent locks, plus variations on these. It is the mode of the lock that determines whether a concurrently requested lock is compatible with locks that have already been granted. We see the lock compatibility matrix at the end of this section in Table 10-2.
Shared Locks
Shared locks are acquired automatically by SQL Server when data is read. Shared locks can be held on a table, a page, an index key, or an individual row. Many processes can hold shared locks on the same data, but no process can acquire an exclusive lock on data that has a shared lock on it (unless the process requesting the exclusive lock is the same process as the one holding the shared lock). Normally, shared locks are released as soon as the data has been read, but you can change this by using query hints or a different transaction isolation level.
Exclusive Locks
SQL Server automatically acquires exclusive locks on data when the data is modified by an INSERT, UPDATE, or DELETE operation. Only one process at a time can hold an exclusive lock on a particular data resource; in fact, as you see when we discuss lock compatibility later in this chapter, no locks of any kind can be acquired by a process if another process has the requested data resource exclusively locked. Exclusive locks are held until the end of the transaction. This means the changed data is normally not available to any other process until the current transaction commits or rolls back. Other processes can decide to read exclusively locked data by using query hints.
Update Locks
Update locks are really not a separate kind of lock; they are a hybrid of shared and exclusive locks. They are acquired when SQL Server executes a data modification operation but first, SQL Server needs to search the table to find the resource that needs to be modified. Using query hints, a process can specifically request update locks, and in that case, the update locks prevent the conversion deadlock situation presented in Figure 10-6 later in this chapter.
){kind=link}
Update locks provide compatibility with other current readers of data, allowing the process to later modify data with the assurance that the data hasn’t been changed since it was last read. An update lock is not sufficient to allow you to change the data—all modifications require that the data resource being modified have an exclusive lock. An update lock acts as a serialization gate to queue future requests for the exclusive lock. (Many processes can hold shared locks for a resource, but only one process can hold an update lock.) So long as a process holds an update lock on a resource, no other process can acquire an update lock or an exclusive lock for that resource; instead, another process requesting an update or exclusive lock for the same resource must wait. The process holding the update lock can convert it into an exclusive lock on that resource because the update lock prevents lock incompatibility with any other processes. You can think of update locks as “intent-to-update” locks, which is essentially the role they perform. Used alone, update locks are insufficient for updating data—an exclusive lock is still required for actual data modification. Serializing access for the exclusive lock lets you avoid conversion deadlocks. Update locks are held until the end of the transaction or until they are converted to an exclusive lock.
Don’t let the name fool you: update locks are not just for UPDATE operations. SQL Server uses update locks for any data modification operation that requires a search for the data prior to the actual modification. Such operations include qualified updates and deletes, as well as inserts into a table with a clustered index. In the latter case, SQL Server must first search the data (using the clustered index) to find the correct position at which to insert the new row. While SQL Server is only searching, it uses update locks to protect the data; only after it has found the correct location and begins inserting does it convert the update lock to an exclusive lock.
Intent Locks
Intent locks are not really a separate mode of locking; they are a qualifier to the modes previously discussed. In other words, you can have intent shared locks, intent exclusive locks, and even intent update locks. Because SQL Server can acquire locks at different levels of granularity, a mechanism is needed to indicate that a component of a resource is already locked. For example, if one process tries to lock a table, SQL Server needs a way to determine whether a row (or a page) of that table is already locked. Intent locks serve this purpose. We discuss them in more detail when we look at lock granularity.
Special Lock Modes
SQL Server offers three additional lock modes: schema stability locks, schema modification locks, and bulk update locks. When queries are compiled, schema stability locks prevent other processes from acquiring schema modification locks, which are taken when a table’s structure is being modified. A bulk update lock is acquired when the BULK INSERT command is executed or when the bcp utility is run to load data into a table. In addition, the bulk import operation must request this special lock by using the TABLOCK hint. Alternatively, the table owner can set the table option called table lock on bulk load to True, and then any bulk copy IN or BULK INSERT operation automatically requests a bulk update lock. Requesting this special bulk update table lock does not necessarily mean it is granted. If other processes already hold locks on the table, or if the table has any indexes, a bulk update lock cannot be granted. If multiple connections have requested and received a bulk update lock, they can perform parallel loads into the same table. Unlike exclusive locks, bulk update locks do not conflict with each other, so concurrent inserts by multiple connections is supported.
Conversion Locks
Conversion locks are never requested directly by SQL Server, but are the result of a conversion from one mode to another. The three types of conversion locks supported by SQL Server 2008 are SIX, SIU, and UIX. The most common of these is the SIX, which occurs if a transaction is holding a shared (S) lock on a resource and later an IX lock is needed. The lock mode is indicated as SIX. For example, suppose that you issue the following batch:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM bigtable
UPDATE bigtable
SET col = 0
WHERE keycolumn = 100
If the table is large, the SELECT statement acquires a shared table lock. (If the table has only a few rows, SQL Server acquires individual row or key locks.) The UPDATE statement then acquires a single exclusive key lock to perform the update of a single row, and the X lock at the key level means an IX lock at the page and table level. The table then shows SIX when viewed through sys.dm_tran_locks. Similarly, SIU occurs when a process has a shared lock on a table and an update lock on a row of that table, and UIX occurs when a process has an update lock on the table and an exclusive lock on a row.
Table 10-3 shows most of the lock modes, as well as the abbreviations used in sys.dm_tran_locks.
Table 10-3. SQL Server Lock Modes
Abbreviation |
Lock Mode |
Description |
S |
Shared |
Allows other processes to read but not change the locked resource. |
X |
Exclusive |
Prevents another process from modifying or reading data in the locked resource. |
U |
Update |
Prevents other processes from acquiring an update or exclusive lock; used when searching for the data to modify. |
IS |
Intent shared |
Indicates that a component of this resource is locked with a shared lock. This lock can be acquired only at the table or page level. |
IU |
Intent update |
Indicates that a component of this resource is locked with an update lock. This lock can be acquired only at the table or page level. |
IX |
Intent exclusive |
Indicates that a component of this resource is locked with an exclusive lock. This lock can be acquired only at the table or page level. |
SIX |
Shared with intent exclusive |
Indicates that a resource holding a shared lock also has a component (a page or row) locked with an exclusive lock. |
SIU |
Shared with intent update |
Indicates that a resource holding a shared lock also has a component (a page or row) locked with an update lock. |
UIX |
Update with intent exclusive |
Indicates that a resource holding an update lock also has a component (a page or row) locked with an exclusive lock. |
Sch-S |
Schema stability |
Indicates that a query using this table is being compiled. |
Sch-M |
Schema modification |
Indicates that the structure of the table is being changed. |
BU |
Bulk update |
Used when a bulk copy operation is copying data into a table and the TABLOCK hint is being applied (either manually or automatically). |
Key-Range Locks
Additional lock modes—called key-range locks—are taken only in the Serializable isolation level for locking ranges of data. Most lock modes can apply to almost any lock resource. For example, shared and exclusive locks can be taken on a table, a page, a row, or a key. Because key-range locks can be taken only on keys, I describe the details of key-range locks later in this chapter in the section on key locks.
Lock Granularity
SQL Server can lock user data resources (not system resources, which are protected with latches) at the table, page, or row level. (If locks are escalated, SQL Server can also lock a single partition of a table or index.) In addition, SQL Server can lock index keys and ranges of index keys. Figure 10-1 shows the basic lock levels in a table that can be acquired when a resource is first accessed. Keep in mind that if the table has a clustered index, the data rows are at the leaf level of the clustered index and they are locked with key locks instead of row locks.
){kind=link}
Figure 10-1. Levels of granularity for SQL Server locks on a table
The sys.dm_tran_locks view keeps track of each lock and contains information about the resource, which is locked (such as a row, key, or page), the mode of the lock, and an identifier for the specific resource. Keep in mind that sys.dm_tran_locks is only a dynamic view that is used to display the information about the locks that are held. The actual information is stored in internal SQL Server structures that are not visible to us at all. So when I talk about information being in the sys.dm_tran_locks view, I am referring to the fact that the information can be seen through that view.
When a process requests a lock, SQL Server compares the lock requested to the resources already listed in sys.dm_tran_locks and looks for an exact match on the resource type and identifier. However, if one process has a row exclusively locked in the Sales.SalesOrderHeader table, for example, another process might try to get a lock on the entire Sales.SalesOrderHeader table. Because these are two different resources, SQL Server does not find an exact match unless additional information is already in sys.dm_tran_locks. This is what intent locks are for. The process that has the exclusive lock on a row of the Sales.SalesOrderHeader table also has an intent exclusive lock on the page containing the row and another intent exclusive lock on the table containing the row. We can see those locks by first running this code:
USE Adventureworks2008; BEGIN TRAN UPDATE Sales.SalesOrderHeader SET ShipDate = ShipDate + 1 WHERE SalesOrderID = 43666;
This statement should affect a single row. Because I have started a transaction and not yet terminated it, the exclusive locks acquired are still held. I can look at those locks using the sys.dm_tran_locks view:
SELECT resource_type, resource_description,
resource_associated_entity_id, request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id > 0;
I give you more details about the data in the section entitled “sys.dm_tran_locks” later in this chapter, but for now, just note that the reason for the filter in the WHERE clause is that I am interested only in locks that are actually held on data resources. If you are running a query on a SQL Server instance that others are using, you might have to provide more filters to get just the rows you’re interested in. For example, you could include a filter on request_session_id to limit the output to locks held by a particular session. Your results should look something like this:
resource_type resource_description resource_associated_entity_id request_mode request_status ------------- -------------------- ---------------------------- ------------ --------------- KEY (92007ad11d1d) 72057594045857792 X GRANT PAGE 1:5280 72057594045857792 IX GRANT OBJECT 722101613 IX GRANT
Note that there are three locks, even though the UPDATE statement affected only a single row. For the KEY and the PAGE locks, the resource_associated_entity_id is a partition_id. For the OBJECT locks, the resource_associated_entity_id is a table. We can verify what table it is by using the following query:
SELECT object_name(722101613)
The results should tell us that the object is the Sales.SalesOrderHeader table. When the second process attempts to acquire an exclusive lock on that table, it finds a conflicting row already in sys.dm_tran_locks on the same lock resource (the Sales.SalesOrderHeader table), and it is blocked. The sys.dm_tran_locks view shows us the following row, indicating a request for an exclusive lock on an object that is unable to be granted. The process requesting the lock is in a WAIT state:
resource_type resource_description resource_associated_entity_id request_mode request_status ------------- -------------------- ------------------------------ ------------ ------------ OBJECT 722101613 X WAIT
Not all requests for locks on resources that are already locked result in a conflict. A conflict occurs when one process requests a lock on a resource that is already locked by another process in an incompatible lock mode. For example, two processes can each acquire shared locks on the same resource because shared locks are compatible with each other. I discuss lock compatibility in detail later in this chapter.
Key Locks
SQL Server 2008 supports two kinds of key locks, and which one it uses depends on the isolation level of the current transaction. If the isolation level is Read Committed, Repeatable Read, or Snapshot, SQL Server tries to lock the actual index keys accessed while processing the query. With a table that has a clustered index, the data rows are the leaf level of the index, and you see key locks acquired. If the table is a heap, you might see key locks for the nonclustered indexes and row locks for the actual data.
If the isolation level is Serializable, the situation is different. We want to prevent phantoms, so if we have scanned a range of data within a transaction, we need to lock enough of the table to make sure no one can insert a value into the range that was scanned. For example, we can issue the following query within an explicit transaction in the AdventureWorks2008 database:
BEGIN TRAN SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN 100 and 110;
When you use Serializable isolation, locks must be acquired to make sure no new rows with CustomerID values between 100 and 110 are inserted before the end of the transaction. Much older versions of SQL Server (prior to 7.0) guaranteed this by locking whole pages or even the entire table. In many cases, however, this was too restrictive—more data was locked than the actual WHERE clause indicated, resulting in unnecessary contention. SQL Server 2008 uses the key-range locks mode, which is associated with a particular key value in an index and indicates that all values between that key and the previous one in the index are locked.
The AdventureWorks2008 database includes an index on the Person table with the LastName column as the leading column. Assume that we are in TRANSACTION ISOLATION LEVEL SERIALIZABLE and we issue this SELECT statement inside a user-defined transaction:
SELECT * FROM Person.Person WHERE LastName BETWEEN 'Freller' AND 'Freund';
If Fredericksen, French, and Friedland are sequential leaf-level index keys in an index on the LastName column, the second two of these keys (French and Friedland) acquire key-range locks (although only one row, for French, is returned in the result set). The key-range locks prevent any inserts into the ranges ending with the two key-range locks. No values greater than Fredericksen and less than or equal to French can be inserted, and no values greater than French and less than or equal to Friedland can be inserted. Note that the key-range locks imply an open interval starting at the previous sequential key and a closed interval ending at the key on which the lock is placed. These two key-range locks prevent anyone from inserting either Fremlich or Frenkin, which are in the range specified in the WHERE clause. However, the key-range locks would also prevent anyone from inserting Freedman (which is greater than Fredericksen and less than French), even though Freedman is not in the query’s specified range. Key-range locks are not perfect, but they do provide much greater concurrency than locking whole pages or tables, while guaranteeing that phantoms are prevented.
There are nine types of key-range locks, and each has a two-part name: the first part indicates the type of lock on the range of data between adjacent index keys, and the second part indicates the type of lock on the key itself. These nine types of key-range locks are described in Table 10-4.
Table 10-4. Types of Key-Range Locks
Abbreviation |
Description |
RangeS-S |
Shared lock on the range between keys; shared lock on the key at the end of the range |
RangeS-U |
Shared lock on the range between keys; update lock on the key at the end of the range |
RangeIn-Null |
Exclusive lock to prevent inserts on the range between keys; no lock on the keys themselves |
RangeX-X |
Exclusive lock on the range between keys; exclusive lock on the key at the end of the range |
RangeIn-S |
Conversion lock created by S and RangeIn_Null lock |
RangeIn-U |
Conversion lock created by U and RangeIn_Null lock |
RangeIn-X |
Conversion of X and RangeIn_Null lock |
RangeX-S |
Conversion of RangeIn_Null and RangeS_S lock |
RangeX-U |
Conversion of RangeIn_Null and RangeS_U lock |
Many of these lock modes are very rare or transient, so you do not see them very often in sys.dm_tran_locks. For example, the RangeIn-Null lock is acquired when SQL Server attempts to insert into the range between keys in a session using Serializable isolation. This type of lock is not often seen because it is typically very transient. It is held only until the correct location for insertion is found, and then the lock is converted into an X lock. However, if one transaction scans a range of data using the Serializable isolation level and then another transaction tries to insert into that range, the second transaction has a lock request with a WAIT status with the RangeIn-Null mode. You can observe this by looking at the status column in sys.dm_tran_locks, which we discuss in more detail later in the chapter.
Additional Lock Resources
In addition to locks on objects, pages, keys, and rows, a few other resources can be locked by SQL Server. Locks can be taken on extents—units of disk space that are 64 KB in size (eight pages of 8 KB each). This kind of locking occurs automatically when a table or an index needs to grow and a new extent must be allocated. You can think of an extent lock as another type of special-purpose latch, but it does show up in sys.dm_tran_locks. Extents can have both shared extent and exclusive extent locks.
When you examine the contents of sys.dm_tran_locks, you should notice that most processes hold a lock on at least one database (resource_type = DATABASE). In fact, any process holding locks in any database other than master or tempdb has a lock for that database resource. These database locks are always shared locks if the process is just using the database. SQL Server checks for these database locks when determining whether a database is in use, and then it can determine whether the database can be dropped, restored, altered, or closed. Because few changes can be made to master and tempdb and they cannot be dropped or closed, DATABASE locks are unnecessary. In addition, tempdb is never restored, and to restore the master database, the entire server must be started in single-user mode, so again, DATABASE locks are unnecessary. When attempting to perform one of these operations, SQL Server requests an exclusive database lock, and if any other processes have a shared lock on the database, the request blocks. Generally, you don’t need to be concerned with extent or database locks, but you see them if you are perusing sys.dm_tran_locks.
You might occasionally see locks on ALLOCATION_UNIT resources. Although all table and index structures contain one or more ALLOCATION_UNITs, when these locks occur, it means SQL Server is dealing with one of these resources that is no longer tied to a particular object. For example, when you drop or rebuild large tables or indexes, the actual page deallocation is deferred until after the transaction commits. Deferred drop operations do not release allocated space immediately, and they introduce additional overhead costs, so a deferred drop is done only on tables or indexes that use more than 128 extents. If the table or index uses 128 or fewer extents, dropping, truncating, and rebuilding are not deferred operations. During the first phase of a deferred operation, the existing allocation units used by the table or index are marked for deallocation and locked until the transaction commits. This is where you see ALLOCATION_UNIT locks in sys.dm_tran_locks. You can also look in the sys.allocation_units view to find allocation units with a type_desc value of DROPPED to see how much space is being used by the allocation units that are not available for reuse but are not currently part of any object. The actual physical dropping of the allocation unit’s space occurs after the transaction commits.
Finally, you occasionally have locks on individual partitions, which are indicated in the lock metadata as HOBT locks. This can happen only when locks are escalated, and only if you have specified that escalation to the partition level is allowed (and, of course, only when the table or index has been partitioned). We look at how you can specify that you want partition-level locking in the section entitled “Lock Escalation,” later in this chapter.
Identifying Lock Resources
When SQL Server tries to determine whether a requested lock can be granted, it checks the sys.dm_tran_locks view to determine whether a matching lock with a conflicting lock mode already exists. It compares locks by looking at the database ID (resource_database_ID), the values in the resource_description and resource_associated_entity_id columns, and the type of resource locked. SQL Server knows nothing about the meaning of the resource description. It simply compares the strings identifying the lock resources to look for a match. If it finds a match with a request_status value of GRANT, it knows the resource is already locked; it then uses the lock compatibility matrix to determine whether the current lock is compatible with the one being requested. Table 10-5 shows many of the possible lock resources that are displayed in the first column of the sys.dm_tran_locks view and the information in the resource_description column, which is used to define the actual resource locked.
Table 10-5. Lockable Resources in SQL Server
Resource_Type |
Resource_Description |
Example |
DATABASE |
None; the database is always indicated in the resource_database_ID column for every locked resource. |
12 |
OBJECT |
The object ID (which can be any database object, not necessarily a table) is reported in the resource_associated_entity_id column. |
69575286 |
HOBT |
hobt_id is reported in the resource_associated_entity_id column. Used only when partition locking has been enabled for a table. |
72057594038779904 |
EXTENT |
File number:page number of the first page of the extent. |
1:96 |
PAGE |
File number:page number of the actual table or index page. |
1:104 |
KEY |
A hashed value derived from all the key components and the locator. For a nonclustered index on a heap, where columns c1 and c2 are indexed, the hash will contain contributions from c1, c2, and the RID. |
ac0001a10a00 |
ROW |
File number:page number:slot number of the actual row. |
1:161:3 |
Note that key locks and key-range locks have identical resource descriptions because key range is considered a mode of locking, not a locking resource. When you look at output from the sys.dm_tran_locks view, you see that you can distinguish between these types of locks by the value in the lock mode column.
Another type of lockable resource is METADATA. More than any other resource, METADATA resources are divided into multiple subtypes, which are described in the resource_subtype column of sys.dm_tran_locks. You might see dozens of subtypes of METADATA resources, but most of them are beyond the scope of this book. For some, however, even though SQL Server Books Online describes them as “for internal use only,” it is pretty obvious what they refer to. For example, when you change properties of a database, you can see a resource_type of METADATA and a resource_subtype of DATABASE. The value in the resource_description column of that row is database_id =<ID>, indicating the ID of the database whose metadata is currently locked.
Associated Entity ID
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. This can be an object ID, a partition ID, or an allocation unit ID, depending on the resource type. Of course, for some resources, such as DATABASE and EXTENT, there is no resource_associated_entity_id. An object ID value is given in this column for OBJECT resources, and an allocation unit ID is given for ALLOCATION_UNIT resources. A partition ID is provided for resource types PAGE, KEY, and RID.
There is no simple function to convert a partition ID value to an object name; you have to actually select from the sys.partitions view. The following query translates all the resource_associated_entity_id values for locks in the current database by joining sys.dm_tran_locks to sys.partitions. For OBJECT resources, the object_name function is applied to the resource_associated_entity_id column. For PAGE, KEY, and RID resources, I use the object_name function with the object_id value from the sys.partitions view. For other resources for which there is no resource_associated_entity_id, the code just returns n/a. Because the code references the sys.partitions view, which occurs in each database, this code is filtered to return only lock information for resources in the current database. The output is organized to reflect the information returned by the sp_lock procedure, but you can add any additional filters or columns that you need. I will use this query in many examples later in this chapter, so I create a VIEW based on the SELECT and call it DBlocks:
CREATE VIEW DBlocks AS
SELECT request_session_id as spid,
db_name(resource_database_id) as dbname,
CASE
WHEN resource_type = 'OBJECT' THEN
object_name(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN 'n/a'
ELSE object_name(p.object_id)
END as entity_name, index_id,
resource_type as resource,
resource_description as description,
request_mode as mode, request_status as status
FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p
ON p.partition_id = t.resource_associated_entity_id
WHERE resource_database_id = db_id();
Lock Duration
The length of time that a lock is held depends primarily on the mode of the lock and the transaction isolation level in effect. The default isolation level for SQL Server is Read Committed. At this level, shared locks are released as soon as SQL Server has read and processed the locked data. In Snapshot isolation, the behavior is the same—shared locks are released as soon as SQL Server has read the data. If your transaction isolation level is Repeatable Read or Serializable, shared locks have the same duration as exclusive locks; that is, they are not released until the transaction is over. In any isolation level, an exclusive lock is held until the end of the transaction, whether the transaction is committed or rolled back. An update lock is also held until the end of the transaction unless it has been promoted to an exclusive lock, in which case the exclusive lock, as is always the case with exclusive locks, remains for the duration of the transaction.
In addition to changing your transaction isolation level, you can control the lock duration by using query hints. I discuss query hints for locking, briefly, later in this chapter.
Lock Ownership
Lock duration is also directly affected by the lock ownership. Lock ownership has nothing to do with the process that requested the lock, but you can think of it as the “scope” of the lock. There are four types of lock owners, or lock scopes: transactions, cursors, transaction_workspaces, and sessions. The lock owner can be viewed through the request_owner_type column in the sys.dm_tran_locks view.
Most of our locking discussion deals with locks with a lock owner of TRANSACTION. As we’ve seen, these locks can have two different durations depending on the isolation level and lock mode. The duration of shared locks in Read Committed isolation is only as long as the locked data is being read. The duration of all other locks owned by a transaction is until the end of the transaction.
A lock with a request_ownertype value of CURSOR must be requested explicitly when the cursor is declared. If a cursor is opened using a locking mode of SCROLL_LOCKS, a cursor lock is held on every row fetched until the next row is fetched or the cursor is closed. Even if the transaction commits before the next fetch, the cursor lock is not released.
In SQL Server 2008, locks owned by a session must also be requested explicitly and apply only to APPLICATION locks. A session lock is requested using the sp_getapplock procedure. Its duration is until the session disconnects or the lock is released explicitly.
Transaction_workspace locks are acquired every time a database is accessed, and the resource associated with these locks is always a database. A workspace holds database locks for sessions that are enlisted into a common environment. Usually, there is one workspace per session, so all DATABASE locks acquired in the session are kept in the same workspace object. In the case of distributed transactions, multiple sessions are enlisted into the same workspace, so they share the database locks.
Every process acquires a DATABASE lock with an owner of SHARED_TRANSACTION_WORKSPACE on any database when the process issues the USE command. The exception is any processes that use master or tempdb, in which case no DATABASE lock is taken. That lock isn’t released until another USE command is issued or until the process is disconnected. If a process attempts to ALTER, RESTORE, or DROP the database, the DATABASE lock acquired has an owner of EXCLUSIVE_TRANSACTION_WORKSPACE. SHARED_TRANSACTION_WORKSPACE and EXCLUSIVE_TRANSACTION_WORKSPACE locks are maintained by the same workspace and are just two different lists in one workspace. The use of two different owner names is misleading in this case.
Viewing Locks
To see the locks currently outstanding in the system, as well as those that are being waited for, the best source of information is the sys.dm_tran_locks view. I’ve shown you some queries from this view in previous sections, and in this section, I show you a few more and explain what more of the output columns mean. This view replaces the sp_lock procedure. Although calling a procedure might require less typing than querying the sys.dm_tran_locks view, the view is much more flexible. Not only are there many more columns of information providing details about your locks, but as a view, sys.dm_tran_locks can be queried to select just the columns you want, or only the rows that meet your criteria. It can be joined with other views and aggregated to get summary information about how many locks of each kind are being held.
sys.dm_tran_locks
All the columns (with the exception of the last column called lock_owner_address) in sys.dm_tran_locks start with one of two prefixes. The columns whose names begin with resource_ describe the resource on which the lock request is being made. The columns whose names begin with request_ describe the process requesting the lock. Two requests operate on the same resource only if all the resource_ columns are the same.
resource_ Columns
I’ve mentioned most of the resource_ columns already, but I referred only briefly to the resource_subtype column. Not all resources have subtypes, and some have many. The METADATA resource type, for example, has over 40 subtypes.
Table 10-6 lists all the subtypes for resource types other than METADATA.
Table 10-6. Subtype Resources
Resource Type |
Resource Subtypes |
Description |
DATABASE |
BULKOP_BACKUP_DB |
Used for synchronization of database backups with bulk operations |
BULKOP_BACKUP_LOG |
Used for synchronization of database log backups with bulk operations |
|
DDL |
Used to synchronize Data Definition Language (DDL) operations with File Group operations (such as DROP) |
|
STARTUP |
Used for database startup synchronization |
|
TABLE |
UPDSTATS |
Used for synchronization of statistics updates on a table |
COMPILE |
Used for synchronization of stored procedure compiles |
|
INDEX_OPERATION |
Used for synchronization of index operations |
|
HOBT |
INDEX_REORGANIZE |
Used for synchronization of heap or index reorganization operations |
BULK_OPERATION |
Used for heap-optimized bulk load operations with concurrent scan, in the Snapshot, Read Uncommitted, and Read Committed SI levels |
|
ALLOCATION_UNIT |
PAGE_COUNT |
Used for synchronization of allocation unit page count statistics during deferred drop operations |
As previously mentioned, most METADATA subtypes are documented as being for INTERNAL USE ONLY, but their meaning is often pretty obvious. Each type of metadata can be locked separately as changes are made. Here is a partial list of the METADATA subtypes:
INDEXSTATS
STATS
SCHEMA
DATABASE_PRINCIPAL
DB_PRINCIPAL_SID
USER_TYPE
DATA_SPACE
PARTITION_FUNCTION
DATABASE
SERVER_PRINCIPAL
SERVER
Most of the other METADATA subtypes not listed here refer to elements of SQL Server 2008 that are not discussed in this book, including CLR routines, XML, certificates, full-text search, and notification services.
request_ Columns
I’ve also mentioned a couple of the most important request_ columns in sys.dm_tran_locks, including request_mode (the type of lock requested), request_owner_type (the scope of the lock requested), and request_session_id. Here are some of the others:
request_type. In SQL Server 2008, the only type of resource request tracked in sys.dm_tran_locks is for a LOCK. Future versions may 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_reference_count. This value is a rough count of 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. A granted resource is no longer considered to be held by a requestor if this field decreases to 0 and request_lifetime is also 0.
request_lifetime. This value is a code that indicates when the lock on the resource is released.
request_session_id. This value is the ID of the session that has requested the lock. The owning session ID can change for distributed and bound transactions. A value of –2 indicates that the request belongs to an orphaned DTC transaction. A value of –3 indicates that the request belongs to a deferred recovery transaction. (These are transactions whose rollback has been deferred at recovery because the rollback could not be completed successfully.)
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 subthread used to execute a parallel query.
request_request_id. This value is the request ID (batch ID) of the process that currently owns this request. This column is populated only for the requests coming in from a client application using Multiple Active Result Sets (MARS).
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.
request_owner_guid. This value is currently used only by DTC transactions when it corresponds to the DTC GUID for that transaction.
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.
Locking Examples
The following examples show what many of the lock types and modes discussed earlier look like when reported using the DBlocks view that I described previously.
Example 1. SELECT with Default Isolation Level
SQL BATCH
USE Adventureworks2008; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRAN SELECT * FROM Production.Product WHERE Name = 'Reflector'; SELECT * FROM DBlocks WHERE spid = @@spid; COMMIT TRAN
RESULTS FROM DBlocks
spid dbname entity_name index_id resource description mode status ----- ----------------- ------------ -------- --------- ----------- ----- ------ 60 Adventureworks2008 n/a NULL DATABASE S GRANT 60 AdventureWorks2008 DBlocks NULL OBJECT IS GRANT
There are no locks on the data in the Production.Product table because the batch was performing only SELECT operations that acquired shared locks. By default, the shared locks are released as soon as the data has been read, so by the time the SELECT from the view is executed, the locks are no longer held. There is only the ever-present DATABASE lock, and an OBJECT lock on the view.
Example 2. SELECT with Repeatable Read Isolation Level
SQL BATCH
USE AdventureWorks2008; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN TRAN SELECT * FROM Production.Product WHERE Name LIKE 'Racing Socks%'; SELECT * FROM DBlocks WHERE spid = @@spid AND entity_name = 'Product'; COMMIT TRAN
RESULTS FROM DBlocks
spid dbname entity_name index_id resource description mode status ---- ------------------- ------------ --------- ---------- -------------- -------- ------ 54 AdventureWorks2008 Product NULL OBJECT IS GRANT 54 AdventureWorks2008 Product 1 PAGE 1:16897 IS GRANT 54 AdventureWorks2008 Product 1 KEY (6b00b8eeda30) S GRANT 54 AdventureWorks2008 Product 1 KEY (6a00dd896688) S GRANT 54 AdventureWorks2008 Product 3 KEY (9502d56a217e) S GRANT 54 AdventureWorks2008 Product 3 PAGE 1:1767 IS GRANT 54 AdventureWorks2008 Product 3 KEY (9602945b3a67) S GRANT
This time, I filtered out the database lock and the locks on the view and the rowset, just to keep the focus on the data locks. Because the Production.Product table has a clustered index, the rows of data are all index rows in the leaf level. The locks on the two individual data rows returned are listed as key locks. There are also two key locks at the leaf level of the nonclustered index on the table used to find the relevant rows. In the Production.Product table, that nonclustered index is on the Name column. You can tell the clustered and nonclustered indexes apart by the value in the index_id column: the data rows (the leaf rows of the clustered index) have an index_id value of 1, and the nonclustered index rows have an index_id value of 3. (For nonclustered indexes, the index_id value can be anything between 2 and 250 or between 356 and 1005.) Because the transaction isolation level is Repeatable Read, the shared locks are held until the transaction is finished. Note that the index rows have shared (S) locks, and the data and index pages, as well as the table itself, have intent shared (IS) locks.
Example 3. SELECT with Serializable Isolation Level
SQL BATCH
USE AdventureWorks2008 ; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRAN SELECT * FROM Production.Product WHERE Name LIKE 'Racing Socks%'; SELECT * FROM DBlocks WHERE spid = @@spid AND entity_name = 'Product'; COMMIT TRAN
RESULTS FROM DBlocks
spid dbname entity_name index_id resource description mode status ---- ------------------ ------------ ---------- ---------- ------------ ------- ------ 54 AdventureWorks2008 Product NULL OBJECT IS GRANT 54 AdventureWorks2008 Product 1 PAGE 1:16897 IS GRANT 54 AdventureWorks2008 Product 1 KEY (6b00b8eeda30) S GRANT 54 AdventureWorks2008 Product 1 KEY (6a00dd896688) S GRANT 54 AdventureWorks2008 Product 3 KEY (9502d56a217e) RangeS-S GRANT 54 AdventureWorks2008 Product 3 PAGE 1:1767 IS GRANT 54 AdventureWorks2008 Product 3 KEY (23027a50f6db) RangeS-S GRANT 54 AdventureWorks2008 Product 3 KEY (9602945b3a67) RangeS-S GRANT
The locks held with the Serializable isolation level are almost identical to those held with the Repeatable Read isolation level. The main difference is in the mode of the lock. The two-part mode RangeS-S indicates a key-range lock in addition to the lock on the key itself. The first part (RangeS) is the lock on the range of keys between (and including) the key holding the lock and the previous key in the index. The key-range locks prevent other transactions from inserting new rows into the table that meet the condition of this query; that is, no new rows with a product name starting with Racing Socks can be inserted. The key-range locks are held on ranges in the nonclustered index on Name (index_id = 3) because that is the index used to find the qualifying rows. There are three key locks in the nonclustered index because three different ranges need to be locked. The two Racing Socks rows are Racing Socks, L and Racing Socks, M. SQL Server must lock the range from the key preceding the first Racing Socks row in the index up to the first Racing Socks. It must lock the range between the two rows starting with Racing Socks, and it must lock the range from the second Racing Socks to the next key in the index. (So actually nothing could be inserted between Racing Socks and the previous key, Pinch Bolt, or between Racing Socks and the next key, Rear Brakes. For example, we could not insert a product with the name Portkey or Racing Tights.)
Example 4. Update Operations
SQL BATCH
USE AdventureWorks2008; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRAN UPDATE Production.Product SET ListPrice = ListPrice * 0.6 WHERE Name LIKE 'Racing Socks%'; SELECT * FROM DBlocks WHERE spid = @@spid AND entity_name = 'Product'; COMMIT TRAN
RESULTS FROM DBlocks
spid dbname entity_name index_id resource description mode status ---- ------------------- ----------- ---------- ---------- -------------- ----- ------- 54 AdventureWorks2008 Product NULL OBJECT IX GRANT 54 AdventureWorks2008 Product 1 PAGE 1:16897 IX GRANT 54 AdventureWorks2008 Product 1 KEY (6b00b8eeda30) X GRANT 54 AdventureWorks2008 Product 1 KEY (6a00dd8966 88) X GRANT
The two rows in the leaf level of the clustered index are locked with X locks. The page and the table are then locked with IX locks. I mentioned earlier that SQL Server actually acquires update locks while it looks for the rows to update. However, these are converted to X locks when the actual update is performed, and by the time we look at the DBLocks view, the update locks are gone. Unless you actually force update locks with a query hint, you might never see them in the lock report from DBLocks or by direct inspection of sys.dm_tran_locks.
Example 5. Update with Serializable Isolation Level Using an Index
SQL BATCH
USE AdventureWorks2008; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRAN UPDATE Production.Product SET ListPrice = ListPrice * 0.6 WHERE Name LIKE 'Racing Socks%'; SELECT * FROM DBlocks WHERE spid = @@spid AND entity_name = 'Product'; COMMIT TRAN
RESULTS FROM DBlocks
spid dbname entity_name index_id resource description mode status ---- ------------------- ------------ ----------- ---------- --------------- -------- ------ 54 AdventureWorks2008 Product NULL OBJECT IX GRANT 54 AdventureWorks2008 Product 1 PAGE 1:16897 IX GRANT 54 AdventureWorks2008 Product 1 KEY (6a00dd896688) X GRANT 54 AdventureWorks2008 Product 1 KEY (6b00b8eeda30) X GRANT 54 AdventureWorks2008 Product 3 KEY (9502d56a217e) RangeS-U GRANT 54 AdventureWorks2008 Product 3 PAGE 1:1767 IU GRANT 54 AdventureWorks2008 Product 3 KEY (23027a50f6db) RangeS-U GRANT 54 AdventureWorks2008 Product 3 KEY (9602945b3a67) RangeS-U GRANT
Again, notice that the key-range locks are on the nonclustered index used to find the relevant rows. The range interval itself needs only a shared lock to prevent insertions, but the searched keys have U locks so no other process can attempt to update them. The keys in the table itself (index_id = 1) obtain the exclusive lock when the actual modification is made.
Now let’s look at an UPDATE operation with the same isolation level when no index can be used for the search.
Example 6. Update with Serializable Isolation Not Using an Index
SQL BATCH
USE AdventureWorks2008; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRAN UPDATE Production.Product SET ListPrice = ListPrice * 0.6 WHERE Color = 'White'; SELECT * FROM DBlocks WHERE spid = @@spid AND entity_name = 'Product'; COMMIT TRAN
RESULTS FROM DBlocks (Abbreviated)
spid dbname entity_name index_id resource description mode status ---- ------------------- ------------ ----------- ---------- -------------- -------- ------- 54 AdventureWorks2008 Product NULL OBJECT IX GRANT 54 AdventureWorks2008 Product 1 KEY (7900ac71caca) RangeS-U GRANT 54 AdventureWorks2008 Product 1 KEY (6100dc0e675f) RangeS-U GRANT 54 AdventureWorks2008 Product 1 KEY (5700a1a9278a) RangeS-U GRANT 54 AdventureWorks2008 Product 1 PAGE 1:16898 IU GRANT 54 AdventureWorks2008 Product 1 PAGE 1:16899 IU GRANT 54 AdventureWorks2008 Product 1 PAGE 1:16896 IU GRANT 54 AdventureWorks2008 Product 1 PAGE 1:16897 IX GRANT 54 AdventureWorks2008 Product 1 PAGE 1:16900 IU GRANT 54 AdventureWorks2008 Product 1 PAGE 1:16901 IU GRANT 54 AdventureWorks2008 Product 1 KEY (5600c4ce9b32) RangeS-U GRANT 54 AdventureWorks2008 Product 1 KEY (7300c89177a5) RangeS-U GRANT 54 AdventureWorks2008 Product 1 KEY (7f00702ea1ef) RangeS-U GRANT 54 AdventureWorks2008 Product 1 KEY (6b00b8eeda30) RangeX-X GRANT 54 AdventureWorks2008 Product 1 KEY (c500b9eaac9c) RangeX-X GRANT 54 AdventureWorks2008 Product 1 KEY (c6005745198e) RangeX-X GRANT 54 AdventureWorks2008 Product 1 KEY (6a00dd896688) RangeX-X GRANT
The locks here are similar to those in the previous example except that all the locks are on the table itself (index_id = 1). A clustered index scan (on the entire table) had to be done, so all keys initially received the RangeS-U lock, and when four rows were eventually modified, the locks on those keys were converted to RangeX-X locks. You can see all the RangeX-X locks, but not all the RangeS-U locks are shown for space reasons (the table has 504 rows).
Example 7. Creating a Table
SQL BATCH
USE AdventureWorks2008; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRAN SELECT * INTO newProducts FROM Production.Product WHERE ListPrice between 1 and 10; SELECT * FROM DBlocks WHERE spid = @@spid; COMMIT TRAN
RESULTS FROM DBlocks (Abbreviated)
spid dbname entity_name index_id resource description mode status ---- ------------------ ------------ ----------- ---------- ------------------ ------ ------ 54 AdventureWorks2008 n/a NULL DATABASE NULL GRANT 54 AdventureWorks2008 n/a NULL DATABASE NULL GRANT 54 AdventureWorks2008 n/a NULL DATABASE S GRANT 54 AdventureWorks2008 n/a NULL METADATA user_type_id = 258 Sch-S GRANT 54 AdventureWorks2008 n/a NULL METADATA data_space_id = 1 Sch-S GRANT 54 AdventureWorks2008 n/a NULL DATABASE S GRANT 54 AdventureWorks2008 n/a NULL METADATA $seq_type = 0, objec Sch-M GRANT 54 AdventureWorks2008 n/a NULL METADATA user_type_id = 260 Sch-S GRANT 54 AdventureWorks2008 sysrowsetcol NULL OBJECT IX GRANT 54 AdventureWorks2008 sysrowsets NULL OBJECT IX GRANT 54 AdventureWorks2008 sysallocunit NULL OBJECT IX GRANT 54 AdventureWorks2008 syshobtcolum NULL OBJECT IX GRANT 54 AdventureWorks2008 syshobts NULL OBJECT IX GRANT 54 AdventureWorks2008 sysserefs NULL OBJECT IX GRANT 54 AdventureWorks2008 sysschobjs NULL OBJECT IX GRANT 54 AdventureWorks2008 syscolpars NULL OBJECT IX GRANT 54 AdventureWorks2008 sysidxstats NULL OBJECT IX GRANT 54 AdventureWorks2008 sysrowsetcol 1 KEY (15004f6b3486) X GRANT 54 AdventureWorks2008 sysrowsetcol 1 KEY (0a00862c4e8e) X GRANT 54 AdventureWorks2008 sysrowsets 1 KEY (000000aaec7b) X GRANT 54 AdventureWorks2008 sysallocunit 1 KEY (00001f2dcf47) X GRANT 54 AdventureWorks2008 syshobtcolum 1 KEY (1900f7d4e2cc) X GRANT 54 AdventureWorks2008 syshobts 1 KEY (000000aaec7b) X GRANT 54 AdventureWorks2008 NULL NULL RID 1:6707:1 X GRANT 54 AdventureWorks2008 DBlocks NULL OBJECT IS GRANT 54 AdventureWorks2008 newProducts NULL OBJECT Sch-M GRANT 54 AdventureWorks2008 sysserefs 1 KEY (010025fabf73) X GRANT 54 AdventureWorks2008 sysschobjs 1 KEY (3b0042322c99) X GRANT 54 AdventureWorks2008 syscolpars 1 KEY (4200c1eb801c) X GRANT 54 AdventureWorks2008 syscolpars 1 KEY (4e00092bfbc3) X GRANT 54 AdventureWorks2008 sysidxstats 1 KEY (3b0006e110a6) X GRANT 54 AdventureWorks2008 sysschobjs 2 KEY (9202706f3e6c) X GRANT 54 AdventureWorks2008 syscolpars 2 KEY (6c0151be80af) X GRANT 54 AdventureWorks2008 syscolpars 2 KEY (2c03557a0b9d) X GRANT 54 AdventureWorks2008 sysidxstats 2 KEY (3c00f3332a43) X GRANT 54 AdventureWorks2008 sysschobjs 3 KEY (9202d42ddd4d) X GRANT 54 AdventureWorks2008 sysschobjs 4 KEY (3c0040d00163) X GRANT 54 AdventureWorks2008 newProducts 0 PAGE 1:6707 X GRANT 54 AdventureWorks2008 newProducts 0 HOBT Sch-M GRANT
Very few of these locks are actually acquired on elements of the newProducts table. In the entity_name column, you can see that most of the objects are undocumented, and normally invisible, system table names. As the new table is created, SQL Server acquires locks on nine different system tables to record information about this new table. In addition, notice the schema modification (Sch-M) lock and other metadata locks on the new table.
The final example looks at the locks held when there is no clustered index on the table and the data rows are being updated.
Example 8. Row Locks
SQL BATCH
USE AdventureWorks2008; SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE newProducts SET ListPrice = 5.99 WHERE name = 'Road Bottle Cage'; SELECT * FROM DBlocks WHERE spid = @@spid AND entity_name = 'newProducts'; COMMIT TRAN
RESULTS FROM DBlocks
spid dbname entity_name index_id resource description mode status ---- ------------------- ------------ ----------- ---------- ------------ -------- -------- 54 AdventureWorks2008 newProducts NULL OBJECT IX GRANT 54 AdventureWorks2008 newProducts 0 PAGE 1:6708 IX GRANT 54 AdventureWorks2008 newProducts 0 RID 1:6708:5 X GRANT
There are no indexes on the newProducts table, so the lock on the actual row meeting our criteria is an exclusive (X) lock on the row (RID). For RID locks, the description actually reports the specific row in the form File number:Page number:Slot number. As expected, IX locks are taken on the page and the table.