THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

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

Published Monday, May 07, 2012 5:35 PM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul White said:

Hi Kalen,

Interesting post, thank you.  From SQL 2008 onward, there's also:

ALTER TABLE dbo.testlocks REBUILD

WITH (ALLOW_ROW_LOCKS = OFF);

I can't see many differences between this and using ALTER INDEX ALL (both affect the heap and any indexes) but this syntax is a bit richer, for example:

ALTER TABLE dbo.testlocks REBUILD

WITH (ALLOW_ROW_LOCKS = ON, ONLINE = ON, DATA_COMPRESSION = ROW);

Kinda odd that there are two syntaxes for essentially the same thing, and no way (that I can see) to affect just the heap.

Paul

May 8, 2012 6:17 AM
 

Jungsun Kim said:

Dear Kalen,

"First create a heap with a clustered index:"

--> "First create a heap with a Nonclustered index:"

Am I right?

Anyway, thank you for the good post.

Reagrds,

Jungsun

May 8, 2012 10:44 AM
 

Kalen Delaney said:

Thanks Paul!  In class, I talk about ALTER TABLE REBUILD for other purposes (like just simply rebuilding a heap), but completely overlooked that it could change the lock properties. I guess if we just want to affect the heap, we'll have to change everything,and then change the nc indexes back individually. I do like the idea of using the ALTER TABLE instead of the ALTER INDEX for this purpose.

May 8, 2012 9:15 PM
 

Kalen Delaney said:

Thanks Jungsun, for your careful reading and your correction! I have fixed the mistake.

May 8, 2012 9:16 PM
 

Mbourgon said:

Another reason you see locks turned off was because of a bug in SSMS 2005, (RTM,I think). We kept having to turn them back on until we figured out it was one particular developer using the older version of SSMS.  Thanks for the info; hopefully we won't need it. :)

May 8, 2012 11:38 PM
 

Adithya said:

I never knew this. However, as you said when Row and Page Level Locks are turned off or disallowed, is it not problematic when SQL Server takes up only the table locks.

May 14, 2012 7:35 AM
 

Kalen Delaney said:

Hi Adithya

Yes, that was my last paragraph... I am not recommending you turn off row and/or page locks as it can cause more problems than it solves!

~Kalen

May 14, 2012 3:02 PM
 

Nimesh Parikh said:

Mem,

I have different question, but on Index only.

I was using SP_SPACEUSED and it gave me some strange result. even though I do not have any index on table, result shows me that SQL Server has occupy some space.

2 pages. why is that so..?

May 15, 2012 5:35 AM
 

Kalen Delaney said:

Nimesh

Comments on a blog post are not the best place to get help with totally unrelated questions. I usually just ignore them, and sometimes I remove them. But I was looking for a topic for my SQL Server Magazine newsletter article, so I decided to use this question as a basis. So the answer will appear to tomorrow in newsletter.

You can subscribe to the SQL Server Pro UPDATE newsletter here:

http://forms.sqlmag.com/forms/Subscribe-Free-SQLServerMag-Newsletters

Thanks

Kalen

May 16, 2012 12:11 PM
 

Paul Harrington said:

Hi Kalen,

Thanks for the timely article I think it is going to solve a deadlock problem I have. I am getting deadlocks as a result of pagelocks on an index. I have multiple processes processing data in the same table but there is a batch ID on the rows to keep the processing separate. Occasionally we are encountering deadlock on the index pages.  I am going to try removing page locking on the index to see if that fixes the problem.

Paul.

June 29, 2012 1:21 AM
 

Mohnish said:

Thank for the help....It worked

March 2, 2013 1:10 AM
 

Aneesh said:

Kalen,

Just curious, what kind of problems we can resolve thru allowing pagelocks and RowLocks at an index level.

March 5, 2013 5:06 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement