Skip to content

SQL Server: When retrieving generated columns in insert, concurrency may cause us not to get the original generated value #27446

@roji

Description

@roji

When inserting a new entity instance which has both identity and other generated values, we current use the INSERT+SELECT pattern:

INSERT INTO [Blogs] ([Name])
VALUES (@p0);
SELECT [Id], [Foo]
FROM [Blogs]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

Although the two statements are wrapped in a transaction, we use the default isolation level, which is read committed; this means that if an update happens between the INSERT and the SELECT, the SELECT returns the updated value for Foo, and not the originally-generated value.

I'm not sure this is an actual problem, but it does seem like we should ideally propagate back the original generated values, and not be sensitive to a race condition such as this. Switching to a single-command method (SELECT ... OUTPUT) would remove this problem, but would degrade performance when triggers are present (SELECT ... OUTPUT INTO) - see #27372 for more info.

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions