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

Treat SQL Server Error Number 1222 as Transient (Lock request time out period exceeded) #31724

Closed
freakingawesome opened this issue Sep 13, 2023 · 3 comments · Fixed by #31725 or #31739
Closed

Treat SQL Server Error Number 1222 as Transient (Lock request time out period exceeded) #31724

freakingawesome opened this issue Sep 13, 2023 · 3 comments · Fixed by #31725 or #31739
Labels
area-sqlserver closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. community-contribution customer-reported type-enhancement
Milestone
8.0.0

Comments

@freakingawesome
Copy link
Contributor

freakingawesome commented Sep 13, 2023 •
edited
Loading

The SQL Server exception number 1222 (Lock request time out period exceeded) should be treated as a transient exception.

  1. The Microsoft.Data.SqlClient documentation lists 1222 as a transient error in need of a retry (source code for SqlConfigurableRetryFactory.cs contains this number)
  2. sys.messages lists the error text for 1222 as "Lock request time out period exceeded."
  3. Official Microsoft documentation recommends retrying the transaction after it has been aborted

Adding the error number to the giant case statement inside SqlServerTransientExceptionDetector.cs should be enough to satisfy this request.
@freakingawesome freakingawesome changed the title Treat SqlException 1222 as Transient (Lock request time out period exceeded) Treat Sql Error Number 1222 as Transient (Lock request time out period exceeded) Sep 13, 2023
freakingawesome added a commit to freakingawesome/efcore that referenced this issue Sep 13, 2023
@freakingawesome
Treat Sql Error Number 1222 as Transient
4398b77 
Error number 1222 is defined as

> Lock request time out period exceeded

And official Microsoft recommendation is to retry the transaction after it has been aborted [Source](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-1222-database-engine-error)

Fixes dotnet#31724
@freakingawesome freakingawesome mentioned this issue Sep 13, 2023
Merged
6 tasks
@freakingawesome
Copy link
Contributor Author

There are two other error numbers inside the SqlConfigurableRetryFactory class which aren't treated as transient by Entity Framework:

  • 42108, // Can not connect to the SQL pool since it is paused. Please resume the SQL pool and try again.
  • 42109, // The SQL pool is warming up. Please try again.

I didn't include these numbers in this PR explicitly because the are not listed in sys.messages, but they are included in official Microsoft documentation, so perhaps it makes sense to broaden this PR to include those two as well?

@freakingawesome freakingawesome changed the title Treat Sql Error Number 1222 as Transient (Lock request time out period exceeded) Treat SQL Server Error Number 1222 as Transient (Lock request time out period exceeded) Sep 13, 2023
AndriySvyryd pushed a commit that referenced this issue Sep 14, 2023
@freakingawesome
Treat Sql Error Number 1222 as Transient (#31725)
301b01b 
Error number 1222 is defined as

> Lock request time out period exceeded

And official Microsoft recommendation is to retry the transaction after it has been aborted [Source](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-1222-database-engine-error)

Fixes #31724
@AndriySvyryd
Copy link
Member

I didn't include these numbers in this PR explicitly because the are not listed in sys.messages, but they are included in official Microsoft documentation, so perhaps it makes sense to broaden this PR to include those two as well?

You can submit a separate PR for this

1 similar comment
@AndriySvyryd
Copy link
Member

I didn't include these numbers in this PR explicitly because the are not listed in sys.messages, but they are included in official Microsoft documentation, so perhaps it makes sense to broaden this PR to include those two as well?

You can submit a separate PR for this

freakingawesome added a commit to freakingawesome/efcore that referenced this issue Sep 14, 2023
@freakingawesome
Treat SQL Pool warmup/pause errors as transient
433b378 
- [Microsoft Documentation on 42108](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-42108-database-engine-error?view=sql-server-2017)
- [Microsoft Documentation on 42109](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-42109-database-engine-error?view=sql-server-ver16)

Fixes dotnet#31724 (these are the additional error codes referenced in this conversation)
@freakingawesome freakingawesome mentioned this issue Sep 14, 2023
Merged
@AndriySvyryd AndriySvyryd reopened this Oct 7, 2023
@AndriySvyryd AndriySvyryd added this to the 8.0.0 milestone Oct 7, 2023
@AndriySvyryd AndriySvyryd added closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. community-contribution labels Oct 7, 2023
AndriySvyryd pushed a commit that referenced this issue Oct 9, 2023
@freakingawesome
Treat SQL Pool warmup/pause errors as transient (#31739)
726cb83 
Fixes #31724
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area-sqlserver closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. community-contribution customer-reported type-enhancement
Projects
None yet
Milestone
8.0.0
3 participants
@ajcvickers @freakingawesome @AndriySvyryd