时间戳

首页 > 解决方案 > SQL Server：仅锁定一行并在并发访问中更新它

sql - SQL Server：仅锁定一行并在并发访问中更新它

问题描述

请在以下情况下帮助我。

我有一张桌子

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table1](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [int] NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

我插入了两行：(id = 1, value = 10), (id = 2, value = 20)

然后我做了以下步骤：

在一个 SSMS 连接中，我运行一个程序应该只锁定和更新一行

BEGIN TRAN;

DECLARE @Value INT;

SELECT @Value = Value
FROM Table1 with (HOLDLOCK XLOCK ROWLOCK)
WHERE Id = 1;

WAITFOR DELAY '00:00:20';

UPDATE Table1 
SET Value = @Value + 5
WHERE Id = 1;

COMMIT TRAN;

SELECT Value 
FROM Table1
WHERE Id = 1

之后我运行另一个 SSMS 连接事务

SELECT [Id], [Value]
FROM [dbo].[Table1]
WHERE Id = 2

我观察到第二个连接冻结并等到第一个事务即将结束。

我不明白为什么第二笔交易要等第一笔。第一个事务锁定 id=1 的行，第二个事务只选择一个 id=2 的行。在我看来，第一笔交易就像我使用了TABLOCKX提示一样。

有人可以解释一下，或者给个建议吗？谢谢！

标签： sqlsql-servertsql

解决方案

申请后

 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

以下交易一切正常

SELECT [Id]
      ,[Value]
  FROM [dbo].[Table1] with (XLOCK, ROWLOCK)
  where Id = 1
GO

和

BEGIN TRAN;

DECLARE @Value INT;

SELECT @Value = Value
--FROM Table1 with (HOLDLOCK XLOCK ROWLOCK)
FROM Table1 with (XLOCK ROWLOCK)
WHERE Id = 1;

WAITFOR DELAY '00:00:10';

UPDATE Table1 
SET Value = @Value + 5
WHERE Id = 1;

COMMIT TRAN;

SELECT Value 
FROM Table1
WHERE Id = 1;

谢谢！

推荐阅读