时间戳

首页 > 解决方案 > SQL Server 仅强制 RowLock

sql-server - SQL Server 仅强制 RowLock

问题描述

我在更新存储计数器的表的行时遇到了一个大问题。我正在使用事务来获取值并对其进行更新，并且我尝试仅锁定受影响的计数器行以避免锁定和死锁，但它不起作用。

这是一个简化的代码示例，我可以在其中重现错误：

CREATE TABLE _COUNTERS_ 
(
    ID INT IDENTITY NOT NULL, 
    CODE VARCHAR(20) NOT NULL,
    CVALUE INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_ID PRIMARY KEY CLUSTERED 
    (
        ID ASC,
        CODE ASC,
        CVALUE ASC
    ) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF)
)

INSERT INTO _COUNTERS_ (CODE, CVALUE) VALUES ('C1', 0)
INSERT INTO _COUNTERS_ (CODE, CVALUE) VALUES ('C2', 0)

我试图通过索引定义强制避免 PageLocks。

在 SQL Server Management Studio 中，我在一个窗口中执行此语句。我使用第一个查询来锁定 COUNTER 以避免服务器上的其他线程（该应用程序是多线程并安装在场上，因此我不能使用 .NET 锁）在接近更新时得到一个错误的数字 num：

-- Window 1
BEGIN TRAN 
SELECT *
FROM _COUNTERS_ this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.CODE = 'C1'
UPDATE _COUNTERS_ SET CVALUE = 1 WHERE ID = 1

在另一个 SQL Server Management Studio 窗口中，我查询锁定的资源：

-- Window 2
SELECT  L.request_session_id AS SPID,
--        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName,
        P.object_id AS LockedObjectId,
        L.resource_type AS LockedResource,
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,       
--        ES.login_name AS LoginName,
--        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

锁定资源

在第三个窗口中，我执行以下语句：

-- Window 3
BEGIN TRAN 
SELECT *
FROM _COUNTERS_ this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.CODE = 'C2'
UPDATE _COUNTERS_ SET CVALUE = 2 WHERE ID = 2

第一个选择一直等到我提交第一个事务。

是否可以让每个计数器在选择和更新中完全隔离？

关于真实环境的一些额外注意事项：

更新 2020-03-02 使用代码索引（由@larny 评论或由@esat 发布）解决了我发布的示例中的问题，但在我的真实表（VISUALSEGCONTADORES）中，选择未使用新索引：

CREATE INDEX ix_VSGCONTADORES ON VISUALSEGCONTADORES (VSC_ALIAS ASC) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF);

这里是其中一个选择的计划（另一个使用相同的索引）：https ://www.brentozar.com/pastetheplan/?id=BJRWtocE8

以及真正的表结构（带有特殊的聚集索引）： 

CREATE TABLE [dbo].[VISUALSEGCONTADORES](
    [VSC_Id] [int] IDENTITY(1,1) NOT NULL,
    [VSC_Alias] [varchar](30) NOT NULL,
    [VSC_Objeto] [int] NOT NULL,
    [VSC_Serie] [varchar](5) NULL,
    [VSC_Contador] [decimal](20, 8) NOT NULL,
    [VSC_Enabled] [tinyint] NOT NULL,
    [USR_Id_FC] [int] NOT NULL,
    [USR_Id_FM] [int] NOT NULL,
    [VSC_FC] [datetime] NOT NULL,
    [VSC_FM] [datetime] NOT NULL,
    [LOG_ID_FC] [varchar](255) NULL,
    [LOG_ID_FM] [varchar](255) NULL,
    [LOG_FC] [datetime] NULL,
    [LOG_FM] [datetime] NULL,
    [OFI_ID] [int] NULL,
    [VSC_OFICODE] [int] NOT NULL,
    [TRN_Aud_Id_FC] [int] NULL,
    [TRN_Aud_Id] [int] NULL,
 CONSTRAINT [PK_VISUALSEGCONTADORES] PRIMARY KEY CLUSTERED 
(
    [VSC_Alias] ASC,
    [VSC_OFICODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

真正的查询如下：

-- Window 1
BEGIN TRAN 
SELECT *
FROM VISUALSEGCONTADORES this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.VSC_Alias = 'VSG_UltimoCodEXP' AND VSC_OFICODE = 0
UPDATE VISUALSEGCONTADORES SET VSC_Contador = 9910 WHERE VSC_Id = 142

另一个窗口

-- Window 3
BEGIN TRAN 
SELECT *
FROM VISUALSEGCONTADORES this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.VSC_Alias = 'VSG_ULTIMAMATRIZ' AND VSC_OFICODE = 0

UPDATE VISUALSEGCONTADORES SET VSC_Contador = 1273 WHERE VSC_Id = 121

由业务定义，有时查询使用 VSC_OfiCode，有时不使用，但我已经测试了两者并抛出了相同的结果。

锁定资源： 在此处输入图像描述

标签： sql-serverlocking

解决方案

Window3查询尝试在Window1仍在读取的行上获取排他锁。如果您分析以下查询的执行情况。

SELECT *
FROM _COUNTERS_ this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.CODE = 'C2'

在此处输入图像描述

所以这个查询将等到 Window1 查询的提交或回滚。如果您尝试单独执行 Window3 更新，它将被执行

-- Window 3
BEGIN TRAN 
--SELECT *
--FROM _COUNTERS_ this_ WITH (
--        UPDLOCK,
--        ROWLOCK
--        )
--WHERE this_.CODE = 'C2'
UPDATE _COUNTERS_ SET CVALUE = 2 WHERE ID = 2

另一方面，如果我们处理这个问题，我们可以在CODE列上创建一个非聚集索引，或者我们可以像下面这样更改查询

--Window3
BEGIN TRAN 
SELECT *
FROM _COUNTERS_ this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.CODE = 'C2' AND ID=2
UPDATE _COUNTERS_ SET CVALUE = 2 WHERE ID = 2

这两个选项提供避免集群索引扫描

推荐阅读