@@IDENTITY和触发器问题

Question

我遇到了一个关于触发器和@@IDENTITY的众所周知的问题。 我创建了一个新的审计表和一个触发器来在其中插入审计行。 我们使用一款软件，它正在使用@@IDENTITY，这会导致与触发器插入新行时生成的Id发生冲突。 我无法访问正在使用@@IDENTITY的代码。

我需要想法来自己生成身份值。我不能使用GUID，因为顺序对我很重要。 如果我将Id列替换为带有默认值GETDATE()的datetime列，那么它是否保证是唯一的？

谢谢

- ilabrada

Answer 1

GETDATE（）不会是唯一的。其准确性使得多个近并发事件可以被赋予相同的时间。

如果您被强制生成自己的标识值，以避免干扰@@IDENTITY，则可以执行以下操作...

INSERT INTO
  myTable (
    id,
    field1,
    field2
  )
SELECT
  (SELECT ISNULL(MAX(id), 0) FROM myTable WITH(TABLOCKX)) + 1,
  @p1,
  @p2

这是隐式地位于自己的事务中，将保证唯一值。

编辑

我的原始评论是，在插入多个记录时，这种方法将无法正常工作，你需要逐个遍历源记录，并逐个插入它们。

但是，下面的示例可能适用于处理数据集合...

WITH
  sorted_data AS
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY field1) AS set_id,   -- DO NOT include a PARTITION here
    *
  FROM
    inserted
)
INSERT INTO
  myTable (
    id,
    field1,
    field2
  )
SELECT
  (SELECT ISNULL(MAX(id), 0) FROM myTable WITH(TABLOCKX)) + set_id,
  @p1,
  @p2
FROM
  sorted_data

这将为每一行生成唯一的ID，并且在多个进程同时使用相同代码时也是安全的。

编辑：我添加了“WITH(TABLOCKX)”以防止其他进程在更新表时从中读取。这可以防止并发进程建立相同的MAX(id)，然后尝试在新记录中插入重复的id。

（单个查询结构已经防止记录在被读取后被修改，但是不能防止其他进程在读取MAX(id)和插入所有新记录之间从表中读取数据。）

- MatBailie

---

你的代码是并发安全的吗？即使触发器插入多行数据？ - ilabrada

是的，这是隐式地在它自己的事务中执行的，并且将保证唯一值。 - MatBailie

即使触发器插入多行，也是这样吗？ - ilabrada

我很好奇隐式事务是如何工作的，因为我不完全确定，但如果在没有锁定myTable的情况下执行两个插入语句，会有什么阻止生成重叠的ID集吗？因为此时MAX（ID）被评估。 - Michael Fredrickson

如果在一个语句中获取了 MAX(ID)，然后在另一个语句中使用它，除非使用事务和表/行锁定，否则会创建竞争条件。然而，当 MAX(ID) 在子查询中获取时，整个查询是一个单独的语句，因此已经隐式地处于自己的事务中。 - MatBailie

在写那个评论的时候，我想到了一些事情... 单个语句的ACID特性将保证不会更改正在读取以生成MAX(id)的数据，但它并不能保证其他东西不会在查找MAX(id)和完成插入新记录之间从该表中读取。这意味着仍然需要锁定提示来防止从表中进行读取。我已经更新了答案并且承认错误。 - MatBailie

Answer 2

我知道你可能无法改变事情，但问题在于软件正在使用@@IDENTITY，而它不在作用域内。插入到任何表中都会更改@@IDENTITY。软件应该使用函数scope_identity()。

- datagod