Locking reaches new levels in 7.0
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 TRANSACTION
SELECT * FROM titles
WHERE price BETWEEN $10 AND $15
GO
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.
Prev. page
[1]
2
next page 
