SQL Server 7.0 Locking

As I mentioned last month (Inside SQL Server, "Transaction Isolation Levels," June 1999), one of the most eagerly anticipated new features of SQL Server 7.0 is its full support for row-level locking. This month I look at SQL Server 7.0's locking modes, the resources that SQL Server can lock, and a simple tool for observing the active locks.

Locking and Isolation Levels

SQL Server's default isolation level is Committed Read, but you can override this level within your application. The most straightforward way to override it is to use the SET command:

SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED |REPEATABLE READ | SERIALIZABLE ]

Previous releases of SQL Server treated Repeatable Read and Serializable as synonymous isolation levels because SQL Server's locking mechanisms had no way to distinguish between the two. For example, suppose you issued the following query while in the Repeatable Read mode:

BEGIN TRANSACTIONSELECT * FROM titlesWHERE price BETWEEN $10 AND $15GO

Repeatable Read guaranteed that the rows read were not updated until this transaction completed. In other words, Repeatable Read guaranteed that, if reread, the same rows returned the same values. SQL Server could not lock the individual rows that met the criteria; it had to lock the page, or even the table. That locking essentially treated the transaction as if it were in the Serializable mode because the page or table lock prevented another process from inserting a new row with, say, a price of $12.

SQL Server 7.0 supports the true Repeatable Read isolation level. True Repeatable Read does not prevent this kind of insertion; it prevents only changes to the data read. Only the Serializable isolation level prevents inserts of new rows into a range. Thus, true Repeatable Read isolation was not possible until SQL Server 7.0 introduced row-level locking.

Lock Modes

SQL Server uses several locking modes, including shared locks, exclusive locks, update locks, and intent locks, to achieve the four required modes of ANSI isolation. The lock mode specifies how restrictive the lock is and what other actions are possible while the lock is held. These same lock modes were available in previous releases.

Shared locks. SQL Server acquires shared locks automatically when it reads data. A table, page, index key, or individual row can hold shared locks. In addition, to support Serializable transaction isolation, SQL Server can place locks on ranges of index keys. 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 process holding the shared lock). Shared locks are usually released as soon as SQL Server reads the data. However, you can use a different transaction isolation level, either Repeatable Read or Serializable, to change this behavior.

Exclusive locks. SQL Server automatically acquires exclusive locks on data when an insert, update, or delete operation is modifying the data. Only one process at a time can hold an exclusive lock on a particular data resource, and exclusive locks remain until the end of the transaction. Thus, the changed data is usually unavailable to any other process until the current transaction either commits or rolls back. If you use the Read Uncommitted transaction isolation level, other processes can read exclusively locked data.

Update locks. Update locks are not really a separate kind of lock, but rather are a hybrid of shared and exclusive locks. A transaction acquires this kind of lock when SQL Server executes a data-modification operation but first needs to search the table to find the resource to modify. I'll discuss the details of update locks in a future column.

Intent locks. Intent locks are not a separate mode of locking. The term intent lock is a qualifier to the modes just 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 (i.e., at the row, page, or table level), you need a mechanism that signals whether a component of a resource is already locked. For example, if one process attempts to lock a table, SQL Server must be able to determine whether a row or a page of that table is already locked. Intent locks serve that purpose.

Lock Granularity

SQL Server can lock user data resources at the row, page, or table level. SQL Server can also lock index keys and ranges of index keys. Be aware that if the table has a clustered index, the data rows are at the leaf level of the clustered index. SQL Server locks leaf-level data rows with key locks instead of row locks. (Future columns will discuss index structures in detail.)

The Syslockinfo table keeps track of each lock by storing information about the type of resource locked (e.g., row, key, page), the mode of the lock, and an identifier for the specific resource. When a process requests a lock, SQL Server compares that lock 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 have an exact match.) However, suppose one process has a row exclusively locked in the Authors table and another process tries to get a lock on the entire Authors table. Because rows and tables are two different resources, SQL Server would not find an exact match unless Syslockinfo contains intent locks signifying that a row in the table is already exclusively locked. Suppose further that the process with the exclusive lock on a row of the Authors table also has an intent-exclusive lock on the page containing the row and an 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.0 supports two kinds of key locks, depending on the isolation level of the current transaction. If the isolation level is Read Committed or Repeatable Read, SQL Server attempts to lock the index keys it accesses while processing the query. If the table has a clustered index, SQL Server acquires key locks because the data rows are at the leaf level of the index. If the table is a heap (no clustered index), SQL Server might acquire key locks for the nonclustered indexes and row locks for the data.

If the isolation level is Serializable, you have a special situation. If you scan a range of data within a transaction, you need to lock enough of the table to ensure that no one can insert a new value into the range you scanned, because that value would then appear as a phantom if you reissued your query. For example, suppose you have an index on the lastname field in the Employee table. You are in the Serializable isolation level, and you issue this SELECT statement within a transaction:

SELECT *FROM employeeWHERE last_name BETWEEN 'MacDougall' AND 'McDougall'

If MacAndrews, MacWorter, and McKenna are sequential leaf-level index keys in the table, the MacWorter and McKenna keys each acquire a key range lock. A key range lock implies a locked range that starts with the key that precedes the locked key and ends with the locked key. You can't insert data that would fall within this range. So, in this example, you have one key range lock that starts with MacAndrews and ends with MacWorter and another key range lock that starts with MacWorter and ends with McKenna. These two key range locks prevent you from inserting values greater than MacAndrews and less than or equal to MacWorter and values greater than MacWorter and less than or equal to McKenna. In other words, these two key range locks prevent you from inserting MacOwen or McBride, which are in the range that the WHERE clause specifies. However, they also prevent you from inserting MacBryde, even though MacBryde is not in the specified range. Key range locks are not perfect, but they do give much greater concurrency than locking a page or a table, which were the only possibilities in previous versions of SQL Server.

Observing Locks

To see both the locks currently outstanding in the system and those applied for, you can look at the Syslockinfo system table or execute the system stored procedure sp_lock. (The Syslockinfo table is not really a system table. SQL Server does not maintain the table on disk because it does not maintain locks on disk. Rather, Syslockinfo appears in table format based on the Lock Manager's current accounting of locks each time a process queries Syslockinfo.) Another way to watch locking activity is with SQL Enterprise Manager's excellent graphical representation of locking status.

The following examples show you how to use the sp_lock procedure to view the types of locks in each transaction isolation level. In the sp_lock code, note that the keyword EXECUTE precedes the call to the sp_lock procedure; this keyword is required when the call to a stored procedure is not the first item in a batch. Note also that the sp_lock procedure takes the argument of @@spid, which refers to the process ID of the current process (server process ID). You don't want to see all the locks in the system, only those your process holds.

The terminology that the sp_lock output uses can be confusing. People often use the terms lock type and lock mode interchangeably to specify whether the lock is shared or exclusive. But in the sp_lock output, the Type column identifies the type of resource locked, or the granularity of the lock. The value for the locked resource can show up in different places in the sp_lock output. If the locked data is a database, the database ID is in the Dbid column. If the locked data is a table, the table ID is in the ObjId column. If the locked data is a page, a two-part ID consisting of the file ID and the page ID appears in the Resource column. So, for example, Page 1:123 means page 123 of the first file. If the locked data is a row, a three-part identifier consisting of the file ID, the page ID, and the slot number on the page appears in the Resource column.

Database locks in the Read Committed isolation level. Every time you run the sp_lock procedure, you acquire locks in the master database that generate the output to be displayed. If you look at the Dbid column in the output in Listing 1, you see locks in database 1 (Master) and database 2 (Tempdb). In addition, SQL Server acquires special session-level database locks in whichever database is current. You can see this locking level in the third row of the results, where the Dbid is 5 and the type of lock is DB. I won't show any of the database-level locks in the rest of the examples. You have no locks on the Authors table at this point because the batch was performing only SELECT operations that acquired shared locks. By default, the shared locks release as soon as SQL Server reads the data, so by the time sp_lock executes, the locks are no longer held.

Key and shared locks in the Repeatable Read isolation level. Because the Authors table has a clustered index, the rows of data are all index rows in the leaf level. As Listing 2 shows, the output marks the locks on the individual rows as key locks instead of row locks. Key locks are also 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; the nonclustered index rows have an IndId value of 2. (Your own nonclustered indexes may have an IndId value of any value between 2 and 250.) Because the transaction isolation level is Repeatable Read, the shared locks remain until the transaction finishes. Note that two rows and two index rows have shared (S) locks. The data page, index page, and table have intent-shared (IS) locks.

Key range locks in the Serializable isolation level. The locks in the Serializable isolation level in Listing 3 are similiar to those in the Repeatable Read isolation level in Listing 2. The main difference is in the mode of the lock. The two-part mode IS-S specifies a key range lock in addition to the lock on the key. The first part (IS) is the lock on the range of keys between (and including) the key holding the lock (S) and the previous key in the index. The nonclustered index on au_lname (Indid = 2) contains three key range locks because SQL Server needs to lock three different key ranges.

Exclusive locks in the Read Committed isolation level. As Listing 4 shows, the two rows in the leaf level of the clustered index have exclusive (X) locks. The page and the table have intent-exclusive (IX) locks. Although SQL Server acquires update locks when looking for the rows to update, these locks escalate to exclusive locks before the update operation is actually carried out. By the time the sp_lock procedure runs, the update locks are gone.

Row locks in the Read Committed isolation level. The newTitles table in Listing 5 does not contain indexes, so the lock on the row meeting the criterion is an exclusive (X) lock on the row (RID). As expected, IX locks are taken on the page and the table.

More Locking Details

The topic of locking is too broad to include all its details in one column. In future columns, I'll talk about special locks held during object creation and bulk-copy operations, and the special update locks mentioned earlier. I'll also discuss further details of the relationship between indexes and locking, showing you some additional tools for monitoring locks held. Finally, I'll talk about mechanisms available besides setting the transaction isolation level for controlling the locking behavior of SQL Server 7.0.