In all versions of SQL Server since 7.0, the engine could choose to lock rows, pages or the entire table. In addition, even if it starts out your query execution by obtaining row or page locks, if too many locks are acquired, SQL Server could escalate to a table lock. Each lock uses memory, so when escalation replaces the thousands of fine-grained locks with one table lock, there can be substantial resource savings. On the other hand, once a table is exclusively locked, no other processes can access any data at all from it, which drastically reduces concurrency.
Way back in SQL Server 6/6.5, we had some controls over what percentage of a table had to be locked in order to induce escalation. These controls were implemented as configuration options but they were removed in SQL 7. So what controls do we have now?
Lock escalation occurs in the following situations (taken from "SQL Server 2008 Internals", MS Press 2009):
- The number of locks held by a single statement on one object, or on one partition of one object, exceeds a threshold. Currently that threshold is 5000 locks, but it might change in future service packs. The lock escalation will not occur if the locks are spread over multiple objects in the same statement—for example, 3000 locks in one index and 3000 in another.
- Memory taken by lock resources exceeds 40 percent of the non-AWE (32-bit) or regular (64-bit) enabled memory and the locks configuration option is set to 0. (In this case, the lock memory is allocated dynamically as needed, so the 40 percent value is not a constant.) If the locks option is set to a non-zero value, memory reserved for locks is statically allocated when SQL Server starts. Escalation will occur when SQL Server is using more than 40 percent of the reserved lock memory for lock resources.
Most of the discussion of escalation that I have seen indicates that people want to prevent escalation, to maximize access to the data. One way to do this is to enable trace flag 1211 on your instance, which prevents lock escalation from occurring under any circumstances. You need to be really careful with this one, because it affects every table in every database on the instance. Another option is to trick SQL Server into thinking the table is in use, because if there is even one row locked by a different connection, escalation cannot occur. So you could add a dummy row to your table and start a process that starts a transaction, updates the dummy rows, and then keeps the transaction open. Although this can keep anyone from acquiring a table lock, this method has its own nasty side-effects. Namely, as long as the transaction is open, the log cannot truncated past that point. But if you can synchronize it with the operation updating the table, and make sure this dummy update gets closed as soon as the 'real' update finishes, you might be ok.
SQL Server 2008 provides us with a bit more control, with a new option to ALTER TABLE:
ALTER TABLE <table_name>
SET (LOCK_ESCALATION = [TABLE | AUTO |DISABLE]);
The default is escalation is TABLE, and that was the only possibility for escalation prior to SQL 2008. If you set the option to AUTO, locks can escalate to a table or to a partition, if the table is partitioned. The third option is to completely disable escalation for this table. This option is much more manageable that my trick of having another transaction lock a single row, and much more fine-grained that disallowing escalation on the entire instance.
But what if you want the opposite behavior? What if you always want to take table locks in order to conserve resources? Yes, there are lock hints available to request TABLOCK or TABLOCKX, but those must be specified in every query. What if there are only a few tables that you want to always lock at the table level.
SQL Server provides an option to ALTER INDEX to disallow ROW and PAGE locks. (In SQL 7 and 2000 you could use the system procedure sp_indexoption to do the same thing.) If the index is a clustered index, this means ROW and PAGE locks will be disallowed for the table. But, if the table is a heap, you can't use this option.
ALTER INDEX <index_name> ON <table_name>
SET (ALLOW_PAGE_LOCKS = OFF);
Also note that if you set ALLOW_PAGE_LOCKS to OFF, you cannot REORGANIZE (defragment) the index.
I usually mention this ALTER INDEX option in my Internals and Tuning class, and also mention that the only time I really had to use it was when clients turned off PAGE or ROW locks inappropriately and ended up with far too many unexplained table locks. Once you check sys.indexes (there are columns called allow_row_locks and allow_page_locks, with possible values of only 0 and 1), the behavior is no longer unexplained. (For versions prior to SQL Server 2005 you can use the INDEXPROPERTY function to check the status of these options.) It was several years ago that I last encountered someone using this option to turn off finer grained locks, and I was considering removing mention of it from my course.
And then, just last week, in my Oslo class, one of students had been asking about why her queries with CTEs ended up getting TABLE locks all the time. I didn't believe it had anything to do with the CTEs, but suggested she wait until we discussed locking, which we do on the fourth day of the class. She paid very close attention, and on Friday morning she came back and told me that the problem had not been the CTEs after all, but that 'someone' had turned off the ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS options!
So I guess I continue to mention this possibility in future classes.