Database Reference
In-Depth Information
The following locks are acquired by the transaction:
•
An (IX) lock on the table
•
An (IX) lock on the page containing the clustered index row
•
An (X) lock on the clustered index row within the table or clustered index
The locks on the clustered index row and the leaf page are actually the locks on the data row and data page, too,
since the data pages and the leaf pages are the same. Thus, the clustered index reduced the locking overhead on the
table compared to the nonclustered index.
Reduced locking overhead of a clustered index is another benefit of using a clustered index over a heap.
Effect of Indexes on the Serializable Isolation Level
Indexes play a significant role in determining the amount of blocking caused by the Serializable isolation level.
The availability of an index on the
WHERE
clause column (that causes the data set to be locked) allows SQL Server
to determine the order of the rows to be locked. For instance, consider the example used in the section on the
Serializable isolation level. The
SELECT
statement uses a filter on the
GroupID
column to form its data set, like so:
DECLARE @NumberOfEmployees INT;
SELECT @NumberOfEmployees = COUNT(*)
FROM dbo.MyEmployees WITH (HOLDLOCK)
WHERE GroupID = 10;
A clustered index is available on the
GroupID
column, allowing SQL Server to acquire a (RangeS-S) lock on the
row to be accessed and the next row in the correct order.
If the index on the
GroupID
column is removed, then SQL Server cannot determine the rows on which the range
locks should be acquired since the order of the rows is no longer guaranteed. Consequently, the
SELECT
statement
acquires an (IS) lock at the table level instead of acquiring lower-granularity locks at the row level, as shown
in Figure
20-13
.
Figure 20-13.
Output from sys.dm_tran_locks showing the locks granted to a SELECT statement with no index on the
WHERE clause column
By failing to have an index on the filter column, you significantly increase the degree of blocking caused by the
Serializable isolation level. This is another good reason to have an index on the
WHERE
clause columns.
Capturing Blocking Information
Although blocking is necessary to isolate a transaction from other concurrent transactions, sometimes it may rise
to excessive levels, adversely affecting database concurrency. In the simplest blocking scenario, the lock acquired
by a session on a resource blocks another session requesting an incompatible lock on the resource. To improve
concurrency, it is important to analyze the cause of blocking and apply the appropriate resolution.
