Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Frequent deadlocks on concurrent inserts with identity columns #25345

Closed
AndriySvyryd opened this issue Jul 27, 2021 · 5 comments · Fixed by #25718
Closed

Frequent deadlocks on concurrent inserts with identity columns #25345

AndriySvyryd opened this issue Jul 27, 2021 · 5 comments · Fixed by #25718
Labels
area-save-changes area-sqlserver closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-bug
Milestone
6.0.0

Comments

@AndriySvyryd
Copy link
Member

The deadlocks are caused by the SELECT statement that reads the generated values.
These could be avoided by using ReadUncommitted isolation level by default or by adding READUNCOMMITTED table hint.
@AndriySvyryd
Copy link
Member Author

Alternatively use an OUTPUT clause instead.

@roji
Copy link
Member

roji commented Jul 29, 2021

Using ReadUncommitted could be problematic if there are any other reads in the transaction (i.e. if it's a user-initiated transaction which spans more than just the SaveChanges). But if we can specify READUNCOMMITTED only on the SaveChanges-generated SELECTs, that sounds great.

Could be somewhat related to #9118, in that we wouldn't fetch generated values at all.

@ajcvickers ajcvickers added this to the Backlog milestone Jul 30, 2021
@ajcvickers
Copy link
Member

See #7188 and #1441 for the triggers issue.

@ajcvickers ajcvickers removed this from the Backlog milestone Jul 30, 2021
@AndriySvyryd
Copy link
Member Author

AndriySvyryd commented Jul 30, 2021 •
edited
Loading

We might still be able to use OUTPUT INTO with a temporary table and avoid the join if the only generated value is the identity

@ajcvickers ajcvickers added this to the Backlog milestone Aug 3, 2021
@AndriySvyryd
Copy link
Member Author

Another trigger issue: #12064

@AndriySvyryd AndriySvyryd modified the milestones: Backlog, 6.0.0 Aug 25, 2021
@AndriySvyryd AndriySvyryd added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Aug 25, 2021
AndriySvyryd added a commit that referenced this issue Aug 25, 2021
@AndriySvyryd
Don't use MERGE for single row insert with non-identity store-generat…
64244ea 
…ed key

Don't join the temporary table with the target table if only key values are store-generated

Fixes #25712
Fixes #25345
@AndriySvyryd AndriySvyryd mentioned this issue Aug 25, 2021
Merged
AndriySvyryd added a commit that referenced this issue Aug 27, 2021
@AndriySvyryd
Don't use MERGE for single row insert with non-identity store-generat…
ffc6bb6 
…ed key

Don't join the temporary table with the target table if only key values are store-generated

Fixes #25712
Fixes #25345
@ajcvickers ajcvickers modified the milestones: 6.0.0, 6.0.0-rc2 Aug 28, 2021
@ajcvickers ajcvickers modified the milestones: 6.0.0-rc2, 6.0.0 Nov 8, 2021
@ajcvickers ajcvickers mentioned this issue Feb 4, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area-save-changes area-sqlserver closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-bug
Projects
None yet
Milestone
6.0.0
Development

Successfully merging a pull request may close this issue.

Don't use MERGE for single row insert with non-identity store-generated key dotnet/efcore
3 participants
@ajcvickers @roji @AndriySvyryd