Did You Know? Turning Off Locking

re: Did You Know? Turning Off Locking

Paul White — Tue, 08 May 2012 10:17:05 GMT

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

re: Did You Know? Turning Off Locking

Jungsun Kim — Tue, 08 May 2012 14:44:25 GMT

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

re: Did You Know? Turning Off Locking

Kalen Delaney — Wed, 09 May 2012 01:15:48 GMT

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.

re: Did You Know? Turning Off Locking

Kalen Delaney — Wed, 09 May 2012 01:16:35 GMT

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

re: Did You Know? Turning Off Locking

Mbourgon — Wed, 09 May 2012 03:38:47 GMT

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. :)

re: Did You Know? Turning Off Locking

Adithya — Mon, 14 May 2012 11:35:55 GMT

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.

re: Did You Know? Turning Off Locking

Kalen Delaney — Mon, 14 May 2012 19:02:19 GMT

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

re: Did You Know? Turning Off Locking

Nimesh Parikh — Tue, 15 May 2012 09:35:22 GMT

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..?

re: Did You Know? Turning Off Locking

Kalen Delaney — Wed, 16 May 2012 16:11:04 GMT

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

re: Did You Know? Turning Off Locking

Paul Harrington — Fri, 29 Jun 2012 05:21:00 GMT

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.

re: Did You Know? Turning Off Locking

Mohnish — Sat, 02 Mar 2013 06:10:14 GMT

Thank for the help....It worked

re: Did You Know? Turning Off Locking

Aneesh — Tue, 05 Mar 2013 22:06:56 GMT

Kalen,

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