sql server:仅锁定一行并在并发访问中更新它

zqry0prt 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(491)

在下列情况下请帮助我。
我有一张table

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 提示。
有人能解释一下吗，或者给个建议？谢谢！

sql sql-server tsql

来源：https://stackoverflow.com/questions/61940836/sql-server-lock-only-one-row-and-update-it-in-concurrence-access

1条答案

按热度按时间

alen0pnh1#

涂抹后

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;

谢谢！

展开查看全部

赞(0）回复(0）举报 2021-08-13

我来回答

sql server:仅锁定一行并在并发访问中更新它

1条答案

相关问题

热门标签

最新问答