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.
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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