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!

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.

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…

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:

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:

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