Database Reference
In-Depth Information
The second case is the implementation of the purge process. Let's assume that you need to purge a large amount
of data using a DELETE statement. If the implementation deletes a large number of rows at once, you could have
exclusive (X) lock escalated to the table level. This blocks access to the table to the all writers, as well as to the readers
in READ COMMITTED, REPEATABLE READ, or SERIALIZABLE isolation levels, even when those queries are working
with a completely different set of data than you are purging.
Finally, you can think about the process that inserts the large batch of rows with the single INSERT statement.
Similar to the purge process, it could escalate exclusive (X) lock to the table level and block the other sessions from
accessing it.
All of those patterns have one thing in common—they acquire and hold the large number of row- and page-level
locks as part of the single statement. That triggers lock escalation, which would succeed if there were no other sessions
holding incompatible locks on the table (or partition) level. This would block other sessions from acquiring incompatible
intent or full locks on the table (or partition) until the first session completes the transaction, regardless of whether the
blocked sessions are trying to access the data affected by the first session or not.
It is worth repeating that lock escalation is triggered by the number of locks acquired by the statement, rather
than transaction. If the separate statements acquire less than 5,000 row- and page-level locks each, lock escalation is
not triggered, regardless of the total number of the locks transaction held. Figure 20-4 shows the example, where the
multiple update statements run in the loop within the single transaction. Even when the total number of the locks
transaction held is far more than the threshold, lock escalation is not triggered.
Figure 20-4. Lock escalation threshold is on per-statement basis
Lock Escalation Troubleshooting
There are a few ways to troubleshoot blocking problems that occur because of lock escalation. One of the signs that
shows potential problems is the high percentage of the intent lock waits in the wait statistics.
 
Search WWH ::




Custom Search