Lock Escalation with MSSQL

  • Even though you can specify the type of locks you want used by setting the TRANSACTION ISOLATION LEVEL or by using table hints, SQL Server will over-ride your setting depending on the usage.
  • SQL Server will try to optimize it's resources and will make sure that it operates as most efficiently as possible and will change your locks.
  • With SQL Server, you can lock from a row to the whole database. The locking level is usually set by: SET TRANSACTION ISOLATION LEVEL. The locking levels range from the lowest to the biggest:
    1. read uncommitted
    2. read committed
    3. repeatable read
    4. snapshot
    5. serializable

Applies to:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

The lock modes are:

  1. Shared: for read operations that do not change or update data, usually a SELECT statement.
  2. Update: to prevent the most common deadlock that occurs when multiple sessions are reading, locking, and updating.
  3. Exclusive: for data-modification, INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
  4. Intent: to establish a lock hierarchy: intent shared, intent exclusive, and shared with intent exclusive.
  5. Schema: when an operation dependent on the schema of a table is executing.
  6. Bulk update: when bulk copying data into a table with the TABLOCK hint.
  7. Key-range: protects a range of rows that are read by a query when using the serializable transaction isolation level.

You can lock:

  1. Row: a single row within a heap.
  2. Key: a row lock within an index to protect key ranges in serializable transactions.
  3. Page: an 8Kb page in a database, either data or index page.
  4. Extent: an 8 pages, either data or index pages.
  5. HOBT: a heap or B-tree (HOBT), a lock protecting either an index or a heap of data pages in a table that does not have a clustered index.
  6. Table: The entire table, including all the data and all the indexes.
  7. File: The whole database file.
  8. Database: The whole database.

Lock escalation

When the lock escalation is triggered, the SQL Server will attempt to escalate the lock to table level but the attempt can fail if there are conflicting locks.
  1. The number of locks held by a statement on an index or a heap within a statement is greater than 5000 [approx]. This also include the intent locks.
  2. The memory taken by the locking resources is greater than 40% of the memory.