我正在开发一个应用程序，该应用程序将EF6用于大多数数据库操作，并且在大多数情况下，数据库内容是非关键的，不会承受太多负载。这个规则有一个例外，我们有一个可能是100/s的事件流，需要根据列值在特定表中插入或更新行。
一般来说，我对SQL有点不熟悉，但我编写这个查询是为了插入或更新并返回元素的id：
DECLARE @Id [int];
MERGE {tableName} WITH (UPDLOCK) AS target
USING (SELECT @MatchName AS id) AS source
ON source.id = target.MatchColumn
WHEN MATCHED
THEN UPDATE SET @Id = target.Id, ...
WHEN NOT MATCHED
THEN INSERT (...) VALUES (...);
IF @Id IS NULL
BEGIN
SELECT @Id = CAST(SCOPE_IDENTITY() as [int]);
END;
SELECT @Id;
它是在(EF)可序列化事务块内完成的，是在显式事务中唯一执行的东西，也是更新此表的唯一代码。(其他内容可以阅读)。如果事务被数据库回滚(EF抛出异常)，则会立即重试，最多重试3次。
问题是，当我们开始进入更高负载的情况时，我们可能会最终处于这样的状态，即许多事情都在尝试更新数据库，而对该表的查询可能需要30多秒(对其他表的查询仍然可以)。我的印象是，即使这在可序列化事务中执行，它也只会锁定由合并匹配表达式选择的行，这应该是一个相对快速的操作。
过去几天我一直在做一些研究，有些人认为在默认事务中只有HOLDLOCK就足够了，而另一些人则认为有必要使用可序列化事务，否则可能会出现数据完整性问题。
我希望有人能解释为什么会发生长时间的死锁，以及在这种情况下什么锁定机制是最佳的。
默认情况下，merge获取updlocks，因此with (updlock)不会为您做任何事情。将updlock更改为holdlock(或serializable)并在事务中运行该语句将确保在操作期间获得并保持正确的锁。
为了防止并发会话插入具有相同密钥的数据，必须获取不兼容的锁，以确保只有一个会话可以读取密钥，并且该锁必须保持到事务完成。
- UPSERT竞赛条件与
Merge-Dan Guzman
您需要使用set transaction isolation level serializable吗？
在这种情况下，如果以上代码是事务中的全部内容，我认为将事务隔离级别显式设置为serializable不会有任何区别。默认情况下，merge with (holdlock)将获取与共享锁不兼容的更新锁，使用holdlock提示解决了竞争条件问题，正如Dan Guzman在参考文章和以上摘录中所解释的那样。
with (holdlock)是一个表提示。表提示覆盖语句的默认行为。
如果事务中有其他语句，那么这些语句将受到事务隔离级别与默认隔离级别或显式设置的set transaction isolation level(即会话级别)的差异的影响，除非使用表提示重写。
最高粒度获胜：
- 最低：数据库(默认为
read committed) - 中：会话(
set transaction isolation level ...) - 最高：表格提示(
with (updlock, serializable))
有关事务隔离级别的更多信息：
- SQL Server隔离级别：A系列-Paul White
- 关于T-SQL事务隔离级别的问题你害羞得不敢问-Robert Sheldon