如何强制使用行锁？

Question

我正试着让我的桌子使用行锁。我已经禁用了锁升级。隔离级别为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

在设置这个之后，它还是不起作用。

为了不使用页面锁定，我需要重新构建我的表吗？

Answer 1

禁用页面锁定的设置适用于每个索引，因此将此更改应用于聚集索引只会影响通过该索引访问数据的执行计划。如果表中有非聚集索引，则可能还必须禁用它们的页面锁定。下面的脚本演示了这一点：

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

使用聚集索引作为访问方法：

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

使用非聚集索引：

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

请注意，禁用页面锁定可能会产生意外的副作用，例如防止索引重组(因为此过程在页面级别工作)：

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