Database Reference
In-Depth Information
Key-range Mode
The Key-Range mode is applicable only while the isolation level is set to Serializable (you'll learn more about
transaction isolation levels in the later “Isolation Levels” section). The Key-Range locks are applied to a series, or
range, of key values that will be used repeatedly while the transaction is open. Locking a range during a serializable
transaction ensures that other rows are not inserted within the range, possibly changing result sets within the
transaction. The range can be locked using the other lock modes, making this more like a combined locking mode
rather than a distinctively separate locking mode. For the Key-Range lock mode to work, an index must be used to
define the values within the range.
Lock Compatibility
SQL Server provides isolation to a transaction by preventing other transactions from accessing the same resource in
an incompatible way. However, if a transaction attempts a compatible task on the same resource, then, to increase
concurrency, it won't be blocked by the first transaction. SQL Server ensures this kind of selective blocking by
preventing a transaction from acquiring an incompatible lock on a resource held by another transaction. For example,
an (S) lock acquired on a resource by a transaction allows other transactions to acquire an (S) lock on the same
resource. However, an (Sch-M) lock on a resource by a transaction prevents other transactions from acquiring any
lock on that resource.
Isolation Levels
The lock modes explained in the previous section help a transaction protect its data consistency from other
concurrent transactions. The degree of data protection or isolation a transaction gets depends, not only on the
lock modes but also on the isolation level of the transaction. This level influences the behavior of the lock modes.
For example, by default an (S) lock is released immediately after the data is read; it isn't held until the end of the
transaction. This behavior may not be suitable for some application functionality. In such cases, you can configure the
isolation level of the transaction to achieve the desired degree of isolation.
SQL Server implements six isolation levels, four of them as defined by ISO:
•
Read Uncommitted
•
Read Committed
•
Repeatable Read
•
Serializable
Two other isolation levels provide row versioning, which is a mechanism whereby a version of the row is created
as part of data manipulation queries. This extra version of the row allows read queries to access the data without
acquiring locks against it. The extra two isolation levels are as follows:
•
Read Committed Snapshot (actually part of the Read Committed isolation)
•
Snapshot
The four ISO isolation levels are listed in increasing order of degree of isolation. You can configure them at
either the connection or query level by using the
SET TRANSACTION ISOLATION LEVEL
statement or the locking hints,
respectively. The isolation level configuration at the connection level remains effective until the isolation level is
reconfigured using the
SET
statement or until the connection is closed. All the isolation levels are explained in the
sections that follow.
