Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)
| Locking is a crucial function of any multi-user 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 will always block readers and writers, and readers can block writers. In the optimistic model, the only blocking that occurs is that writers will 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 datadata 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, 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 latchesyou 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 DMV sys.dm_os_tasks shows a status of SPINLOOP for any task that is currently using a spinlock. Lock Types for User Data
We'll examine four aspects of locking user data. First we'll look at the mode of locking (the type of lock). I already mentioned shared, exclusive, and update locks, and I'll go into more detail about these modes as well as others. Next we'll 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, 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. We'll see a chart illustrating the lock compatibility matrix at the end of this section. 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'll see when we discuss lock compatibility, 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 needs to search the table to find the resource that will 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 8-6 later in this chapter. 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 dataall 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.) As 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 dataan 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 escalate 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'll 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 can set the table option called table lock on bulk load to true, and then any bulk copy IN or BULK INSERT operation will automatically request a bulk update lock. Requesting this special bulk update table lock does not necessarily mean it will be 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 2005 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 are operating at the Repeatable Read transaction isolation level and 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 will acquire a shared table lock. (If the table has only a few rows, SQL Server will acquire individual row or key locks.) The UPDATE statement will then acquire a single exclusive key lock to perform the update of a single row, and the X lock at the key level will mean an IX lock at the page and table level. The table will then show SIX when viewed through sys.dm_tran_locks. Similarly, SIU will occur when a process has a shared lock on a table and an update lock on a row of that table, and UIX will occur when a process has an update lock on the table and an exclusive lock on a row. Table 8-3 shows most of the lock modes, as well as the abbreviations used in sys.dm_tran_locks.
Key-Range Locks
Additional lock modescalled key-range locksare 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 only be taken on keys, I'll describe the details of key-range locks in the upcoming 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. It also locks index keys and ranges of index keys. Figure 8-1 shows the possible lock levels in a table. 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. Figure 8-1. Levels of granularity for SQL Server locks on a table
The sys.dm_tran_locks view keeps track of each lock and contains the resource 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 AdventureWorks; 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, any 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'll give you more details about the data in the sys.dm_tran_locks view later in this chapter, but for now, I'll point out 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: [View full width] resource_type resource_description resource_associated_entity_id request_mode
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 an allocation_unit_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 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 will be blocked. The sys.dm_tran_locks view would show 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 will 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'll discuss lock compatibility in detail later in this chapter. Key Locks
SQL Server 2005 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 will see key locks acquired. If the table is a heap, you might see key locks for the non-clustered 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 AdventureWorks database: BEGIN TRAN SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEN 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 restrictivemore data was locked than the actual WHERE clause indicated, resulting in unnecessary contention. SQL Server 2005 uses a separate lock mode, called key-range locks, 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 AdventureWorks database includes an index on the LastName column in the Person.Contact table. Assume we are in TRANSACTION ISOLATION LEVEL SERIALIZABLE and we issue this SELECT statement: SELECT * FROM Person.Contact 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 8-4.
Many of these lock modes are very rare or transient, so you not often see them 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 escalated 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 will have 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'll 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 extentsunits of disk space that are 64 kilobytes (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 will have 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 will request an exclusive database lock, and if any other processes have a shared lock on the database, the request will block. Generally, you don't need to be concerned with extent or database locks, but you'll see them if you are perusing sys.dm_tran_locks. You might occasionally see locks on HOBT and ALLOCATION_UNIT resources. Although all table and index structures are based on HOBTs and 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 done just as in versions prior to SQL Server 2005, and no deferred operation takes place. 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 will see ALLOCATION_UNIT locks in sys.dm_tran_locks. You can also look in 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 will occur after the transaction commits. Application Locks
The method used by SQL Server to store information about locking and to check for incompatible locks is very straightforward and extensible. SQL Server knows nothing about the object it is locking. It works only with strings representing the resources, without knowing the actual structure of the item. If two processes are trying to obtain incompatible locks on the same resource, blocking will occur. Application locks allow you to take advantage of the supplied mechanisms for detecting blocking and deadlocking situations, and you can choose to lock anything you like. These lock resources are called application locks. To define an application lock, you specify a name for the resource you are locking, a mode, an owner (or scope) of the lock, a timeout, and a Database Principal ID (which is a user, role, or application role that can have permissions in a database). Unlike SQL Server's own lock resources, such as tables and pages, application locks must be specifically requested. Only users meeting one of the following criteria can execute the sp_getapplock procedure:
The default Database Principal ID, when calling sp_getapplock, is public. Note
If two resources have the same name in the same database and have the same Database Principal ID, they are considered to be the same resource and are subject to blocking. For application locks, the lock owner can be either the session or the transaction. Two requests for locks on the same resource can be granted if the modes of the locks requested are compatible. The locks are checked for compatibility using the same compatibility matrix used for SQL Serversupplied locks. For example, suppose that you have a stored procedure that only one user at a time should execute. Anyone in the ProcUserRole database role can lock that procedure by using the sp_getapplock procedure to acquire a special lock, which indicates to other processes that someone is using this procedure. When the procedure is complete, you can use sp_releaseapplock to release the lock: EXEC sp_getapplock 'ProcLock', 'Exclusive', 'session', 'ProcUserRole' EXEC MySpecialProc <parameter list> EXEC sp_releaseapplock 'ProcLock', 'session'
Until the lock is released using sp_releaseapplock, or until the session terminates, no other session can execute this procedure if it follows this protocol and uses sp_getapplock to request rights on the resource called ProcLock before trying to execute the procedure. SQL Server doesn't know what the resource ProcLock means. You can use any identifier you choose. SQL Server just adds a row to the sys.dm_tran_locks view when an application lock is requested, and it uses the resource name and Database Principal ID to compare against other requested locks. Note that the procedure itself is not really locked. If another user or application doesn't know that this is a special procedure and tries to execute MySpecialProc without acquiring the application lock, SQL Server will not prevent the session from executing the procedure. The resource name used in these procedures can be any identifier up to 255 characters long; however, only the first 32 characters will be shown in the resource_description string in sys.dm_tran_locks. The possible modes of the lock, which is used to check compatibility with other requests for this same resource, are Shared, Update, Exclusive, IntentExclusive, and Intent-Shared. There is no default; you must specify a mode. The possible values for lock owner, the third parameter, are transaction (the default) or session. A lock with an owner of transaction must be acquired with a user-defined transaction, and it is automatically released at the end of the transaction without any need to call sp_releaseapplock. A lock with an owner of session is released automatically only when the session disconnects. Here's an example. Let's request the lock shown previously and then look at the sys.dm_tran_locks view: EXEC sp_getapplock 'ProcLock', 'Exclusive', 'session'; GO SELECT resource_type, resource_description, resource_associated_entity_id, request_mode, request_status FROM sys.dm_tran_locks WHERE resource_type = 'APPLICATION' Here are my results: [View full width] resource_type resource_description resource_associated_entity_id request_mode
Note that no database entity is associated with this lock because SQL Server does not connect it to any resource actually in the database. 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 8-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.
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'll see that you can distinguish between these types of locks by the value in the lock mode column. Other possible resources listed in the resource_type column are HOBT and ALLOCATION_UNIT, which I mentioned earlier. These can be identified by the value in the resource_associated_entity_id column, which I'll discuss in the next section. A final 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 will be 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 HoBT 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 HoBT ID is provided for resource types PAGE, KEY, RID, and HOBT. There is no simple function to convert a HoBT 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 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 object_name function applies only to the current database, this code is filtered to only return 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'll 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.hobt_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 sameshared 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'll discuss query hints for locking briefly later in this chapter. A full discussion of hints is in Inside Microsoft SQL Server 2005: Query Tuning and Optimization. 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 req_ownertype value of CURSOR must be explicitly requested 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 2005, locks owned by a session must also be explicitly requested 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 explicitly released. SQL Server 2000 makes much heavier use of session-owned locks; in SQL Server 2005, transaction_workspace locks are used instead. 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 and bound session (discussed later in this chapter), 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'll 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 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 requesting process. 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 made only brief reference 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 8-6 lists all the subtypes for resource types other than METADATA.
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:
Most of the other METADATA subtypes not listed here refer to elements of SQL Server 2005 that are not discussed in this book, including CLR routines, XML, certificates, full-text search, and notification services. Request Columns
I've also mention 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:
Locking Examples
The following examples show what many of the lock types and modes discussed earlier look like when reported using the DBlocks view I described previously. Example 1: SELECT with Default Isolation Level
SQL BATCH USE AdventureWorks; 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 AdventureWorks n/a NULL DATABASE S GRANT 60 AdventureWorks sysrowsets NULL OBJECT Sch-S GRANT 60 AdventureWorks DBlocks NULL OBJECT IS GRANT
There are no locks on the data in the Production.Product table because the batch was doing 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, an OBJECT lock on the view's schema, and an OBJECT lock on the rowset. Example 2: SELECT with Repeatable Read Isolation Level
SQL BATCH USE AdventureWorks; 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 AdventureWorks Product NULL OBJECT IS GRANT 54 AdventureWorks Product 1 PAGE 1:16897 IS GRANT 54 AdventureWorks Product 1 KEY (6b00b8eeda30) S GRANT 54 AdventureWorks Product 1 KEY (6a00dd896688) S GRANT 54 AdventureWorks Product 3 KEY (9502d56a217e) S GRANT 54 AdventureWorks Product 3 PAGE 1:1767 IS GRANT 54 AdventureWorks 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 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 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.) 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 AdventureWorks; 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 AdventureWorks Product NULL OBJECT IS GRANT 54 AdventureWorks Product 1 PAGE 1:16897 IS GRANT 54 AdventureWorks Product 1 KEY (6b00b8eeda30) S GRANT 54 AdventureWorks Product 1 KEY (6a00dd896688) S GRANT 54 AdventureWorks Product 3 KEY (9502d56a217e) RangeS-S GRANT 54 AdventureWorks Product 3 PAGE 1:1767 IS GRANT 54 AdventureWorks Product 3 KEY (23027a50f6db) RangeS-S GRANT 54 AdventureWorks 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 (IndId = 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 between Racing Socks and the previous key, Pinch Bolt, and nothing between Racing Socks and the next key, Rear Brakes, could be inserted. For example, we could not insert a product with the name Portkey or Racing Tights.) Example 4: Update Operations
SQL BATCH USE AdventureWorks; 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 AdventureWorks Product NULL OBJECT IX GRANT 54 AdventureWorks Product 1 PAGE 1:16897 IX GRANT 54 AdventureWorks Product 1 KEY (6b00b8eeda30) X GRANT 54 AdventureWorks Product 1 KEY (6a00dd896688) 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 escalated to X locks when the actual update is done, 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 AdventureWorks; 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 AdventureWorks Product NULL OBJECT IX GRANT 54 AdventureWorks Product 1 PAGE 1:16897 IX GRANT 54 AdventureWorks Product 1 KEY (6a00dd896688) X GRANT 54 AdventureWorks Product 1 KEY (6b00b8eeda30) X GRANT 54 AdventureWorks Product 3 KEY (9502d56a217e) RangeS-U GRANT 54 AdventureWorks Product 3 PAGE 1:1767 IU GRANT 54 AdventureWorks Product 3 KEY (23027a50f6db) RangeS-U GRANT 54 AdventureWorks 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 share 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 (IndId = 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 Level Not Using an Index
SQL BATCH USE AdventureWorks; 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 AdventureWorks Product NULL OBJECT IX GRANT 54 AdventureWorks Product 1 KEY (7900ac71caca) RangeS-U GRANT 54 AdventureWorks Product 1 KEY (6100dc0e675f) RangeS-U GRANT 54 AdventureWorks Product 1 KEY (5700a1a9278a) RangeS-U GRANT 54 AdventureWorks Product 1 PAGE 1:16898 IU GRANT 54 AdventureWorks Product 1 PAGE 1:16899 IU GRANT 54 AdventureWorks Product 1 PAGE 1:16896 IU GRANT 54 AdventureWorks Product 1 PAGE 1:16897 IX GRANT 54 AdventureWorks Product 1 PAGE 1:16900 IU GRANT 54 AdventureWorks Product 1 PAGE 1:16901 IU GRANT 54 AdventureWorks Product 1 KEY (5600c4ce9b32) RangeS-U GRANT 54 AdventureWorks Product 1 KEY (7300c89177a5) RangeS-U GRANT 54 AdventureWorks Product 1 KEY (7f00702ea1ef) RangeS-U GRANT 54 AdventureWorks Product 1 KEY (6b00b8eeda30) RangeX-X GRANT 54 AdventureWorks Product 1 KEY (c500b9eaac9c) RangeX-X GRANT 54 AdventureWorks Product 1 KEY (c6005745198e) RangeX-X GRANT 54 AdventureWorks 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 (IndId = 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 escalated to the RangeX-X lock. You can see all the RangeX-X locks, but not all the RangeS-U locks are shown because there are 504 rows in the table. Example 7: Creating a Table
SQL BATCH USE AdventureWorks; 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 AdventureWorks n/a NULL DATABASE NULL GRANT 54 AdventureWorks n/a NULL DATABASE NULL GRANT 54 AdventureWorks n/a NULL DATABASE S GRANT 54 AdventureWorks n/a NULL METADATA user_type_id = 258 Sch-S GRANT 54 AdventureWorks n/a NULL METADATA data_space_id = 1 Sch-S GRANT 54 AdventureWorks n/a NULL DATABASE S GRANT 54 AdventureWorks n/a NULL METADATA $seq_type = 0, objec Sch-M GRANT 54 AdventureWorks n/a NULL METADATA user_type_id = 260 Sch-S GRANT 54 AdventureWorks sysrowsetcol NULL OBJECT IX GRANT 54 AdventureWorks sysrowsets NULL OBJECT IX GRANT 54 AdventureWorks sysallocunit NULL OBJECT IX GRANT 54 AdventureWorks syshobtcolum NULL OBJECT IX GRANT 54 AdventureWorks syshobts NULL OBJECT IX GRANT 54 AdventureWorks sysserefs NULL OBJECT IX GRANT 54 AdventureWorks sysschobjs NULL OBJECT IX GRANT 54 AdventureWorks syscolpars NULL OBJECT IX GRANT 54 AdventureWorks sysidxstats NULL OBJECT IX GRANT 54 AdventureWorks sysrowsetcol 1 KEY (15004f6b3486) X GRANT 54 AdventureWorks sysrowsetcol 1 KEY (0a00862c4e8e) X GRANT 54 AdventureWorks sysrowsets 1 KEY (000000aaec7b) X GRANT 54 AdventureWorks sysallocunit 1 KEY (00001f2dcf47) X GRANT 54 AdventureWorks syshobtcolum 1 KEY (1900f7d4e2cc) X GRANT 54 AdventureWorks syshobts 1 KEY (000000aaec7b) X GRANT 54 AdventureWorks NULL NULL RID 1:6707:1 X GRANT 54 AdventureWorks DBlocks NULL OBJECT IS GRANT 54 AdventureWorks newProducts NULL OBJECT Sch-M GRANT 54 AdventureWorks sysserefs 1 KEY (010025fabf73) X GRANT 54 AdventureWorks sysschobjs 1 KEY (3b0042322c99) X GRANT 54 AdventureWorks syscolpars 1 KEY (4200c1eb801c) X GRANT 54 AdventureWorks syscolpars 1 KEY (4e00092bfbc3) X GRANT 54 AdventureWorks sysidxstats 1 KEY (3b0006e110a6) X GRANT 54 AdventureWorks sysschobjs 2 KEY (9202706f3e6c) X GRANT 54 AdventureWorks syscolpars 2 KEY (6c0151be80af) X GRANT 54 AdventureWorks syscolpars 2 KEY (2c03557a0b9d) X GRANT 54 AdventureWorks sysidxstats 2 KEY (3c00f3332a43) X GRANT 54 AdventureWorks sysschobjs 3 KEY (9202d42ddd4d) X GRANT 54 AdventureWorks sysschobjs 4 KEY (3c0040d00163) X GRANT 54 AdventureWorks newProducts 0 PAGE 1:6707 X GRANT 54 AdventureWorks 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. Also notice the schema modification (Sch-M) lock and other metadata locks on the new table. The final example will look 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 AdventureWorks; 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 AdventureWorks newProducts NULL OBJECT IX GRANT 54 AdventureWorks newProducts 0 PAGE 1:6708 IX GRANT 54 AdventureWorks 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 criterion 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. |