Regarding DB Locks and Lock Escalation SQL Server 2008

Question

Dear Gurus

I have seen many articles and Questions/Answers Regarding Lock Escalation but following things are still vague. Please answer to the specific required information.

1. If Lock Escalation is disabled will there be Intended Locks on higher resources like on Page or Table???

2. If a Lock(IX) is placed on Page can another connection acquire lock(S) or lock(X) on a ROW within that page.

3. Does Intended lock behaves same like Shared lock i.e. if Lock(IX) or Lock(IS) is placed on a table we can't insert a new row in the table.

Please help me to understand above scenarios and also i would like to tell you that in my application there will no more than 2 or 3 locks within one table from one connection but my application is multi threaded and may acquire more than 20 connections concurrently so should i disable the lock escalation??? specially if answer of question 1 is "NO".

Thanks and Regards

Answer 1

To start from the end - and sorry, 20 or more concurrent connections are nothing in terms of load - you should ONLY disable lock escalation if you need it. that is, after determining hat a problem you have is fixed by this. That simple. Anything else is overoptimization - and fighting the intelligence in SQL Server, which does a pretty good job.

1: not that I know of, with some possible rare occasions.

2: to my knowlege not documented. Any answer could change with a hotfix.

3: Intended locks are basically a preparation. They stop allocation of locks that would not allow their intend to materialize. That said - unless your table is minimal... you rarely will se a table level lock at all.

Do you have a specific scenario for yor questions? Basically - this seems, and sorry if that comes through wrong, like a beginner afraid what all the locking does to his application without ever experiencing how a sql server really works beforehand. In general, locks happen as you like them. Sometimes no-locking makes sense, normally using the riht transaction isolation level makes sense, and normally you totally ignore the locking thing details because they do not matter. The ONLY situation you will get into problems are deadlocks, and those are fixed by changing access order.

Besides that, Locks just work and you trust SQL Server to do it's work properly.