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

Viewing Deadlock Graphs With The system_health Session

Jes Borland shows how you can avoid using trace flags 1204 and 1222 and view deadlocks from the system_health Extended Event: This one isn’t bad, but imagine a multi-statement deadlock, or a server with several deadlocks in an hour – how do you easily see if there were other errors on the server at the […]

Read More

Query Store Cleanup Can Be Blocked

Kendra Little shows that you can block Query Store cleanup: This is an isolated test system, so I went to clean out Query Store as a reset. I didn’t need any of the old information in there, so I ran: ALTER DATABASE BabbyNames SET QUERY_STORE CLEAR ALL; GO I was surprised when I didn’t see […]

Read More

Categories

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