0

I have few questions on table lock escalations, please confirm or disprove:

  1. Index Scan operator in a query results in Table Lock Escalation ? always or not (small tables) ?

  2. When Shared lock is escalated to a table level, it blocks small updates and deletes ? inserts as well ?

  3. Is it possible to get counts of table lock_escalation event, grouped by login and then by table ? For example:

login1 - table1 - 11
login1 - table2 - 5
login2 - table1 - 29
etc...

Extended Events seemed to allow only one level of grouping if I am not mistaken

1 Answer 1

2

I will preface by saying: a Table Lock is generally taken only when the number of locks on a single table hits 5,000, or 40% of lock memory has been used. See the documentation for further details.

With that in mind:

  1. An Index Scan does not necessarily take a table lock, it will only do so once it hits the thresholds. This may not happen even on a large table, if there is a row-goal on the scan (for example TOP or EXISTS). The total size of the table appears immaterial, only the number of rows actually read.
  2. An S lock on the Table level is not compatible with an IX or an X lock, one of which would be attempted by DML such as UPDATE or DELETE. However, a U lock can still be taken, which means that DML can still find rows to modify, but will have to wait to actually attempt the modification.
1
  • If you have a new question, you should create one, not add it to your original Oct 25, 2021 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.