Locking Enhancements - SQL Server 2008

The whole point of locking is to optimize for better concurrency. Imagine what life in the database world would be like if there were no locking. Chances are once you had more than one user utilizing the database, you would run into many problems like dirty reads and phantom values.

SQL Server 2008 includes shared, update, exclusive, intent, schema, and bulk update locks. The lock manager within the SQL Server database engine is responsible for managing all these types of locks in all the different transactions. Since holding a lock is not the best thing to do from a performance standpoint, you always want to lock at the most granular level, like at a row level. You can imagine the amount of memory and CPU resources that are required by the lock manager when it’s trying to lock at the lowest level, given potentially thousands of transactions a second. To help alleviate this strain on resources, the lock manager will escalate a lock (for example, from row to table level) given certain circumstances. In general, if a single T-SQL statement acquires at least 5,000 locks on a single nonpartitioned table (or index), or if the lock manager is running short on memory, the lock manager will issue an escalation.

In SQL Server 2008, the ALTER TABLE statement has a SET option for LOCK_ ESCALATION with three
possible values:

  • TABLE: When set to TABLE (the default), the behavior of the lock manager is the same as in previous versions of SQL Server, in that the escalation will be from a row or page to a table lock, regardless of how many partitions are in the table.
  • DISABLE: When LOCK_ESCALATION is set to DISABLE, the lock manager will not escalate most of the time. There are a few corner cases where the SQL Server database engine absolutely needs a lock to protect data integrity, but for the most part, no escalations take place.
  • AUTO: The AUTO option will add a lot of value for those users who partition their tables. When set to AUTO, the lock manager will lock the heap or B-tree of the partition instead of the table. Once escalated to a heap or B-tree of the partition, it will never escalate again to the table level. If the table is not partitioned and LOCK_ESCALATION is set to AUTO, the escalation will be to the table level.

If you use partitions and currently have locking issues, you should definitely consider changing the lock escalation to AUTO as follows:

ALTER TABLE [MyTable] SET (LOCK_ESCALATION = AUTO)

You may be pleasantly surprised at the reduction in locking issues after changing to AUTO lock escalation.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics