gotchasqlModerate
Difference Between Row Level and Page Level Locking and Consequences
Viewed 0 times
leveldifferencebetweenlockingpageconsequencesandrow
Problem
When attempting to run my Maintenance Plan, I receive the following error:
Executing the query "" failed with the following error: "The
index "" (partition 1) on table "" cannot be reorganized
because page level locking is disabled."
We currently have Row Level locking enabled on this Index. I can enable Page Level locking, but I am unsure what the repercussions are.
My Question Is: What is the difference between the two locking schemes, and what are their real-world (in production) consequences?
Executing the query "" failed with the following error: "The
index "" (partition 1) on table "" cannot be reorganized
because page level locking is disabled."
We currently have Row Level locking enabled on this Index. I can enable Page Level locking, but I am unsure what the repercussions are.
My Question Is: What is the difference between the two locking schemes, and what are their real-world (in production) consequences?
Solution
Executing the query "" failed with the following error: "The index ""
(partition 1) on table "" cannot be reorganized because page level
locking is disabled."
The maintenance plan must be attempting an ALTER INDEX REORGANIZE, which is an online operation. To remove fragmentation (pages not in order), pages must be locked and moved, which is not possible if page locks have been disabled. The only way to defragment without page locks is to lock the entire partition, which is not possible for REORGANIZE as its online only.
What is the difference between the two locking schemes, and what are
their real-world (in production) consequences?
You need to grasp what a record and page are to evaluate the impact of disallowing a particular lock type. If you are unfamiliar with SQL Server storage internals, start with Anatomy of a Record and Anatomy of a Page. Put very simply:
If you were to alter the permitted lock types:
There are two scenarios I'm aware of where it can be beneficial to disallow a lock type. Doesn't mean there aren't others, hopefully someone else will step in with examples.
A frequently accessed lookup table, that changes infrequently - By disabling both page and row level locks, all readers will take a shared table lock. This is faster/cheaper rather than the usual intent-shared on the table, followed by intent-shared on a page and finally a shared lock on a specific row or rows.
Preventing a specific deadlock scenario - If you encounter deadlocks caused by concurrent processes acquiring locks that are frequently on the same page, disallowing row locks results in page locks being taken instead. Only one process can then access the page at a time, the other must wait.
The first example is micro-optimisation and unlikely to yield measurable benefit on a typical system. The second will solve that particular deadlock scenario but may introduce unexpected side effects e.g. killing concurrency in a different section of code. Difficult to assess the impact fully, approach with caution!
The default is for both to be enabled and this should not be changed without good cause.
(partition 1) on table "" cannot be reorganized because page level
locking is disabled."
The maintenance plan must be attempting an ALTER INDEX REORGANIZE, which is an online operation. To remove fragmentation (pages not in order), pages must be locked and moved, which is not possible if page locks have been disabled. The only way to defragment without page locks is to lock the entire partition, which is not possible for REORGANIZE as its online only.
What is the difference between the two locking schemes, and what are
their real-world (in production) consequences?
You need to grasp what a record and page are to evaluate the impact of disallowing a particular lock type. If you are unfamiliar with SQL Server storage internals, start with Anatomy of a Record and Anatomy of a Page. Put very simply:
- rows = records
- rows are stored in pages of 8kb
If you were to alter the permitted lock types:
- Disable page locks = Row and table locks only
- Disable row locks = Page and table locks only
- Disable both = Table locks only
There are two scenarios I'm aware of where it can be beneficial to disallow a lock type. Doesn't mean there aren't others, hopefully someone else will step in with examples.
A frequently accessed lookup table, that changes infrequently - By disabling both page and row level locks, all readers will take a shared table lock. This is faster/cheaper rather than the usual intent-shared on the table, followed by intent-shared on a page and finally a shared lock on a specific row or rows.
Preventing a specific deadlock scenario - If you encounter deadlocks caused by concurrent processes acquiring locks that are frequently on the same page, disallowing row locks results in page locks being taken instead. Only one process can then access the page at a time, the other must wait.
The first example is micro-optimisation and unlikely to yield measurable benefit on a typical system. The second will solve that particular deadlock scenario but may introduce unexpected side effects e.g. killing concurrency in a different section of code. Difficult to assess the impact fully, approach with caution!
The default is for both to be enabled and this should not be changed without good cause.
Context
StackExchange Database Administrators Q#6512, answer score: 16
Revisions (0)
No revisions yet.