Did You Know? Turning Off Locking

Back when the lock manager was rewritten for SQL Server 7, and row level locking was added to the product, the procedure sp_indexoption gave us the ability to turn OFF either row or page locks or both, for one particular index, or for a table and all the indexes on it. I must admit, I rarely found this option to be the solution to any sticky problems, although while doing some quick research prior to writing this post, I did find that Microsoft recommended turning off locking options on a couple of tables  when working with SAP:  http://msdn.microsoft.com/en-us/library/aa226172(v=sql.70).aspx

In my experience, it is MUCH more common to have people turn off row or page locks when they shouldn’t, and by inspecting the sys.indexes catalog view, you can tell if that has happened.  (Prior to SQL Server 2005,  you could use the INDEXPROPERTY function.) 

I recently got email from a reader of my books who pointed out to me an error I had made in my SQL Server 2005 book, where I had claimed that sp_indexoption could not be used to disallow row or page locks on heaps. If you check the BOL, it clearly says that you can supply either an index name or a table name to sp_indexoption, so that was just an oversight in my writing. 

But then along comes ALTER INDEX, and the BOL now says you should avoid using sp_indexoption, as it will be removed in a future release, and you should use ALTER INDEX instead. So the reader sent me a follow-up question, asking how can we disallow row or page locking on a heap using ALTER INDEX. 

It turns out that we can specify the ALL option instead of an index name when using ALTER INDEX, and this has the same effect as using sp_indexoption on a heap.  You can see it in this short example.

First create a heap with a nonclustered index:

use tempdb;
GO
IF object_id('testlocks') IS NOT NULL
   DROP TABLE testlocks;
GO

CREATE TABLE testlocks
( a int);
CREATE INDEX testlocks_index ON testlocks(a);
GO

Now look at the lock properties:

SELECT allow_row_locks, allow_page_locks, type_desc FROM sys.indexes
WHERE object_id = object_id('testlocks');
GO

Results:

allow_row_locks allow_page_locks type_desc
--------------- ---------------- ---------------
1               1                HEAP
1               1                NONCLUSTERED

As shown, the default is that both row and page locks are allowed.

Now use the old sp_indexoption to disallow row locks and then check the lock properties again:

EXEC sp_indexoption testlocks, disallowrowlocks, 1;
GO
SELECT allow_row_locks, allow_page_locks, type_desc FROM sys.indexes
WHERE object_id = object_id('testlocks');
GO

Results:

allow_row_locks allow_page_locks type_desc
--------------- ---------------- -------------
0               1                HEAP
0               1                NONCLUSTERED

Notice that sp_indexoption turned off row locking for both the heap and the nonclustered index.

Now use ALTER INDEX to allow row locks and inspect again:

ALTER INDEX ALL ON testlocks
SET  (ALLOW_ROW_LOCKS = ON );
GO
SELECT allow_row_locks, allow_page_locks, type_desc FROM sys.indexes
WHERE object_id = object_id('testlocks');
GO

RESULTS:

allow_row_locks allow_page_locks type_desc
--------------- ---------------- -------------
1               1                HEAP
1               1                NONCLUSTERED

Keep in mind that I’m not recommending that you turn off either row or page locking. And if you decide to change the types of locks allowed, make sure you test your application thoroughly under your maximum expected load to make sure you haven’t made things worse. And remember that you can’t turn off TABLE locking, only row and page locking.

Have fun!

~Kalen