0

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?

Improve this question

2 Answers 2

Reset to default
3

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

Improve this answer
1

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.

Improve this answer
3
  • Thanks for your answer , but again I have problem understanding that :( . again I found contradiction in your answer from this sentence :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.
    – user123215
    May 15, 2018 at 17:08
  • Please explain a little bit more with an example . thank you very very much
    – user123215
    May 15, 2018 at 17:09
  • Lets say a table has five pages, process A gets an exclusive lock on page 2. Process B wants a shared lock on the entire table (all five pages), it cannot get it though until process A releases it's exclusive lock. Might help to read up on lock escalation too.
    – MrTCS
    May 15, 2018 at 18:00

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.