SQL Server: Effect of Setting These Index Options

Question

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?

Answer 1

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);

CREATE CLUSTERED INDEX IX_t1
    ON t1(a)
        WITH(ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); /* Default */


BEGIN TRANSACTION;

    UPDATE t1
        SET a = 10
        WHERE a = 1;

    EXEC sp_lock;

ROLLBACK;

DROP TABLE t1;

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!