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.

A reindexing script was recently run in our dev environment that set ALLOW_ROW_LOCK = FALSE and ALLOW_PAGE_LOCKS = FALSE on all the indexes in the database.

This was done while testing new reindexing scripts--it was not done intentionally.

After this script was run, we immediately started seeing a large number of deadlocks where we normally never saw them.

  1. Was setting the locking options on the indexes the direct cause?
  2. Why exactly would those settings cause deadlocks?
share|improve this question
I think doing this means locks would get taken at the table level (non-partitioned), but I'm not 100% sure. –  Jon Seigel Jul 16 '12 at 13:43
Yeah I think Jon is right, sounds like it would result in table locks –  Eric Higgins Jul 16 '12 at 15:50
Correct, if no row lock nor page locks are available the only option left are table/partition locks. –  Remus Rusanu Jul 17 '12 at 11:48

1 Answer 1

up vote 5 down vote accepted

The article linked to in the comments makes it clear, but let's prove it with an example.

CREATE TABLE t1(a int);
INSERT INTO t1(a) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);

    ON t1(a)
        WITH(ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); /* Default */


    UPDATE t1
        SET a = 10
        WHERE a = 1;

    EXEC sp_lock;



Results (filtered to SPID and table of interest):

spid   dbid    ObjId       IndId   Type    Resource        Mode    Status
52      6       213575799   1       PAG     1:118           IX      GRANT
52      6       213575799   1       KEY     (de42f79bc795)   X      GRANT
52      6       213575799   1       KEY     (241332e1ddb0)   X      GRANT
52      6       213575799   0       TAB                     IX      GRANT <--
52      6       0           0       DB                       S      GRANT

(There are two KEY locks because SQL Server needs to protect both the "before" and "after" index values. If you want to see this, run SELECT a, %%lockres%% FROM t1 before and after the UPDATE statement.)

If I run the same script again with the clustered index definition having ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF, I get this:

spid   dbid         ObjId   IndId  Type    Resource    Mode    Status
52      6               0       0   DB                    S     GRANT
52      6       229575856       0   TAB                   X     GRANT <--

So it should be pretty obvious now why you suddenly started having deadlocks!

share|improve this answer

Your Answer


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.