Question :
According to the book Microsoft Sql server 2012, Chapter 13 (Transactions and concurrency):
If an exclusive page lock is held in one page of a table, another
process can not get even a shared table lock for that table. This
hierarchy is protected using intent locks. A process acquiring an
exclusive page lock, update page lock or intent exclusive page lock
first acquires an intent exclusive lock on that table. This intent
exclusive table lock prevents another process from acquiring the
shared table lock on that table.
And according to another paragraph of this book:
… lock compatibility is an issue only when the locks affect the same
object. For example two or more process can hold exclusive page locks
simultaneously, as long as the locks are on different pages.
I think these two paragraphs are in contradiction with each other, because in the first one it is said that if there is an exclusive lock on a page a table, other type of locks can’t be granted on the whole table to other processes In the second paragraph it is said that it can be granted if it is on a different page.
Which one is true?
Answer :
It is not that simple to understand without making your hands dirty and digging much deeper into the SQL Server locking. And yes, you must take into account the locking escalation as it is a completely different approach and while sometimes could be very useful, it has its own negative consequences
I would suggest reading All about locking in SQL Server where you can find a lot of details, explained in a simple and concise manner, that allows even the beginners to process this rather complex thing like SQL locking
There’s no contradiction. It states that if a single process holds an exclusive lock on one page, no other process can hold a shared table lock – this would mean the second process would hold a lock on every page in said table effectively, but it can’t do that due to the exclusive lock held by process 1.
This does not rule out the second process holding locks on other pages though (pages, not tables is the important part to take away here)
I do agree the wording is odd and could lead to this conclusion easily though.