SQL Server:在选择中使用表锁提示以确保正确性?

Question

我有一个项目正在尝试应用DDD(领域驱动设计)。目前，我们有这样的东西：

begin tran
try
_manager.CreateNewEmployee(newEmployeeCmd);
tran.Commit();
catch
rollback tran

在内部，CreateNewEmployee 方法使用域服务来检查是否已有具有 memberId 的员工。下面是一些伪代码：

void CreateNewEmployee(NewEmployeeCmd cmd)
if(_duplicateMember.AlreadyRegistered(cmd.MemberId) )
throw duplicate
// extra stuff
saveNewEmployee()
end

现在，最后，就好像我们执行了以下SQL指令(再次是pesudo代码)：

begin sql tran
select count(*) from table where memberId=@memberId and status=1 -- active
--some time goes by
insert into table ...
end

现在，当我开始查看代码时，我注意到它使用的是默认的SQL Server锁定级别。在实践中，这意味着可能会发生这样的事情：

--thread 1
(1)select ... --assume it returns 0
--thread 2
(2)select ... ---nothing found
(3)insert recordA
--thread 1
(4)insert record --some as before
(5) commit tran
--thread 1
(6) commit tran

因此，我们最终可能会有重复的记录。我尝试过使用事务级别，但我设法使其按预期工作的唯一方法是更改用于检查表中是否已有记录的选择。我最终使用了一个表锁提示，它指示 sql 在事务结束之前保持锁。这是我在选择开始时设法锁定的唯一方法(更改其他隔离级别仍然无法满足我的需求，因为它们都允许选择运行)

因此，我最终使用了从事务开始到结束一直保持的表锁。实际上，这意味着步骤 (2) 将阻塞，直到线程 1 结束其作业。

对于这种场景(不依赖于使用索引)是否有更好的选择？

谢谢。

路易斯

Answer 1

您需要在初始select上获得正确的锁，您可以使用锁定提示with (updlock, serializable)来完成。执行此操作后，如果线程 2 在其where中使用相同的键范围，则线程 2 将等待线程 1 完成。

您可以使用Sam Saffron更新插入方法。

例如：

create procedure dbo.Employee_getset_byName (@Name nvarchar(50), @MemberId int output) as
begin
set nocount, xact_abort on;
begin tran;
select  @MemberId = Id
from  dbo.Employee with (updlock, serializable) /* hold key range for @Name */
where Name = @Name;
if @@rowcount = 0 /* if we still do not have an Id for @Name */
begin;
/* for a sequence */
set @MemberId = next value for dbo.IdSequence; /* get next sequence value */
insert into dbo.Employee (Name, Id)
values (@Name, @MemberId);
/* for identity */
insert into dbo.Employee (Name)
values (@Name);
set @MemberId = scope_identity();
end;
commit tran;
end;
go