Lock Types for User Data

Now 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. For example, cursor scroll locks are held until a new FETCH operation is executed. 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.

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 it 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 that 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 between 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 14-2.

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.) 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 acquire 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.

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 copy 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. If multiple connections have requested and received a bulk update lock, they can perform parallel loads into the same table.

Another lock mode that you might notice is the SIX lock. This mode is never requested directly by the lock manager but is the result of a conversion. If a transaction is holding a shared (S) lock on a resource and later an IX lock is needed, the lock mode will be indicated as SIX. For example, suppose 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 

Assuming that the table is large, the SELECT statement will acquire a shared table lock. (If there are only a few rows in bigtable, SQL Server will acquire individual row or key locks.) The UPDATE statement will then acquire a single exclusive key lock to do 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 sp_lock.

Table 14-1 shows most of the lock modes, as well as the abbreviations used in the output of sp_lock.

Table 14-1. SQL Server lock modes.

Abrreviation	Lock Mode	Internal Code for Lock Mode	Description
S	Shared	4	Allows other processes to read but not change the locked resource.
X	Exclusive	6	Prevents another process from modifying or reading data in the locked resource (unless the process is set to the Read Uncommitted isolation level).
U	Update	5	Prevents other processes from acquiring an update or exclusive lock; used when searching for the data to modify.
IS	Intent update	7	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	8	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	9	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	11	Indicates that a resource intent exclusive holding a shared lock also has a component (a page or row)locked with an exclusive lock.
Sch-S	Schema stability	2	Indicates that a query using this table is being compiled.
Sch-M	Schema modification	3	Indicates that the structure fication of the table is being changed.
BU	Bulk update	13	Used when a bulk copy operation is copying data into a table and the TABLOCK hint is being applied (either manually or automatically).

Additional locks modes—called key-range locks—are taken only in serializable isolation level for locking ranges of data. 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 14-2.

Table 14-2. Key-range locks.

Abbreviation	Internal Code for Lock Mode	Description
RangeS-S	14	Shared lock on the range between keys; shared lock on the key at the end of the range
RangeS-U	15	Shared lock on the range between keys; update lock on the key at the end of the range
RangeIn-Null	16	Exclusive lock to prevent inserts on the range between keys; no lock on the keys themselves
RangeX-X	22	Exclusive lock on the range between keys; exclusive lock on the key at the end of the range
RangeIn-S	17	Conversion lock created by S and RangeIn_Null lock
RangeIn-U	18	Conversion lock created by U and RangeIn_Null lock
RangeIn-X	19	Conversion of X and RangeIn_Null lock
RangeIn-U	20	Conversion of RangeIn_Null and RangeS_S lock
RangeX-U	21	Conversion of RangeIn_Null and RangeS_U lock

The RangeIn-Null lock is acquired when SQL Server attempts to insert into the range between keys. 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 you have one transaction scan 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 in a WAIT state with the RangeIn-Null mode. You can observe this using the sp_lock command, as I'll describe in the section on viewing locks later in the chapter.

Lock Granularity

SQL Server can lock user data resources (not system resources, which are protected with latches) at the table, page, or row level. SQL Server also locks index keys and ranges of index keys. Figure 14-3 shows the possible lock levels in a table. Note 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 14-3. Levels of granularity for SQL Server locks.

The syslockinfo table keeps track of each lock by storing the type of resource locked (such as a row, key, or page), the mode of the lock, and an identifier for the specific resource. When a process requests a lock, SQL Server compares the lock requested to the resources already listed in the syslockinfo table and looks for an exact match on the resource type and identifier. (The lock modes don't have to be the same to yield an exact match.) However, if one process has a row exclusively locked in the authors table, for example, another process might try to get a lock on the entire authors table. Since these are two different resources, SQL Server does not find an exact match unless additional information is already stored in syslockinfo. This is what intent locks are for. The process that has the exclusive lock on a row of the authors table also has an intent exclusive lock on the page containing the row and another intent exclusive lock on the table containing the row. When the second process attempts to acquire the exclusive lock on the table, it finds a conflicting row already in the syslockinfo table on the same lock resource (the authors table). 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 2000 supports two kinds of key locks, whose use depends on the isolation level of the current transaction. If the isolation level is Read Committed or Repeatable Read, 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 nonclustered indexes and row locks for the actual data.

If the isolation level is Serializable, the situation is special. We want to prevent phantoms, which means that if we have scanned a range of data within a transaction, we need to lock enough of the table to make sure that no one can insert a value into the range that was scanned. For example, we can issue the following query within an explicit transaction:

 BEGIN TRAN SELECT * FROM employees WHERE salary BETWEEN 30000 AND 50000 

Locks must be acquired to make sure that no new rows with salary values between 30000 and 50000 are inserted before the end of the transaction. Prior to version 7.0, SQL Server 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 2000 uses key-range locks, which are associated with a particular key value in an index and indicate that all values between that key and the previous one in the index are locked.

Suppose we have an index on the lastname field in the employees table. We are in TRANSACTION ISOLATION LEVEL SERIALIZABLE and we issue this SELECT statement:

 SELECT * FROM employees WHERE last_name BETWEEN 'Delaney' AND 'DuLaney' 

If Dallas, Donovan, and Duluth are sequential leaf-level index keys in the table, the second two of these (Donovan and Duluth) acquire key-range locks (although only one row, for Donovan, 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 Dallas and less than or equal to Donovan can be inserted, and no values greater than Donovan and less than or equal to Duluth 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 Delany or Delanie, which are in the range specified in the WHERE clause. However, the key-range locks would also prevent anyone from inserting DeLancey (which is greater than Dallas and less than Donovan) even though DeLancey 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, which was the only possibility in previous SQL Server versions.

Additional Lock Resources

Locking is also done 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 the output of the sp_lock procedure. Extents can have both shared extent and exclusive extent locks.

When you examine the output of sp_lock, notice that most processes hold a lock on at least one database. In fact, any process holding locks in any database other than master or tempdb will have a DB lock for that database. These are always shared locks and are used by SQL Server for determining when a database is in use. SQL Server detects DB locks when determining whether a database can be dropped, restored, or closed. Since master and tempdb cannot be dropped or closed, DB 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, DB locks are unnecessary. Generally, you don't need to be concerned with extent or database locks, but you might see them if you are running sp_lock or perusing syslockinfo.

Application Locks

The method used by SQL Server to store information about locking and to check for incompatible locks is very straightforward and extensible. As you've seen, the SQL Server lock manager 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.

If the SQL Server developers were to decide to allow you to lock individual columns as well as rows, pages, and tables, they could simply decide on an internal code number for column locks, and then we could add that to the list of resources in Table 14-3.

Instead of adding new lock resources, SQL Server 2000 lets you extend the resources that can be locked. You can 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, and a timeout.

Two resources are considered to be the same resource and are subject to blocking if they have the same name and the same owner in the same database. Remember that by lock owner we mean the session, the transaction, or a cursor. For your own application locks, the only possible owners are transaction and session. 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 Server supplied locks.

For example, suppose you have a stored procedure that only one user at a time should execute. You can "lock" that procedure by using the sp_getapplock procedure to acquire a special lock, which means 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' 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 as long as it follows the protocol and uses sp_getapplock to request rights to the procedure before trying to execute it. SQL Server doesn't know what the resource ProcLock means. It just adds a row to the syslockinfo table that it will use 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. 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 IntentShared. 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 will be automatically released at the end of the transaction without any need to call sp_releaseapplock. A lock with an owner of session will be released automatically only when the session disconnects.

Identifying Lock Resources

When the lock manager tries to determine whether a requested lock can be granted, it checks the syslockinfo table to determine whether a matching lock with a conflicting lock mode already exists. It compares locks by looking at the database ID (dbid), the object ID (objid), the type of resource locked, and the description of the specific resource referenced by the lock. The lock manager 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, 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 14-3 shows all the lock resources, the abbreviations used in the output of sp_lock, and the information used to define the actual resource locked.

Table 14-3. Lockable resources in SQL Server.

Resource	Abbreviation	Internal Code for Resource	Resource Description	Example
Database	DB	2	None; the database is always indicated in the dbid column for every locked resource.
Table	TAB	5	The table ID.	261575970 (Note that sp_lock shows the table ID in its own column rather than in the resource description column.)
Extent	EXT	8	File number:page number of the first page of the extent.	1:96
Page	PAG	6	File number:page number of the actual table or index page.	1:104
Index Key	KEY	7	A hashed value derived from all the key components and the locator. For a nonclustered index on a heap, where columns c1 and c2are indexed, the hash would contain contributions from c1, c2, and the RID.	ac0001a10a00
Index Key Range	KEY	7	Same as Index Key	ac0001a10a00
Row	RID	9	File number:page number:slot number of the actual row.	1:161:3
Application	APP	10	A hashed value derived from the name given to the lock.	MyPr8adaea5f

Note that key locks and key-range locks have identical resource descriptions. When we look at some examples of output from the sp_lock procedure, you'll see that you can distinguish between these types of locks by the value in the lock mode column.

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. An exclusive lock is held until the end of the transaction, whether it 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 with all exclusive locks, remains for the duration of the transaction. 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 addition to changing your transaction isolation level, you can control the lock duration by using query hints. I'll discuss query hints for locking and for other purposes in Chapter 16.

Lock Ownership

Lock duration can also be affected by the lock ownership. There are three types of lock owners: transactions, cursors, and sessions. These are available through the req_ownertype column in the syslockinfo table. (This information is not visible through the sp_lock stored procedure.) A req_ownertype value of 1 indicates that the lock is owned by transaction, and its duration is as discussed as described in the previous section. Most of our locking discussion, in fact, deals with locks owned by a transaction.

A cursor lock has a req_ownertype value of 2. 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.

Locks owned by a session have a req_ownertype value of 3. A session lock is one taken on behalf of a process that is outside the scope of a transaction. The most common example is a database lock, as discussed earlier. A process acquires a session lock on the database when it issues the USE database command, and that lock isn't released until another USE command is issued or until the process is disconnected.

Viewing Locks

To see the locks currently outstanding in the system as well as those that are being waited for, examine the syslockinfo system table or execute the system stored procedure sp_lock. The syslockinfo table is not really a system table. It is not maintained on disk because locks are not maintained on disk. Rather, it is materialized in table format based on the lock manager's current accounting of locks each time syslockinfo is queried. Another way to watch locking activity is with the excellent graphical representation of locking status provided by SQL Server Enterprise Manager. Even those who think that GUIs are for wimps can appreciate SQL Server Enterprise Manager's view of locking.

In some cases, the output of sp_lock can be voluminous. You can reduce the output by specifying one or two process ID values; sp_lock will then show you only locks held by those processes. The process ID for a particular connection is available using the system function @@spid. You can execute sp_lock and specify only your current connection:

 EXEC sp_lock @@spid 

However, even limiting the output to just the locks for the current connection can sometimes generate more output that you're interested in. To produce the lock output, SQL Server must translate internal ID numbers for the type of lock and mode of lock into the strings shown in Tables 14-1, 14-2, and 14-3. To do this translation, SQL Server uses the spt_values table in the master database as a giant lookup table. If you're using the Serializable isolation level, locks can be held on this table in the master database as well as on temporary tables in tempdb. Having to wade through these additional locks—which exist only because you're running sp_lock to examine your locks—can make it difficult to understand the locks on your user data. To help solve this problem, I have written a modified sp_lock procedure called sp_lock2, which does not print out any locks in the master, model, tempdb, or msdb databases. In addition, the procedure translates the database ID into the database name. You can find the script to create sp_lock2 on the companion CD.

The following examples show what each of the lock types and modes discussed earlier look like when reported by the sp_lock2 procedure. Note that the call to the sp_lock2 procedure is preceded by the keyword EXECUTE, which is required when the call to a stored procedure is not the first item in a batch. Note also that the sp_lock2 procedure is given an argument of @@spid so that we'll see only locks for the current process.

Example 1: SELECT with Default Isolation Level

SQL BATCH

 USE PUBS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT * FROM authors WHERE au_lname = 'Ringer' EXEC sp_lock2 @@spid COMMIT TRAN 

OUTPUT OF sp_lock2

 spid Database ObjId IndId Type Resource Mode Status ------ --------- ------ ------ ---- --------------- -------- ------ 52 pubs 0 0 DB S GRANT 

The only lock we have is the DB lock. No locks on the authors table are held at this point 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 sp_lock2 is executed, the locks are no longer held.

Example 2: SELECT with Repeatable Read Isolation Level

SQL BATCH

 USE PUBS SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM authors WHERE au_lname = 'Ringer' EXEC sp_lock2 @@spid COMMIT TRAN 

OUTPUT OF sp_lock2

 spid Database ObjId IndId Type Resource Mode Status ----- --------- ----------- ------ ---- ---------------- ----- ------ 54 pubs 0 0 DB S GRANT 54 pubs 1977058079 2 KEY (62039d7395e8) S GRANT 54 pubs 1977058079 1 PAG 1:88 IS GRANT 54 pubs 1977058079 2 PAG 1:116 IS GRANT 54 pubs 1977058079 1 KEY (04015bb61919) S GRANT 54 pubs 1977058079 1 KEY (1201b4159b48) S GRANT 54 pubs 1977058079 2 KEY (6e021955d8e9) S GRANT 54 pubs 1977058079 0 TAB IS GRANT 

Because the authors table has a clustered index, the rows of data are all index rows in the leaf level. The locks on the individual rows are marked as key locks instead of row locks. There are also key locks at the leaf level of the nonclustered index on the table. In the authors table, the nonclustered index is on the author's last name (au_lname) column, and that is the index being traversed to find the specified rows. You can tell the clustered and nonclustered indexes apart by the value in the IndId field: the data rows have an IndId value of 1, and the nonclustered index rows have an IndId value of 2. Because the transaction isolation level is Repeatable Read, the shared locks are held until the transaction is finished. Note that the two rows and two 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 PUBS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM authors WHERE au_lname = 'Ringer' EXEC sp_lock2 @@spid COMMIT TRAN 

OUTPUT OF sp_lock2

 spid Database ObjId IndId Type Resource Mode Status ----- -------- ----------- ----- ---- --------------- -------- ------ 56 pubs 0 0 DB S GRANT 56 pubs 1977058079 2 KEY (62039d7395e8) RangeS-S GRANT 56 pubs 1977058079 1 PAG 1:88 IS GRANT 56 pubs 1977058079 2 PAG 1:116 IS GRANT 56 pubs 1977058079 1 KEY (04015bb61919) S GRANT 56 pubs 1977058079 1 KEY (1201b4159b48) S GRANT 56 pubs 1977058079 2 KEY (6e021955d8e9) RangeS-S GRANT 56 pubs 1977058079 0 TAB IS GRANT 56 pubs 1977058079 2 KEY (4903312f82bf) 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 last name of Ringer can be inserted. The key-range locks are held on ranges in the nonclustered index on au_lname and au_fname (IndId = 2) 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. SQL Server must lock the range from the key preceding the first Ringer in the index up to the first Ringer, it must lock the range between the two instances of Ringer, and it must lock the range from the second Ringer to the next key in the index. (So actually nothing between Ringer and the previous key, Panteley, and nothing between Ringer and the next key, Smith, could be inserted. For example, we could not insert an author with the last name Pike or Singh.)

Example 4: Update Operations

SQL BATCH

 USE PUBS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE authors SET contract = 0 WHERE au_lname = 'Ringer' EXEC sp_lock2 @@spid COMMIT TRAN 

OUTPUT OF sp_lock2

 spid Database ObjId IndId Type Resource Mode Status ----- --------- ----------- ------ ---- --------------- ----- ------ 57 pubs 0 0 DB S GRANT 57 pubs 1977058079 1 PAG 1:88 IX GRANT 57 pubs 1977058079 1 KEY (04015bb61919) X GRANT 57 pubs 1977058079 1 KEY (1201b4159b48) X GRANT 57 pubs 1977058079 0 TAB IX 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 the sp_lock2 procedure is run, the update locks are gone. Unless you actually force update locks with a query hint, you might never see them in the output of sp_lock or sp_lock2.

Example 5: Update with Serializable Isolation Level Using an Index

SQL BATCH

 USE PUBS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN UPDATE authors SET contract = 0 WHERE au_lname = 'Ringer' EXEC sp_lock2 @@spid COMMIT TRAN 

OUTPUT OF sp_lock2

 spid Database ObjId IndId Type Resource Mode Status ----- --------- ----------- ----- ---- --------------- -------- ------ 53 pubs 0 0 DB S GRANT 53 pubs 1977058079 2 KEY (62039d7395e8) RangeS-U GRANT 53 pubs 1977058079 0 TAB IX GRANT 53 pubs 1977058079 2 KEY (4903312f82bf) RangeS-U GRANT 53 pubs 1977058079 1 PAG 1:88 IX GRANT 53 pubs 1977058079 2 KEY (6e021955d8e9) RangeS-U GRANT 53 pubs 1977058079 2 PAG 1:116 IU GRANT 53 pubs 1977058079 1 KEY (04015bb61919) X GRANT 53 pubs 1977058079 1 KEY (1201b4159b48) X 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 that 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 PUBS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN UPDATE authors SET contract = 1 WHERE state = 'UT' EXEC sp_lock2 @@spid COMMIT TRAN 

OUTPUT OF sp_lock2

 spid Database ObjId IndId Type Resource Mode Status ---- -------- ----------- ------ ---- ---------------- -------- ------ 53 pubs 1977058079 1 KEY (0b018636f9dc) RangeS-U GRANT 53 pubs 0 0 DB S GRANT 53 pubs 1977058079 1 KEY (100125852812) RangeS-U GRANT 53 pubs 1977058079 1 KEY (1001898f02b5) RangeS-U GRANT 53 pubs 1977058079 1 KEY (02010b688383) RangeS-U GRANT 53 pubs 1977058079 1 KEY (0401c963b692) RangeS-U GRANT 53 pubs 1977058079 1 KEY (06013d38d450) RangeS-U GRANT 53 pubs 1977058079 1 KEY (0d01ac7ad21d) RangeS-U GRANT 53 pubs 1977058079 0 TAB IX GRANT 53 pubs 1977058079 1 KEY (0c01f170d106) RangeS-U GRANT 53 pubs 1977058079 1 KEY (10018e6baefb) RangeS-U GRANT 53 pubs 1977058079 1 KEY (0501dc9d152f) RangeS-U GRANT 53 pubs 1977058079 1 KEY (0801c4f7a625) RangeS-U GRANT 53 pubs 1977058079 1 KEY (0c019fc272ba) RangeS-U GRANT 53 pubs 1977058079 1 KEY (0101aedb232b) RangeS-U GRANT 53 pubs 1977058079 1 KEY (0601989a35e6) RangeS-U GRANT 53 pubs 1977058079 1 KEY (0e01733a43ca) RangeS-U GRANT 53 pubs 1977058079 1 KEY (0601d808a263) RangeS-U GRANT 53 pubs 1977058079 1 KEY (070199a50c2d) RangeS-U GRANT 53 pubs 1977058079 1 PAG 1:88 IX GRANT 53 pubs 1977058079 1 KEY (100198c3f985) RangeS-U GRANT 53 pubs 1977058079 1 KEY (ffffffffffff) RangeS-U GRANT 53 pubs 1977058079 1 KEY (0701a9d28094) RangeS-U GRANT 53 pubs 1977058079 1 KEY (02014f0bec4e) RangeS-U GRANT 53 pubs 1977058079 1 KEY (0901ce1ba5f0) RangeS-U GRANT 53 pubs 1977058079 1 KEY (04015bb61919) RangeX-X GRANT 53 pubs 1977058079 1 KEY (1201b4159b48) 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 the two row were eventually modified, the locks on those keys escalated to the RangeX-X lock.

Example 7: Creating a Table

SQL BATCH

 USE PUBS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT * INTO newTitles FROM titles WHERE price < 5 EXEC sp_lock2 @@spid COMMIT TRAN 

OUTPUT OF sp_lock2

 spid Database ObjId IndId Type Resource Mode Status ---- --------- ----------- ------ ---- ---------------- ----- ------ 57 pubs 0 0 DB [BULK-OP-LOG] NULL GRANT 57 pubs 0 0 DB S GRANT 57 pubs 0 0 DB [BULK-OP-DB] NULL GRANT 57 pubs 1 0 TAB IX GRANT 57 pubs 3 0 TAB IX GRANT 57 pubs 2 0 TAB IX GRANT 57 pubs 3 2 KEY (e40194df4eba) X GRANT 57 pubs 3 2 KEY (7b0152f4ac0f) X GRANT 57 pubs 3 2 KEY (ec01c5f30203) X GRANT 57 pubs 0 0 PAG 1:146 X GRANT 57 pubs 0 0 PAG 1:145 X GRANT 57 pubs 3 2 KEY (410288dc0a1c) X GRANT 57 pubs 0 0 PAG 1:199 X GRANT 57 pubs 0 0 PAG 1:198 X GRANT 57 pubs 0 0 PAG 1:197 X GRANT 57 pubs 0 0 PAG 1:196 X GRANT 57 pubs 0 0 PAG 1:195 X GRANT 57 pubs 0 0 PAG 1:194 X GRANT 57 pubs 0 0 PAG 1:193 X GRANT 57 pubs 0 0 PAG 1:192 X GRANT 57 pubs 0 0 EXT 1:192 X GRANT 57 pubs 3 1 KEY (a1007beae6ee) X GRANT 57 pubs 3 1 KEY (9a00f1a5ee93) X GRANT 57 pubs 3 1 KEY (a000954553fc) X GRANT 57 pubs 3 1 KEY (9d00c35530a4) X GRANT 57 pubs 3 1 KEY (9c002dfa85b6) X GRANT 57 pubs 3 2 KEY (4d02b937e929) X GRANT 57 pubs 3 2 KEY (c6029c7b5ab8) X GRANT 57 pubs 1 1 KEY (97009fa6c549) X GRANT 57 pubs 3 2 KEY (d2016d108bf1) X GRANT 57 pubs 3 1 KEY (98007a6de739) X GRANT 57 pubs 3 1 KEY (990094c2522b) X GRANT 57 pubs 3 1 KEY (9e00a6328c1c) X GRANT 57 pubs 3 1 KEY (9b00489d390e) X GRANT 57 pubs 3 1 KEY (9f00f022ef44) X GRANT 57 pubs 3 2 KEY (d501db08ee70) X GRANT 57 pubs 3 2 KEY (2f02d5933c81) X GRANT 57 pubs 0 0 IDX IDX: 5:725577623 X GRANT 57 pubs 725577623 0 TAB Sch-M GRANT 57 pubs 1 2 KEY (2e02001f6a2c) X GRANT 57 pubs 2 1 KEY (9700cdd96cc9) X GRANT 57 pubs 3 2 KEY (6702012f70a8) X GRANT 57 pubs 1 3 KEY (9700eaa6a30d) X GRANT  

Very few of these locks are actually acquired on elements of the new table. In the ObjId column, notice that most of the objects have an ID of less than 100, which means that they are system tables. As the new newTitles table is built, SQL Server acquires locks on sysobjects and syscolumns to record information about this new table. Also notice the schema modification (Sch-M) lock on the new table as well as the extent (EXT) locks. While the table is being built, the extents are not marked as belonging to the table; you can see that the ObjId is 0. In the output in this example, the extent ID is shown as 1:192. This means that page 192 in file 1 is the first page of the extent. You can also see that the subsequent seven pages (193-199) in this extent are all exclusively locked while the table is being created.

Example 8: Row Locks

SQL BATCH

 USE PUBS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE newTitles SET price = 3.99 WHERE type = 'business' EXEC sp_lock2 @@spid ROLLBACK TRAN 

OUTPUT OF sp_lock2

 spid Database ObjId IndId Type Resource Mode Status ---- --------- ----------- ------ ---- ---------- -------- ------ 58 pubs 0 0 DB S GRANT 58 pubs 693577509 0 TAB IX GRANT 58 pubs 693577509 0 PAG 1:192 IX GRANT 58 pubs 693577509 0 RID 1:192:0 X GRANT 

There are no indexes on the newTitles table, so the lock on the actual row meeting our criterion is an exclusive (X) lock on the row (RID). As expected, IX locks are taken on the page and the table.