Database Reference
In-Depth Information
As you can see, from a concurrency standpoint, the row-level locking is perfect. Sessions do not block each other
as long as they do not compete for the same rows. At the same time, keeping the large number of the locks is memory
intensive, and memory is one of the most precious resources in SQL Server, especially with non-Enterprise editions in
which there is a limitation on the amount of memory they can utilize. Moreover, there is the overhead of maintaining
the locking information. In our example, SQL Server needs to keep more than ten million two hundred thousand locks
using almost two gigabytes of RAM. This number includes the row-level shared (S) locks, as well as the page-level
intent shared (IS) locks.
Let's see what happens if we enable lock escalation with
ALTER TABLE SET (LOCK_ESCALATION=TABLE)
command
and run the code shown in Figure
20-3
.
Figure 20-3.
Number of locks and concurrency with lock escalations enabled
SQL Server replaces the row- and page-level locks with the object shared (S) lock. Although it is great from
the memory usage standpoint—there is just a single lock to maintain—it affects concurrency. As you can see, the
second session is blocked—it cannot acquire intent exclusive (IX) lock on the table, because it is incompatible with
shared (S) lock held by the first session. It is also worth mentioning that
WITH ROWLOCK
hint does not affect lock
escalation behavior.
Lock escalation is enabled by default and could introduce the blocking issues, which can be confusing for
developers and database administrators. Let's talk about a few typical cases.
The first case is reporting using REPEATABLE READ or SERIALIZABLE isolation levels for data consistency
purposes. If reporting queries are reading large amounts of data when there are no sessions updating the data, those
queries can escalate shared (S) locks to the table level. Afterwards, all writers would be blocked, even when trying to
insert new data or modify the data not read by reporting queries, as you saw earlier in the chapter.
