Although row level locking is great from a concurrency standpoint, it is expensive. In memory, lock structure uses 64 bytes in 32-bit and 128 bytes in 64-bit operating systems. Keeping information about millions of row- and page-level locks would require SQL server to allocate gigabytes of RAM to store them.

SQL Server reduces the number of locks held in memory with a technique called Lock Escalation, which we will discuss in this chapter.

Lock Escalation Overview

Once a statement acquires at least 5,000 row- and page-level locks on the same object, SQL Server tries to escalate, or perhaps better said, replace, those locks with a single table- or, in some cases, partition-level lock. Operation would succeed if no other sessions held incompatible locks on the object or partition.

When an operation succeeds, SQL Server releases all row- and page-level locks held by the transaction on the object (or partition), keeping the object- (or partition-) level lock only. If an operation fails, SQL Server continues to use row-level locking and repeats escalation attempts after about every 1,250 new locks acquired. In addition to the number of the locks taken, SQL Server can escalate locks when the total number of locks in the instance exceeds memory or configuration thresholds.

Let’s look at the example. The first session starts the transaction in REPEATABLE READ transaction isolation level and runs a SELECT statement that counts the number of rows in Delivery.Orders table. As you will remember, in this isolation level, SQL Server keeps shared (S) locks until the end of transaction.

Let’s disable lock escalation for this table with the ALTER TABLE SET (LOCK_ESCALATION=DISABLE) command (more about this later), and look at the number of locks SQL Server acquires as well as at the memory required to store them. In addition, while the transaction is still active, let’s insert another row from the different session to demonstrate how lock escalation affects concurrency in the system using a WITH ROWLOCK hint. This hint prevents the situation in which SQL Server optimizes the locking by acquiring page-level shared (S) locks instead of row-level locks.

Figure 20-1 shows the code and results of the queries.

Figure 20-2 shows the Lock Memory (KB) system performance counter while the transaction is active.

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.

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.

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.

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.

You can capture the Lock Escalation SQL Trace event. Figure 20-5 illustrates the output from this event in the SQL Profiler application.

The following attributes are useful during troubleshooting:

• EventSubClassindicates what triggered lock escalation—number of locks or memory threshold.
• IntegerData and IntegerData2show the number of locks that existed at the time of the escalation and how many locks were converted during the escalation process.
• Mode tells what kind of lock was escalated.
• ObjectID is the object_id of the table for which lock escalation was triggered.
• ObjectID2 is the HoBT ID for which lock escalation was triggered.
• Type represents lock escalation granularity.
• TextData, LineNumber, and Offset provide the information of the batch and statement that trigger lock escalation.

There is also Table Lock Escalations/sec performance counter in the SQL Server Access Methods section that can be useful for baselining the system and, of course, lock_escalation Extended Event.

From the blocked session standpoint, if you run the code shown in Listing 18-2 (Chapter 18), you will see the results shown in Figure 20-6.

The key point here is that you have two object-level locks. The blocked session is trying to acquire intent lock on the object level while the blocking session holds incompatible full lock.

If you look at the blocked process report, you see that the blocked process is waiting on the intent lock on the object, as shown in Listing 20-1.

Listing 20-1.  Blocked Process Report (Partial)

<blocked-process-report>
 <blocked-process>
  <process id="process3e99330c8" taskpriority="0" logused="0" waitresource="OBJECT: 14:469576711:0 " waittime="28054" ownerId="771300" transactionname="user_transaction" lasttranstarted="2013-06-08T14:56:30.483" XDES="0x3e6ac4d28" lockMode="IX" schedulerid="2" kpid="2384" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2013-06-08T14:56:30.470" lastbatchcompleted="2013-06-08T14:56:30.470" lastattention="1900-01-01T00:00:00.470" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQL2012-STD1" hostpid="828" loginname="SQL2012-STD1Administrator" isolationlevel="read committed (2)" xactid="771300" currentdb="14" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">

Keep in mind that there could be other reasons for the sessions to acquire full object locks or be blocked waiting for intent lock on the table. You must correlate the information from the other venues (SQL Traces, Extended Events, and so on) to be sure blocking occurs because of lock escalation.

Although lock escalation can introduce blocking issues, it helps to preserve SQL Server memory. The large number of locks held by the instance reduces the size of the Buffer Pool. As a result, you have fewer data pages in the cache, which could lead to the higher number of the physical I/O operations and degrade the performance of the queries. In addition, SQL Server could terminate the queries with error 1204 when there is no available memory to store the lock information. Figure 20-7 shows just such an error message.

In SQL Server 2008 and above, you can control escalation behavior at the table level by using the ALTER TABLE SET LOCK_ESCALATION statement. This option affects lock escalation behavior for all—clustered and non-clustered—indexes defined on the table. Three options are available:

DISABLE: This option disables lock escalation for a specific table.

TABLE: SQL Server escalates locks to the table level. This is the default option.

AUTO: SQL Server escalates locks to the partition level when the table is partitioned or to the table level when the table is not partitioned. Use this option with the large partitioned tables, especially when there are large reporting queries running on the old data.

Unfortunately, SQL Server 2005 does not support this option, and the only way to disable lock escalation in this version is by using documented trace flags T1211 or T1224 at the instance or session level.

T1211 disables lock escalation, regardless of the memory conditions.

T1224 disables lock escalation based on the number of locks threshold, although lock escalation can still be triggered in the case of the memory pressure.

As with the other blocking issues, you should find the root cause as to why lock escalation occurs. You should also think about the pros and cons of disabling lock escalation on particular objects in the system. Although it could reduce the blocking in the system, SQL Server would use more memory to store lock information. And, of course, you can consider code refactoring as another option.

In case lock escalation is triggered by the writers, you can reduce the batches to the point at which they are acquiring less than 5,000 row-and page-level locks per object. You can still process multiple batches in the same transaction - the 5,000 locks threshold is per statement. At the same time, you should remember that smaller batches are usually less effective than large ones. You need to fine-tune the batch sizes and find the optimal values. It is normal to have lock escalation triggered as long as object-level locks are not held for an excessive period of time and/or it does not affect the other sessions.

As for the lock escalation triggered by the readers, you should avoid situations in which many shared (S) locks are held. One example is scans due to non-optimized or reporting queries in REPEATABLE READ or SERIALIZABLE transaction isolation levels when queries held the shared (S) locks until the end of transaction. The example shown in Figure 20-8 runs the select from the Orders table using 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.

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.

In some cases, you may consider partitioning the tables and set lock escalation option to use partition-level escalation, rather than table level, using the ALTER TABLE SET (LOCK_ESCALATION=AUTO) statement. This could help in scenarios in which you must purge old data using the DELETE statement or run reporting queries against old data in REPEATABLE READ or SERIALIZABLE isolation levels. In those cases, statements escalate the locks to partitions, rather than tables and queries that are not accessing those partitions, would not be blocked.

In some cases, you can switch to optimistic isolation levels, which is discussed in Chapter 21. Finally, you would not have any reader-related blocking issues in the READ UNCOMMITTED transaction isolation level where shared (S) locks are not acquired, although this method is not recommended because of all the other data consistency issues it introduces.

Summary

SQL Server escalates locks to the object level after the statement acquires and holds about 5,000 row- and page-level locks. When escalation succeeds, SQL Server keeps the single object-level lock, blocking other sessions with incompatible lock types from accessing the table. If escalation fails, SQL Server repeats escalation attempts after about every 1,250 new locks are acquired.

Lock escalation fits perfectly into the “it depends” category. It reduces the SQL Server Lock Manager memory usage and overhead of maintaining the large number of locks. At the same time, it could increase blocking in the system because of the object- or partition-level locks held.

You should keep lock escalation enabled, unless you find that it introduces noticeable blocking issues in the system. Even in those cases, however, you should perform root-cause analysis as to why blocking due to lock escalation occurs and evaluate the pros and cons of disabling it. You should also look at the other options available, such as code and database schema refactoring, query tuning, as well as switching to optimistic transaction isolation levels. Either option might be the better choice to solve blocking problems, rather than disabling lock escalation.