Understanding Lock Escalation

Kendra Little explains some of the rules behind lock escalation, including which locks don’t cause escalation:

Books Online has a good article about this, which explains a lot of the details about how many locks you need to take out to trigger lock escalation. Here are the (simplified) basics:

  • The ‘magic’ number to trigger escalation for the first time is 5,000 locks on a single table reference

  • Locks do NOT escalate from row level to page level. Row locks escalate to table. Page level locks also escalate to table level. In other words, forcing row level locking will not make it less likely to escalate locks to the table level, but rather it will do the opposite.

    • Note: for partitioned tables, you have the option to enable partition level escalation
  • If you’re modifying data, the escalated table lock will be exclusive. That means nobody else can party with the table if lock escalation succeeds while you’re doing your work.

Not all locks count, though, and Kendra has provided a test to show this.

Related Posts

ROWGROUP_FLUSH Deadlocks Inserting Into Clustered Columnstore Indexes

Joe Obbish ran into a strange deadlock when performing concurrent insertions into a clustered columnstore index: We’ve only observed this deadlock with multiple concurrent sessions insert to the delta store for the same target CCI due to server memory pressure or very low cardinality estimates (less than 251 rows). The correct mitigation depends on why […]

Read More

Finding Blocking In SQL Server

Amy Herold has a script to help you find which query is blocking your important query: It might look complicated but it is actually very simple – query sys.sysprocesses with a cross apply using the sql_handle to get the text of the query, and then an outer apply with the same query again but you […]

Read More

Categories

April 2017
MTWTFSS
« Mar May »
 12
3456789
10111213141516
17181920212223
24252627282930