Database Reference
In-Depth Information
Figure 20-8.
Selecting data in the REPEATABLE READ isolation level
Even if the query returned just a single row, you see that shared (S) locks have been escalated to the table level.
Let's take a look at the execution plan shown in Figure
20-9
.
Figure 20-9.
Execution plan of the query
There are no indexes on the
OrderNum
column, and SQL Server uses the
Clustered Index Scan
operator. Even
if the query returned just a single row, it acquired and held shared (S) locks on all the rows it read. As a result,
lock escalation had been triggered. If you add the index on the
OrderNum
column, it changes the execution plan to
Nonclustered Index Seek
. Only one row is read, very few row- and page-level locks are acquired and held, and lock
escalation is not needed.