SQL Server SCOPE_IDENTITY() - 线程安全

发布于 2024-11-01 21:23:48 字数 652 浏览 1 评论 0原文

我们必须修改数据库并自己管理 IDENTITY 列（而不是依赖于 auto-inc 字段）。

我们的解决方案是有一个“生成器”表，其中有一个 auto-inc 字段。我们插入到该表中，然后读取 SCOPE_IDENTITY 值来获取新 ID，例如，

insert into NewIDEntity
        (CreationDate)
        select
        GetDate()

select @EntityID = SCOPE_IDENTITY()

我们主要关注以下场景：

• 事务 1 插入 NewID 并接收 101 作为要插入到实体表中的新 ID。
• 在提交之前，事务 2 插入 NEWID 并接收 101 作为新 ID（这是因为 SCOPE_IDENTITY() 将返回当前作用域上下文中的 ID。原始行尚未提交，因此我们期望值为 101)

• 事务 1 提交并写入该行。
• 事务2 尝试提交，但101 已被写入，导致主键冲突并中止事务。

然而，在运行时，SCOPE_IDENTITY() 似乎是由 SQL Server 处理的，即使在 READ UNCOMMITTED 隔离级别下运行，我们也不会遇到冲突。

这可以吗？还是有我们无法找到的问题？

谢谢邓肯

原文

We have to modify our database and manage the IDENTITY column ourselves (rather than relying on an auto-inc field).

Our solution is to have a "Generator" table, which has an auto-inc field. We insert to this table and then read the SCOPE_IDENTITY value to get our new ID, e.g.

insert into NewIDEntity
        (CreationDate)
        select
        GetDate()

select @EntityID = SCOPE_IDENTITY()

We are mainly concerned with the following scenario:

• Transaction 1 INSERTS into NewID and receives 101 as the new ID to insert into the Entity table.
• Before it can commit, Transaction 2 INSERTS into NEWID and also receives 101 as the new ID (this is because SCOPE_IDENTITY() will return the ID within the context of the current scope. The original row has not been committed yet so we would expect the value to be 101)

• Transaction 1 commits and the row is written.
• Transaction 2 attempts to commit but 101 has already been written and results in a Primary Key violation and aborts the transaction.

However on running this it seems that SCOPE_IDENTITY() is handled by SQL Server, even if running under READ UNCOMMITTED isolation level we get no conflicts.

Is this okay or are there gotchas that we haven't been able to find?

Thanks
Duncan

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无风消散 2024-11-08 21:23:48

身份生成本质上不属于任何事务的范围 - 一旦生成 ID，ID 计数器就会立即增加，而不仅仅是在提交事务时。这会导致身份序列在事务回滚时出现间隙，但这是因为它在您描述的那种情况下保证了安全。

回复收藏 0 原文

久而酒知 2024-11-08 21:23:48

我同意 Araqnid 的回答，但更重要的是，除非您想保留生成的 id 的历史记录，否则您也可以在完成后删除它们。我们还存在以特定方式（以其他值作为前缀）生成 ID 的业务需求，而我执行此操作的方法是使用一个仅包含标识字段的表。然后我的代码如下所示：

/*
 * There is no "user data" being added to the table, so we just use
 * "Default Values".
 */
Insert  Into dbo.MasterIds Default Values

Set @MasterId = Scope_Identity()

/*
 * We don't keep just serial numbers on this table. Now that we have
 * the new MasterId delete it from the table.
 */
Delete  From dbo.MasterIds
Where   MasterId = @MasterId

Select  @MasterId As MasterId

希望有帮助。

I agree with Araqnid's answer, but just as a finer point, unless you are wanting to keep a history of generated ids, then you can also delete them when you are done. We also have a business requirement of generating IDs in a particular way (prefixed with other values) and the way I do this is by having a table that only has the identity field in it. Then my code looks like this:

/*
 * There is no "user data" being added to the table, so we just use
 * "Default Values".
 */
Insert  Into dbo.MasterIds Default Values

Set @MasterId = Scope_Identity()

/*
 * We don't keep just serial numbers on this table. Now that we have
 * the new MasterId delete it from the table.
 */
Delete  From dbo.MasterIds
Where   MasterId = @MasterId

Select  @MasterId As MasterId

Hope that helps.

回复收藏 0 原文

~没有更多了~