sql-server - 不清楚我需要哪种类型的锁来避免在唯一字段上重复插入

Question

我需要检查表中是否存在唯一(非主键)字段，如果不存在 - 插入它:

ID unqiueidentifier, --PK
name varchar(100),
otherID int --Unique

也许使用条件插入:

DECLARE @OtherID int
INSERT INTO TableA (OtherID)
SELECT @OtherID
WHERE NOT EXIST (SELECT * from TableA where OtherID = @OtherID)

SELECT MainID from TableA where OtherID = @OtherID

引用:Whilst locked - see if otherID exists, if so return mainID, if not insert otherID & return new mainID

我需要哪种锁/提示来确保并发进程在检查和插入之间的时间内不会插入相同的 OtherID(显然，即使在单个语句中，并发性仍然是一个问题如上)。

引用:http://kejser.org/race-condition-when-creating-unique-values/

引用:http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx

我找到了很多关于HOLDLOCK/UPDLOCK的资料。我了解他们如何暂停 SELECTING 或 UPDATING existing rows。没有意义的是他们如何锁定 INSERTING 新行，而基本上只是锁定整个表 - 又名 TABLOCK。

如何锁定不存在的行？!或者 UPDLOCK 只是锁定上面的整个表，因为您正在执行 SELECT *？

如果它确实在整个表上加锁 - 这似乎会造成很大的破坏并且不利于扩展？!也许我只是强制执行 UNIQUE 约束并允许偶尔出现异常？

我在这里看不到任何好的选择？!

Answer 1

这是更新插入的一个很好的引用，但它也可以应用于这种情况:Sam Saffron upsert approach

要获取键范围锁(以免锁定整个表)，您需要一个以OtherId 为键的索引，最好是唯一的，以便使用with (updlock, serializable)(等同于with (updlock, holdlock))。

declare @OtherId int;

insert into TableA (OtherId)
select @OtherId
where not exists (
  select OtherId 
  from TableA with (updlock, serializable) 
  where OtherId = @OtherId
)

select Mainid 
from TableA 
where OtherId = @OtherId;

引用:

• Documentation on serializable and other Table Hints - MSDN
• Key-Range Locking - MSDN
• Sam Saffron''s upsert (update/insert) patterns
• SQL Server Isolation Levels: A Series - Paul White