mohammed moinudheen (5/25/2011)
I thought row level locks would be escalated to table locks only.I referred the link provided in the answer, but couldn't find any information regarding escalation to partition.
The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the associated partition instead of to the table lock. A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.
Did anyone find more information regarding the escalation of row level locks to partition in the link provided?
I'll give it a try (in a technically simplified way): The HoBT level (HoBT = Heap or B-Tree, see http://technet.microsoft.com/en-us/library/ms189849.aspx) is the structure underlying a table. If the table is not partitioned, you could say HoBT(a) = table(a).
For a partitioned table, each partition is one HoBT, or HoBT(a1) = p(1)table(a), HoBT(a2) = p(2)table(a), etc.
So locking just one HoBT of a partitioned table = locking the partition, but not the entire table.
Or as BOL puts it: A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.
Personally I'd rephrase that to: A HoBT-level lock does not necessarily lock all aligned HoBTs for an entire partitioned table.
Regards,
Michael