Database Reference
In-Depth Information
Now rerun the updated bonus query and the new employee query. Figure
20-8
shows the resultant output of
sys.dm_tran_locks
for the
PayBonus
transaction.
Figure 20-8.
Output from sys.dm_tran_locks showing range locks granted to the serializable transaction with no index
on the WHERE clause column
Once again, the range lock on the last possible row (
KEY = ffffffffffff
) in the new clustered index, as shown
in Figure
20-8
, will block the addition of any new row to the table. I will discuss the reason behind this extensive
locking later in the chapter in the “Effect of Indexes on the Serializable Isolation Level” section.
As you've seen, the Serializable isolation level not only holds the share locks until the end of the transaction
like the Repeatable Read isolation level but also prevents any new row from appearing in the data set by holding
range locks. Because this increased blocking can hurt database concurrency, you should avoid the Serializable
isolation level. If you have to use Serializable, then be sure you have good indexes and queries in place to optimize
performance in order to minimize the size and length of your transactions.
Snapshot
Snapshot isolation is the second of the row-versioning isolation levels available in SQL Server since SQL Server
2005. Unlike Read Committed Snapshot isolation, Snapshot isolation requires an explicit call to
SET TRANSACTION
ISOLATION LEVEL
at the start of the transaction. It also requires setting the isolation level on the database. Snapshot
isolation is meant as a more stringent isolation level than the Read Committed Snapshot isolation. Snapshot isolation
will attempt to put an exclusive lock on the data it intends to modify. If that data already has a lock on it, the snapshot
transaction will fail. It provides transaction-level read consistency, which makes it more applicable to financial-type
systems than Read Committed Snapshot.
Effect of Indexes on Locking
Indexes affect the locking behavior on a table. On a table with no indexes, the lock granularities are
RID
,
PAG
(on
the page containing the
RID
), and
TAB
. Adding indexes to the table affects the resources to be locked. For example,
consider the following test table with no indexes:
IF (SELECT OBJECT_ID('dbo.Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;
GO
