主页 / dba / 问题 / 273765
Accepted
Panzercrisis
Asked: 2020-08-15 08:53:56 +0800 CST

在 SQL Server 中，用外行的话来说，如何将资源锁定到足以执行 INSERT-IF-NOT-EXISTS 事务的程度？

  • 772

如果有人问如何INSERT-IF-NOT-EXISTS在 SQL Server 中执行操作，他们通常会得到这样的答案：

IF NOT EXISTS(SELECT 1 FROM [TheTable] WHERE [ColumnX] = @valX)
    INSERT [TheTable] ([ColumnX]) VALUES (@valX)

我看到的问题是在SELECT声明和INSERT声明之间，情况可能会在外部发生变化。另一个进程可以在语句之后但在语句之前插入ColumnX值，从而导致引发错误。SELECTINSERT

我在软件领域工作了一段时间，但不是数据库专家，当我在 SQL Server 中搜索此问题的答案时，我看到的结果要么无关紧要，要么很难真正应用（因为他们'要么回答不同的问题，要么按照数据库专家量身定制的术语编写）。

那么通俗的讲，你是如何解决这个问题的呢？ 在最近的历史中，我确实对 SQL 有点生疏，但我认为确实应该有一个实用的锁定机制来用于此（无论是否存在）。作为后备，也许错误处理可以专门确定引发的错误是否与这个确切的问题相匹配，在这种特定情况下忽略它。

最好这不涉及每次都锁定整个表。

sql-server locking

1 个回答

  1. Best Answer

    选项 1：取出至少锁定该行将存在的索引中的范围的锁定。

    SET XACT_ABORT ON;
BEGIN TRAN
IF NOT EXISTS(SELECT 1 FROM [TheTable] WITH (UPDLOCK, HOLDLOCK) WHERE [ColumnX] = @valX)
    INSERT [TheTable] ([ColumnX]) VALUES (@valX)
COMMIT

    HOLDLOCK将给出可序列化的语义并锁定索引中现有键之间的范围，该值适合（如果有合适的索引，否则它将锁定整个表）。UPDLOCK减少了这种模式下死锁的可能性，因为两个并发查询在读取阶段不能取出相同的范围锁。

    选项2：您可以只添加一个唯一约束ColumnX并尝试插入并捕获重复键违规引发的错误。

    鉴于选项 1ColumnX无论如何都需要一个带有前导列的索引来满足您不“每次都锁定整个表”的偏好，您不妨添加一个并将其定义为唯一的。无论如何，索引都会加快存在检查。有了这个，我会根据我期望尝试插入重复项的频率在选项 1 和 2 之间进行选择。

    • 4

相关问题