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 employee
WHERE 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.