如何仅阻止同一用户在 SQL Server 中进行插入 - 可序列化隔离

Question

我有下表现

CREATE TABLE [dbo].[Test]([Id] [int] NOT NULL,[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_Test] 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

CREATE NONCLUSTERED INDEX [IX_Test] ON [dbo].[Test] 
(
   [Name] ASC
)
WITH (PAD_INDEX  = OFF, 
STATISTICS_NORECOMPUTE  = OFF, 
SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, 
DROP_EXISTING = OFF, 
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

在我想阻止来自同一用户（但不同会话）的插入，直到正在进行的会话完成。例如，如果用户 John 使用插入启动会话，我希望阻止他的其他会话（也使用插入），直到第一个会话完成。换句话说，我需要阻止尚不存在的插入。

因此，我创建了以下两个脚本来在单独的 Management studio 查询窗口中运行它们： 第一（用户 John；waitfor 在第一个查询中只是为了能够模拟问题）：

begin tran

select COUNT(*)
from Test with(updlock,holdlock)
where Name = 'John'

waitfor delay '00:00:05'

insert into Test(Id,Name)
select isnull(MAX(Id) + 1,1),'John' from Test

commit tran

第二（用户 Peter）：

begin tran

select COUNT(*)
from Test with(updlock,holdlock)
where Name = 'Peter'

insert into Test(Id,Name)
select isnull(MAX(Id) + 1,1),'Peter' from Test

commit tran

现在，如果我从第一个 Management Studio 查询窗口启动第一个查询，然后在第二个查询窗口中启动第二个查询第一个查询已完成，第二个查询将被阻止，直到第一个查询完成。 好吧，也许这就是应该如何工作，但这不是我想要的：我希望仅当同一用户想要插入数据时才阻止第二个会话。在我的示例中，John 开始插入数据，但 Peter（第二个查询）被阻止，直到 John 的插入完成。

是否可以指示 sql server（我想带有锁定提示）仅在同一用户尝试插入数据时阻止第二个查询？例如，如果 John 开始插入数据以仅阻止 John 的其他会话，而不阻止 Peter 的会话？

Answer 1

在这种情况下，您可能需要在数据库中设置一个特定于用户的标志（使用 user_id 和 is_writing 标志创建表）。当开始向表中写入时，首先检查标志是否已设置，如果未设置，则更新标志并写入表。写入完成后取消设置标志。

这就是我可能会解决这个问题的方法。但我愿意接受任何其他方式；）