THE SQL Server Blog Spot on the Web

Welcome to - 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:

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;
IF object_id('testlocks') IS NOT NULL
   DROP TABLE testlocks;

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

Now look at the lock properties:

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


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;
SELECT allow_row_locks, allow_page_locks, type_desc FROM sys.indexes
WHERE object_id = object_id('testlocks');


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:

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


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!


Published Monday, May 7, 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



Paul White said:

Hi Kalen,

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



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:



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


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.



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!


May 14, 2012 3:02 PM

Nimesh Parikh said:


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:


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:



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.


June 29, 2012 1:21 AM

Mohnish said:

Thank for the help....It worked

March 2, 2013 1:10 AM

Aneesh said:


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

March 5, 2013 5:06 PM

mbourgon said:

To answer Aneesh's question (since we're having to fix it right now) - on a heavily inserted table, we were having really bad contention issues.  Tons of deadlocks, which didn't make any sense since it was just doing very simple inserts via a SP, and under 100k/day. Both row locks and page locks were turned OFF.  Turning them back on fixed the issue.

April 6, 2015 9:28 AM

king said:



January 16, 2018 11:30 PM

aaaa said:

mt0417 mt0417

April 16, 2018 9:00 PM

chenyingying said:


May 6, 2018 10:50 PM

linying123 said:


May 10, 2018 8:09 PM

shenyuhang said:


June 1, 2018 7:08 PM

dongdong8 said:



June 29, 2018 2:55 AM

linying123 said:



July 16, 2018 8:55 PM

dongdong8 said:



July 23, 2018 11:42 PM

obat diabetes said:

August 1, 2018 11:18 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:50 PM

obat sinusitis said:

August 10, 2018 7:23 PM

yaoxuemei said:


August 15, 2018 2:15 AM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 8:59 PM

chenjinyan said:


August 22, 2018 11:14 PM

shenyuhang said:


August 23, 2018 10:30 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:58 PM

obat benjolan di tubuh said:

August 30, 2018 7:41 PM

obat wasir said:

September 4, 2018 8:26 PM

obat katarak said:

September 16, 2018 5:47 PM

obat maag said:

September 17, 2018 7:11 PM

obat limfoma said:


September 23, 2018 6:51 PM

xiaojun said:

20180928 junda

September 27, 2018 10:58 PM

yanmaneee said:">">

June 29, 2020 11:10 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement