Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation level is READ_COMMITTED_SNAPSHOT

alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE)
go
alter index <INDEX_NAME> ON <TABLE_NAME> SET (ALLOW_PAGE_LOCKS=OFF) 
go

It's still not working after setting this.

Do I need to rebuild my table in order for it to not use page locking?

share|improve this question
4  
it's still not working How do you know is working or not working? What do you measure, what do you observe? –  Remus Rusanu Nov 26 '12 at 7:55
add comment

migrated from stackoverflow.com Nov 26 '12 at 11:29

This question came from our site for professional and enthusiast programmers.

1 Answer

The setting to disable page locking applies per index, so applying this change to the clustered index only affects execution plans that access the data via that index. If there are nonclustered indexes on the table, you may have to disable page locking for them as well. The following script demonstrates this:

CREATE TABLE dbo.LockTest
(
    col1    integer IDENTITY NOT NULL,
    col2    integer NOT NULL,
    col3    integer NOT NULL,

    CONSTRAINT PK_LockTest
        PRIMARY KEY CLUSTERED (col1)
);
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_LockTest
ON dbo.LockTest (col2);
GO
ALTER TABLE dbo.LockTest SET (LOCK_ESCALATION=DISABLE);
ALTER INDEX PK_LockTest ON dbo.LockTest SET (ALLOW_PAGE_LOCKS=OFF);

Using the clustered index as the access method:

-- Error 651:
-- Cannot use the PAGE granularity hint on the table "dbo.LockTest"
-- because locking at the specified granularity is inhibited.
SELECT col1
FROM dbo.LockTest WITH (INDEX(PK_LockTest), PAGLOCK);

Using the nonclustered index:

-- Succeeds: page locks allowed on index UQ_LockTest
SELECT col2 FROM dbo.LockTest AS lt WITH (PAGLOCK);

-- Succeeds: UQ_LockTest also includes col1
-- NC indexes always include the clustering key
SELECT col1 FROM dbo.LockTest AS lt WITH (PAGLOCK);

-- Fails: cannot retrieve col3 without touching the clustered index
SELECT col3 FROM dbo.LockTest AS lt WITH (PAGLOCK);

Note that disabling page locking can have unexpected side-effects, such as preventing index reorganization (since this process works at the page level):

-- Error 1943:
-- The index "PK_LockTest" on table "LockTest" cannot
-- be reorganized because page level locking is disabled.
ALTER INDEX PK_LockTest ON dbo.LockTest REORGANIZE;

-- Succeeds (page locking available):
ALTER INDEX UQ_LockTest ON dbo.LockTest REORGANIZE;
share|improve this answer
3  
+1 great information, Paul –  Thomas Stringer Nov 30 '12 at 2:40
    
thanks a lot, it worked –  grassbl8d Jan 2 '13 at 17:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.