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.
 
Search WWH ::




Custom Search