The Lock Manager
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 when the data is read. The SQL Server lock manager acquires and releases these locks. It also manages compatibility between lock modes, resolves deadlocks, and escalates locks if necessary. It controls locks on tables, on the pages of a table, on index keys, and on individual rows of data. Locks can also be held on system data—data that's private to the database system, such as page headers and indexes.

The lock manager 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, text 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. It uses latches, which are less resource intensive than locks and provide performance optimization. You could use full-blown locks for all locking, but because of their complexity, they would slow down the system if they were used for all internal needs. If you examine locks using the sp_lock system stored procedure or a similar mechanism that gets information from the syslockinfo table, you cannot see latches—you see only information about locks for fully shared data.

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.

The Lock Manager and Isolation Levels
SQL Server supports all four transaction isolation levels specified by ANSI and ISO: Serializable, Repeatable Read, Read Committed, and Read Uncommitted. (See Chapter 3 and Chapter 10 for details.) To achieve the Serializable isolation level, phantoms must be prevented because the transaction's behavior must be identical to what would have occurred had the transaction run on a single-user system. SQL Server provides serializability, which you can set using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. To support serializability, SQL Server locks index ranges using a special type of lock called a key-range lock. Such locks are held until the end of the transaction to prevent phantoms. If no index exists, the lock manager uses a table lock to guarantee serializability.

The lock manager provides fairly standard two-phase locking services. Although the names are similar, two-phase locking (2PL) and the two-phase commit (2PC) protocol are not directly related, other than by the obvious fact that 2PC must use 2PL services. In two-phase locking, a transaction has a "growing" phase, during which it acquires locks, and a "shrinking" phase, during which it releases locks. To achieve serializability, all acquired locks are held until the end of the transaction and then are dropped all at once.

For a lower isolation level, such as Committed Read, locks can be released sooner—when the use of the object is completed. For example, if a range of data is being queried in the table, there will probably be shared locks outstanding. With Committed Read isolation, a shared lock is released as soon as the scan moves off one piece of data and onto the next. Exclusive locks, on the other hand, are always held until the end of the transaction so that the transaction can be rolled back if necessary.

With Serializable or Repeatable Read isolation, shared locks must be held until the end of the transaction to guarantee that the data that was read will not change or that new rows meeting the criteria of the query cannot be added while the transaction is in progress.

Like shared locks, latches are not tied to the boundaries of a transaction because they are used to provide mutual exclusion (mutex) functionality rather than to directly lock data. For example, during a row insert in a table with a clustered index, the nearby index page is latched to prevent other inserts from colliding. The latches are needed to provide mutual exclusion only during long periods of time (that is, periods with more than a few instruction cycles).

Spinlocks
For shorter-term needs, SQL Server achieves mutual exclusion using a latch, implemented 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 spinlock is the only place in SQL Server in which processor-specific assembly language is used. A spinlock is implemented in a few lines of assembly language specific to each processor type (such as x86/Pentium or Alpha). The requester of a spinlock repeats its request if the lock is not immediately available. (That is, the requestor "spins" on the lock until it is free.)

Spinlocks are often used as mutexes within SQL Server when a resource is 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 Microsoft Windows NT, 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).

Deadlocks
A deadlock occurs when two processes are waiting for a resource and neither process can advance because the other process prevents it from getting the resource. A true deadlock is a catch-22 in which, without intervention, neither process can ever progress. When a deadlock occurs, SQL Server intervenes automatically.

NOTE   A simple wait for a lock is not a deadlock. When the process that's holding the lock completes, the waiting process gets the lock. Lock waits are normal, expected, and necessary in multiple-user systems.

	Figure 13-1 Click here.

In SQL Server, two main types of deadlocks can occur: a cycle deadlock and a conversion deadlock. Figure 13-1 on page 731 shows an example of a cycle deadlock. Process A starts a transaction, acquires an exclusive table lock on the authors table, and requests an exclusive table lock on the publishers table. Simultaneously, process B starts a transaction, acquires an exclusive lock on the publishers table, and requests an exclusive lock on the authors table. The two processes become deadlocked—caught in a "deadly embrace." Each process holds a resource needed by the other process. Neither can progress, and, without intervention, both would be stuck in deadlock forever. You can actually generate the deadlock using the SQL Server Query Analyzer, as follows:

1. Open a query window, and change your database context to the pubs database.

Execute the following batch for process A:

BEGIN TRAN
UPDATE authors SET contract = 0
GO

2. Open a second window, and execute this batch for process B:

BEGIN TRAN
UPDATE publishers SET city = 'Redmond', state = 'WA'
GO

3. Go back to the first window, and execute this update statement:

UPDATE publishers SET city = 'New Orleans', state = 'LA'
GO

At this point, the query should block. It is not deadlocked yet, however. It is waiting for a lock on the publishers table, and there is no reason to suspect that it won't eventually get that lock.

4. Go back to the second window, and execute this update statement:

BEGIN TRAN
UPDATE authors SET contract = 1
GO

At this point, a deadlock occurs. The first connection will never get its requested lock on the publishers table because the second connection will not give it up until it gets a lock on the authors table. Since the first connection already has the lock on the authors table, we have a deadlock. One of the processes received this error message (of course, the actual process ID reported will probably be different):

Server: Msg 1205, Level 13, State 1, Line 1
Your transaction (process ID #12) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.

Figure 13-2 Click here.

Figure 13-2 on the following page shows an example of a conversion deadlock. Process A and process B each hold a shared lock on the same page within a transaction. Each process wants to promote its shared lock to an exclusive lock but cannot do so because of the other process's lock. Again, intervention is required.

SQL Server automatically detects deadlocks and intervenes through the lock manager, which provides deadlock detection for regular locks. Latches are not involved in deadlock detection because SQL Server uses deadlock-proof algorithms when it acquires latches. When SQL Server detects a deadlock, it terminates one process's batch, rolling back the transaction and releasing all that process's locks to resolve the deadlock.

In SQL Server 7, a separate thread called LOCK_MONITOR checks the system for deadlocks every 5 seconds. The lock monitor also uses an internal counter called a deadlock detection counter to determine whether to check the system for deadlocks more often. The deadlock detection counter starts at a value of 3 and is reset to 3 if a deadlock occurs. If the LOCK_MONITOR thread finds no deadlocks when it checks at the end of its 5-second cycle, it decrements the deadlock detection counter. If the counter has a value greater than 0, the lock manager requests that the lock monitor also check all the locks for a deadlock cycle if a process requests a lock resource and is blocked. Thus, after 20 seconds of not finding any deadlocks, the deadlock detection counter is 0 and the lock manager stops requesting deadlock detection every time a process blocks on a lock. The deadlock detection counter stays at 0 most of the time and the checking for deadlocks happens only at the 5-second intervals of the lock monitor.

This LOCK_MONITOR thread checks for deadlocks by inspecting the list of waiting locks for any cycles, which indicate a circular relationship between processes holding locks and processes waiting for locks. Typically, the process chosen as the victim is the process that has just requested a lock and initiated the check for deadlocks. If a deadlock is encountered during the normal execution of the lock monitor, the first process whose termination will break the deadlock cycle is typically chosen as the victim. However, certain operations are marked as golden, or unkillable. For example, a process involved in rolling back a transaction cannot be chosen as a deadlock victim because the changes being rolled back could be left in an indeterminate state, causing data corruption.

In SQL Server 6.5, the lock manager checked for deadlocks every time a process began waiting for a lock resource. This provided rapid deadlock detection, but it wasted a lot of resources looking for deadlocks even in systems or situations in which they were rare. In earlier SQL Server releases, other algorithms were used to choose the deadlock victim (such as choosing the process that had consumed fewer CPU cycles). Those algorithms made a noble attempt at fairness, but they often resulted in another deadlock because yet another process might have been queued up for the resource that was part of the deadlock circular chain of lock requests. The result would be a chain reaction.

Using the SET DEADLOCK_PRIORITY LOW | NORMAL statement, you can make a process sacrifice itself as the victim if a deadlock is detected. If a process has a deadlock priority of LOW, it terminates when a deadlock is detected even if it is not the process that closed the loop. However, there is no counterpart SET option to set a deadlock priority to HIGH. As much as you might want your own processes to always come out the winner in a deadlock situation, this feature has not yet been implemented in SQL Server.

NOTE   The lightweight latches and spinlocks used internally do not have deadlock detection services. Instead, deadlocks on latches and spinlocks are avoided rather than resolved. Avoidance is achieved via strict programming guidelines used by the SQL Server development team. These lightweight locks must be acquired in a hierarchy, and a process must not have to wait for a regular lock while holding a latch or spinlock. For example, one coding rule is that a process holding a spinlock must never directly wait for a lock or call another service that might have to wait for a lock, and a request can never be made for a spinlock that is higher in the acquisition hierarchy. By establishing similar guidelines for your development team for the order in which SQL Server objects are accessed, you can go a long way toward avoiding deadlocks in the first place.

In the example in Figure 13-1, the cycle deadlock could have been avoided if the processes had decided on a protocol beforehand—for example, if they had decided to always access the customer table first and the parts table second. Then one of the processes would get the initial exclusive lock on the table being accessed first, and the other process would wait for the lock to be released. One process waiting for a lock is normal and natural. (Remember, waiting is not a deadlock.)

You should always try to have a standard protocol for the order in which processes access tables. If you know that the processes might need to update the row after reading it, they should initially request an update lock, not a shared lock. If both processes request an update lock rather than a shared lock, the process that is granted an update lock is assured that the lock can later be promoted to an exclusive lock. The other process requesting an update lock has to wait. The use of an update lock serializes the requests for an exclusive lock. Other processes needing only to read the data can still get their shared locks and read. Since the holder of the update lock is guaranteed an exclusive lock, the deadlock is avoided. We'll look in more detail at the compatibility of locks later in this chapter; additional information on deadlocks is presented in Chapter 14.

By the way, the time that your process holds locks should be minimal so other processes don't wait too long for locks to be released. Although you don't usually invoke locking directly, you can influence locking by keeping transactions as short as possible. For example, don't ask for user input in the middle of a transaction. Instead, get the input first and then quickly perform the transaction.

Locks and Memory
Locks are not on-disk structures—you won't find a lock field directly on a data page or a table header—because it would be too slow to do disk I/O for locking operations. Locks are internal memory structures-they consume part of the memory used for SQL Server. Each locked data resource (a row, index key, page, or table) requires 32 bytes of memory to keep track of the database, the type of lock, and the information describing the locked resource. Each process holding a lock also must have a lock owner block of 32 bytes. Sometimes a single transaction can have multiple lock owner blocks; a scrollable cursor sometimes uses several. Also, one lock can have many lock owner blocks, as in the case of a shared lock. Finally, each process waiting for a lock has a lock waiter block of another 32 bytes.

NOTE   In this context, we use the term "process" to refer to a SQL Server subtask. Every user connection is referred to as a process, as are the checkpoint manager, the lazywriter, the log writer, and the lock monitor. But these are only subtasks within SQL Server, not processes from the perspective of Windows NT, which considers the entire SQL Server engine to be a single process with multiple threads.

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). We already mentioned shared, exclusive, and update locks, and we'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 we'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 they prevent the conversion deadlock situation presented in Figure 13-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 has 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 automatically requests a bulk update lock. 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:

BEGIN TRAN
SELECT * FROM bigtable 
UPDATE bigtable
    SET col = 0
    WHERE keycolumn = 100

Assuming 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 13-1 shows most of the lock modes, as well as the abbreviations used in the output of sp_lock.

	Figure 13-3 Click here.

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 13-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.

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

Key Locks
SQL Server 7 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:

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. Earlier versions of 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 7 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 employee table. We are in TRANSACTION ISOLATION LEVEL SERIALIZABLE and we issue this SELECT statement:

SELECT * 
FROM members
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 keyrange 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 ior 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 give 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 each process holds a lock on at least one database. These are always shared locks and are used by SQL Server for determining when a database is in use. 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.

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 that 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 13-2 shows all the lock resources, the abbreviations used in the output of sp_lock, and the information used to define the actual resource locked.

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 held until the end of the transaction unless it has been promoted to an exclusive lock, in which case the exclusive lock 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. We'll discuss query hints for locking and for other purposes in Chapter 14.

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 above. 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 of you who think GUIs are for wimps can appreciate SQL Server Enterprise Manager's view of locking.)

The following examples show what each of the lock types and modes discussed earlier look like when reported by the sp_lock procedure. Note that the call to the sp_lock procedure is preceded by the keyword EXECUTE, which is required when the call to a stored procedure is not the first thing in a batch. Note also that the sp_lock procedure is given an argument of @@spid, which refers to the process ID of the current process (the server process ID). We don't want to see all the locks in the system, only those held by our 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_lock @@spid
COMMIT TRAN

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ----- 
11     1      0           0      DB                    S        GRANT
11     2      0           0      DB                    S        GRANT
11     5      0           0      DB                    S        GRANT
11     1      117575457   0      TAB                   IS       GRANT

Every time we run the sp_lock procedure, we acquire locks in the master database to generate the output to be displayed. If you look at the dbid column in the output, you'll see locks in database 1 (master) and database 2 (tempdb). We won't show those locks in the output for the rest of the examples. If we ignore the locks in master and tempdb, we'll see that the only lock in the pubs database is the session-level database 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_lock 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_lock @@spid
COMMIT TRAN

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ------
11     5      117575457   2      PAG  1:123            IS       GRANT
11     5      117575457   1      PAG  1:96             IS       GRANT
11     5      0           0      DB                    S        GRANT
11     5      117575457   2      KEY  (d5f329a7dcdc)   S        GRANT
11     5      117575457   2      KEY  (4c62318cf11f)   S        GRANT
11     5      117575457   1      KEY  (3dc1b1ecb5be)   S        GRANT
11     5      117575457   1      KEY  (37fdb5efbcbe)   S        GRANT
11     5      117575457   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. You can tell the two 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_lock @@spid
COMMIT TRAN

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ------
11     5      117575457   2      PAG  1:123            IS       GRANT
11     5      117575457   1      PAG  1:96             IS       GRANT
11     5      0           0      DB                    S        GRANT
11     5      117575457   2      KEY  (d5f329a7dcdc)   IS-S     GRANT
11     5      117575457   2      KEY  (4c62318cf11f)   IS-S     GRANT
11     5      117575457   1      KEY  (3dc1b1ecb5be)   IS-S     GRANT
11     5      117575457   1      KEY  (37fdb5efbcbe)   IS-S     GRANT
11     5      117575457   2      KEY  (d5968ed3b619)   IS-S     GRANT
11     5      117575457   0      TAB                   IS       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 IS-S indicates a key range lock in addition to the lock on the key itself. The first part (IS) 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. There are three key locks in the nonclustered index on au_lname (IndId = 2) because three different ranges need to be locked. SQL Server must lock the range from the key preceding the first Ringer in the table 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 into the table. For example, we could not insert an author with the last name Pattin 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_lock @@spid
COMMIT TRAN

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ------
11     5      117575457   1      PAG  1:96             IX       GRANT
11     5      0           0      DB                    S        GRANT
11     5      117575457   1      KEY  (3dc1b1ecb5be)   X        GRANT
11     5      117575457   1      KEY  (37fdb5efbcbe)   X        GRANT
11     5      117575457   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. We discussed 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_lock 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.

Example 5: 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_lock @@spid
COMMIT TRAN

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ------
7      5      0           0      DB   [BULK-OP-LOG]    S        GRANT
7      5      0           0      DB                    S        GRANT
7      5      0           0      DB   [BULK-OP-DB]     S        GRANT
7      5      1           0      TAB                   IX       GRANT
7      5      3           0      TAB                   IX       GRANT
7      5      2           0      TAB                   IX       GRANT
7      5      0           0      PAG  1:199            X        GRANT
7      5      0           0      PAG  1:198            X        GRANT
7      5      0           0      PAG  1:197            X        GRANT
7      5      0           0      PAG  1:196            X        GRANT
7      5      0           0      PAG  1:195            X        GRANT
7      5      0           0      PAG  1:194            X        GRANT
7      5      0           0      PAG  1:193            X        GRANT
7      5      0           0      EXT  1:192            X        GRANT
7      5      0           0      PAG  1:166            X        GRANT
7      5      0           0      PAG  1:165            X        GRANT
7      5      3           2      KEY  (0693cc100b75)   X        GRANT
7      5      3           2      KEY  (3790f0cf7f55)   X        GRANT
7      5      2           1      KEY  (0257f4438466)   X        GRANT
7      5      3           2      KEY  (242ad7320fa2)   X        GRANT
7      5      3           2      KEY  (e794778e397a)   X        GRANT
7      5      1061578820  0      TAB                   Sch-M    GRANT
7      5      1           2      KEY  (269dc7159f33)   X        GRANT
7      5      3           1      KEY  (024e7788d08c)   X        GRANT
7      5      3           1      KEY  (02567f88d08c)   X        GRANT
(etc.)

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 extent (EXT) locks. While the table is built, the extents are not marked as belonging to the table; you can see that the ObjId is 0. In the output above, 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 6: 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_lock @@spid
ROLLBACK TRAN

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ------
13     1      0           0      DB                    S        GRANT
13     2      0           0      DB                    S        GRANT
13     5      0           0      DB                    S        GRANT
13     5      1077578877  0      RID  1:184:0          X        GRANT
13     5      1077578877  0      PAG  1:184            IX       GRANT
13     5      1077578877  0      TAB                   IX       GRANT
13     1      117575457   0      TAB                   IS       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.

Lock Compatibility
Two locks are compatible if one lock can be granted while another lock on the same object by a different process is outstanding. On the other hand, if a lock requested for an object is not compatible with a lock currently being held, the requesting connection must wait for the lock. For example, if a shared page lock exists on a page, another process requesting a shared page lock for the same page is granted the lock because the two lock types are compatible. But a process that requests an exclusive lock for the same page is not granted the lock because an exclusive lock is not compatible with the shared lock already held. Table 13-3 summarizes the compatibility of locks in SQL Server.

Lock compatibility comes into play between locks on different resources, such as table locks and page locks. A table and a page obviously represent an implicit hierarchy since a table is made up of multiple pages. If an exclusive page lock is held on one page of a table, another process cannot get even a shared table lock for that table. This hierarchy is protected using intent locks. A process acquiring an exclusive page lock, update page lock, or intent exclusive page lock first acquires an intent exclusive lock on the table. This intent exclusive table lock prevents another process from acquiring the shared table lock on that table. (Remember that intent exclusive and shared locks are not compatible.)

Similarly, a process acquiring a shared row lock must first acquire an intent shared lock for the table, which prevents another process from acquiring an exclusive table lock. Or if the exclusive table lock already exists, the intent shared lock is not granted and the shared page lock has to wait until the exclusive table lock is released. Without intent locks, process A can lock a page in a table with an exclusive page lock and process B can place an exclusive table lock on the same table and hence think that it has a right to modify the entire table, including the page that process A has exclusively locked.

NOTE   At the risk of stating the obvious, lock compatibility is an issue only when the locks affect the same object. For example, two or more processes can each hold exclusive page locks simultaneously as long as the locks are on different pages or different tables.

Even if two locks are compatible, the requester of the second lock might still have to wait if an incompatible lock is waiting. For example, suppose that process A holds a shared page lock. Process B requests an exclusive page lock and must wait because the shared page lock and the exclusive page lock are not compatible. Process C requests a shared page lock that is compatible with the shared page already outstanding to process A. However, the shared page lock cannot be immediately granted. Process C must wait for its shared page lock because process B is ahead of it in the lock queue with a request (exclusive page) that is not compatible.

By examining the compatibility of locks not only to those processes granted but also to those processes waiting, SQL Server prevents lock starvation, which occurs when requests for shared locks keep overlapping so that the request for the exclusive lock can never be granted.

Bound Connections
Remember that the issue of lock contention applies only between different processes (also called connections). A process holding locks on a resource does not lock itself from the resource-only other processes are prevented access. But any other process (or connection to SQL Server) can actually execute as the same application and user. It is common for applications to have more than one connection to SQL Server. Every such connection is treated as an entirely different SQL Server process, and by default no sharing of the "lock space" occurs between connections, even if they belong to the same user and the same application. (Again, in this context, "process" means a SQL Server subtask, not a Windows NT process.)

However, two different connections can share a lock space and hence not lock each other out. This capability is known as a bound connection. With a bound connection, the first connection asks SQL Server to give out its bind token. The bind token is passed by the application (using a client-side global variable, a shared memory, or another method) for use in subsequent connections. The bind token acts as a "magic cookie" so that those other connections can share the lock space of the original connection. Locks held by bound connections do not lock each other. (The sp_getbindtoken and sp_bindsession system stored procedures get and use the bind token.)

Bound connections are especially useful if you are writing an extended stored procedure, which is a function written in your own DLL, and that extended stored procedure needs to call back into the database to do some work. Without a bound connection, the extended stored procedure collides with its own calling process's locks. When multiple processes share a lock space and a transaction space by using bound connections, a COMMIT or ROLLBACK affects all the participating connections.

Here's an example of using bound connections between two different windows in SQL Server's Query Analyzer. Since we don't have a controlling application to declare and store the bind token in a client-side variable, we have to actually copy it from the first session and paste it into the second. So, in your first query window, you execute this batch:

DECLARE @token varchar(30)
EXEC sp_getbindtoken @token
SELECT @token
GO

This should return something like the following:

---------------------------------------- 
dPe---5---.?j0U<_WP?1HMK-3/D8;@1

Normally, you wouldn't have to look at this messy string; your application would just store it and pass it on without your ever having to see it. But for a quick example using the Query Analyzer, it's necessary to actually see the value. You use your keyboard or mouse to select the token string that you received and use it in the following batch in a second Query Analyzer window:

EXEC sp_bindsession 'dPe---5---.?j0U<_WP?1HMK-3/D8;@1'
GO

Now go back to the first query window and begin a transaction that locks some data. You can use something like this:

USE pubs
BEGIN TRAN
UPDATE titles
SET price = $100
GO

This should exclusively lock every row in the titles table. Now go to the second query window and select from the locked table:

SELECT title_id, price FROM titles
GO

You should be able to see all the $100 prices in the titles table, just as if you were part of the same connection as the first query. Besides sharing lock space, the bound connection also shares transaction space. You can execute a ROLLBACK TRAN in the second window even though the first one began the transaction. If the first connection tries to then issue a ROLLBACK TRAN, it gets this message:

Server: Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Row-Level vs. Page-Level Locking
The debate over whether row-level locking is better than page-level locking or vice versa has been one of those near-religious wars and warrants a few comments here. Although some people would have you believe that one is always better, it's not really that simple.

Prior to SQL Server version 7, the smallest unit of data that SQL Server could lock was a page. Even though many people argued that this was unacceptable and it was impossible to maintain good concurrency while locking entire pages, many large and powerful applications were written and deployed using only page-level locking. If they were well designed and tuned, concurrency was not an issue, and some of these applications supported hundreds of active user connections with acceptable response times and throughput. However, with the change in page size from 2 KB to 8 KB for SQL Server 7, the issue has become more critical. Locking an entire page means locking four times as much data as in previous versions. SQL Server 7 implements full row-level locking, so any potential problems due to lower concurrency with the larger page size should not be an issue. However, locking isn't free. Considerable resources are required to manage locks. Recall that a lock is an in-memory structure of about 32 bytes, with another 32 bytes for each process holding the lock and each process waiting for the lock. If you need a lock for every row and you scan a million rows, you need more than 30 MB of RAM just to hold locks for that one process.

Beyond the memory consumption issues, locking is a fairly processing-intensive operation. Managing locks requires substantial bookkeeping. (Recall that, internally, SQL Server uses a lightweight mutex called a spinlock to guard resources, and it uses latches-also lighter than full-blown locks-to protect non-leaf-level index pages. These performance optimizations avoid the overhead of full locking.) If a page of data contains 50 rows of data, all of which will be used, it is obviously more efficient to issue and manage one lock on the page than to manage 50. That's the obvious benefit of page locking-a reduction in the number of lock structures that must exist and be managed.

If two different processes each need to update a few separate rows of data and some of the rows needed by each process happen to exist on the same page, one process must wait until the page locks of the other process are released. If, in this case, you use row-level locking instead of page-level locking, the other process does not have to wait. The finer granularity of the locks means that no conflict occurs in the first place because each process is concerned with different rows. That's the obvious benefit of row-level locking. Which of these obvious benefits wins? Well, the decision isn't clear cut, and it depends on the application and the data. Each type of locking can be shown to be superior for different types of applications and usage.

The stored procedure sp_indexoption lets you manually control the unit of locking within an index. It also lets you disallow page locks or row locks within an index. Since these options are available only for indexes, there is no way to control the locking within the data pages of a heap. (But remember that if a table has a clustered index, the data pages are part of the index and are affected by the sp_indexoption setting.) The index options are set for each table or index individually. Two options, AllowRowLocks and AllowPageLocks, are both set to TRUE initially for every table and index. If both of these options are set to FALSE for a table, only full table locks are allowed.

Lock Escalation
SQL Server automatically escalates row, key, or page locks to coarser table locks as appropriate. This escalation protects system resources-it prevents the system from using too much memory for keeping track of locks-and increases efficiency. For example, after a query acquires many row locks, the lock level can be escalated to a table lock. If every row in a table must be visited, it probably makes more sense to acquire and hold a single table lock than to hold many row locks. A single table lock is acquired and the many row locks are released. This escalation to a table lock reduces locking overhead and keeps the system from running out of locks. Recall that a lock is a memory structure. Because there is a finite amount of memory, there must be a finite number of locks. You can configure SQL Server for the maximum number of locks your system is expected to need, but the default is for the locks configuration parameter to be tuned automatically by SQL Server.

As mentioned earlier, SQL Server determines at runtime whether to lock rows, pages, or the entire table. The locking of rows (or keys) is heavily favored if at all possible. The type of locking chosen is based on the number of rows and pages to be scanned, the number of rows on a page, the isolation level in effect, the update activity going on, the number of users on the system needing memory for their own purposes, and so on. Perhaps the best of both worlds would be to "deescalate" locks-that is, to start at a coarser level of locking and change to more granular locks if and only if there is a conflict for the coarse lock.

For example, suppose you have two processes that each update a few rows. Process A might start with page locking. If no other process requests a lock of the same page, no contention occurs, so the additional overhead of doing locking for every row does not happen. But if process B asks for a lock on one of the pages locked by process A, those held locks can be deescalated to row locks for only the rows that are actually needed. After failing to obtain the page-level lock, process B might then try to get a lock on only the specific rows it needs rather than on the entire page. If the rows needed are not the same between the processes, there is no contention and each gets its own row locks. But if there is no contention for rows, the reduced overhead of locking only at the page level can be realized. You can begin the locking at an even coarser level, such as at the table level, and deescalate as needed to a page, and then further to a row, and conceptually even further to perhaps a column level, although this might be stretching the model.

A deescalation locking strategy is being considered for future releases. As of version 7, SQL Server uses predominantly row-level locking. As mentioned, sometimes page locks or a table lock are chosen initially, or if too many individual row locks are acquired, SQL Server dynamically escalates those to a table lock. In version 7, lock escalation converts many individual row or page locks to a table lock; escalation never converts row locks to page locks. Lock deescalation is not implemented.

Locking Hints and Trace Flags
Just as you can specify hints on queries to direct the optimizer to choose a certain index or strategy in its query plan, you can specify hints for locking. For example, if you know that your query will scan so many rows that its page locks will escalate to table locks, you can direct the query to use table locks in the first place, which is more efficient.

We'll look at locking hints and locking contention issues in detail in Chapter 14. For now, armed with the knowledge of locking you've gained here, you can observe the locking activity of your system to understand how and when locks occur. Trace flag 1204 provides detailed information about deadlocks, and this information can help you understand why locks are occurring and how to change the order of access to objects to reduce them. Trace flag 1200 provides detailed locking information as every request for a lock is made. (But its output is voluminous.) You'll see details about these trace flags in Chapter 14.

Summary
SQL Server lets you manage multiple users simultaneously and ensure that transactions observe the properties of the chosen isolation level. Locking guards data and the internal resources that make it possible for a multiple-user system to operate like a single-user system. In this chapter, we looked at the locking mechanisms in SQL Server, including full locking for data and leaf-level index pages and lightweight locking mechanisms for internally used resources.