Disabling ROW and PAGE Level Locks in SQL Server

Today I want to talk about another very interesting topic in SQL Server: disabling Row and Page Level Locks in SQL Server. Every time that you rebuild an Index in SQL Server, you can use the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options to specify that SQL Server should acquire Row and Page Level Locks when accessing your data for reading and writing. Let’s look at what happens internally when we disable these locks.

Disable Row Level Locks

Let’s run a simple REBUILD operation on a Clustered Index, where we disable Row Level Locks:

-- Disable row level locks
ALTER INDEX idx_ci ON Foo REBUILD
WITH (ALLOW_ROW_LOCKS = OFF)
GO

As you know from the Locking Hierarchy, SQL Server acquires locks at the table level, the page level, and the row level. Now let’s run a simple SELECT statement in an explicit transaction and let’s hold the Shared Locks until the end of the transaction with the query hint HOLDLOCK.

-- SQL Server acquires in Repeatable Read a Shared Lock on the Page Level,
-- because Shared Row Locks are not possible anymore.
BEGIN TRANSACTION

SELECT * FROM Foo WITH (HOLDLOCK)
WHERE ID = 5000

SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

ROLLBACK
GO

When you look into the Lock Manager during that transaction, you can see that SQL Server has only acquired the IS Lock at the Table level, and a Shared Lock at the Page level. There are no Row Level locks anymore!

There are no row locks anymore!

The acquired locks are now more restrictive, because normally SQL Server acquires an IS lock at the page level and a Shared Lock on the row itself. The same concept applies when you change your data through a transaction:

-- SQL Server acquires for an UPDATE statement an Exclusive Lock on the Page Level,
-- because Exclusive Row Locks are not possible anymore.
BEGIN TRANSACTION

UPDATE Foo
SET Col2 = REPLICATE('y', 100)
WHERE ID = 5000

SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

ROLLBACK
GO

In that case you again end up with an Exclusive Lock at the Page Level instead of an IX lock.

Disable Page Level Locks

Next let’s disable Page Level Locks:

-- Disable Page level locks
ALTER INDEX idx_ci ON Foo REBUILD
WITH (ALLOW_PAGE_LOCKS = OFF)
GO

The first thing that I want to show you here is that an Index Reorganize operation is dependent on Page Level locks. Therefore a simple Reorganize of that index will fail:

The index “idx_ci” on table “Foo” cannot be reorganized because page level locking is disabled.

Now let’s run our SELECT statement again but this time with the query hint HOLDLOCK:

-- There is no IS lock on the Page anymore.
BEGIN TRANSACTION

SELECT * FROM Foo WITH (HOLDLOCK)
WHERE ID = 5000

SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

ROLLBACK
GO

When you look again into the Lock Manager you can see that the IS lock at the Page level disappeared. We only have an IS lock at the Table level, and the S Lock on the row.

And now we have no page locks anymore!

Let’s try to change a record again:

-- There is no IX lock on the Page anymore.
BEGIN TRANSACTION

UPDATE Foo
SET Col2 = REPLICATE('y', 100)
WHERE ID = 5000

SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

ROLLBACK
GO

The same thing has happened as previously: SQL Server has only acquired the IX Lock at the Table level, and the X Lock on the row. There is no lock at the Page level anymore…

The IX lock on the page is gone...

Disable Row and Page Level Locks

And now let’s go overboard, and we disable Row and Page level Locks for our Clustered Index:

-- Disable Row and Page level locks
ALTER INDEX idx_ci ON Foo REBUILD
WITH (ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF)
GO

When you now read some data, SQL Server just acquires a Shared Lock at the Table level. Your whole table is read-only:

Great, our table is now read-only!

And when you change a record without being able to acquire Page and Row level Locks, SQL Server acquires an X Lock on the whole table – ouch:

And finally we have exclusively locked our table...

Summary

The moral of this story/blog post? There is not really a good reason why you should disable Page and Row level Locks in SQL Server. Just work with the default Locking Strategy that SQL Server offers, because otherwise the locking that is employed will be too restrictive and the throughput of your workload will suffer…

Thanks for your time,

-Klaus

7 thoughts on “Disabling ROW and PAGE Level Locks in SQL Server”

  1. What about the other way around? Disabling table locks so > 5000 locks does not lock the whole table? I haven’t found a case where an OLTP database should be allowed to AUTOMATICALLY escalate to a table lock. This means odd locks manage to get taken accidentally. If a table lock is required, it should be specifically requested.

  2. Hi Klaus,

    thank you for this great blog post!
    Now I understand why Ola Hallengrens’ IndexOptimize is rebuilding some of my indexes which would have been suitable for a reorganise.

    Developers at my company typically thought: “Man, page locks are bad. I don’t wanna SQL Server to lock the whole page and affect other processes…therefore I’ll set ALLOW_PAGE_LOCKS = OFF on every index I build to get less blocking.”. Turns out that this is not the right way to go.

    Thanks

    Martin

  3. Hamad Sheikh

    What are your thoughts on performance, with regards to locking turned OFF? Imagine a nightly job running exclusively on teh database with no lock contention. Would that benefit from the lack of locking, by gaining better performance?

    1. Klaus Aschenbrenner

      Hello Hamad,

      Thanks for your comment.
      When your job is single-threaded, it doesn’t matter.

      Thanks,

      -Klaus

  4. Chris Fournier

    Klaus,

    Thanks for explaining this topic. I appreciate the technical detail you stepped us through.

    I do have a comment regarding disabling PAGE level locks on an index. I sometimes do this to gain a “back door rowlock hint” in situation where I cannot access vendor code. This approach has helped me increase concurrency and eliminate deadlocking. It does come at a cost of losing the ability to REORG the index, as you pointed out, so I have to take care to account for that in my maintenance process.

    -Chris

  5. It is nice to get your experience on PageLock, RowLocks. You will help us get started with something than to suffer like you when starting from scratch. Adios amigos.

Leave a Reply to Kelvin nyota Cancel Reply

Your email address will not be published. Required fields are marked *