SQL Server: Effect of Setting These Index Options

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.

• Was setting the locking options on the indexes the direct cause?

• Why exactly would those settings cause deadlocks?

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

(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!

StackExchange Database Administrators Q#20956, answer score: 6