Hello,
Just posting this here for visibility really as I've got a viable workaround...
I've been working on some housekeeping scripts to delete a large number of legacy package versions from a private NuGet feed (hosted on ProGet running in a container on an Azure Kubernetes with an Azure SQL Database in case the details matter) using the ProGet Delete Package Endpoint.
Due to the number of package versions involved (10's of thousands) I'm running the deletes via the API in parallel from multiple threads (specifically, I'm using PowerShell's foreach-object -parallel and Invoke-RestMethod) to delete specific versions of packages.
When this script is running I frequently see failed requests like the following in the ProGet Diagnostic Log while it's actively deleting multiple package versions in parallel:
Unhandled exception processing http://[myserver]/api/packages/[myfeed]/delete?name=[mypackage]&version=[myversion]
Details:Microsoft.Data.SqlClient.SqlException (0x80131904): 1205`13`51`FeedPackageVersions_DeletePackageVersion`36`Transaction (Process ID 218) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
...snip ...
at Inedo.ProGet.Feeds.StandardPackageFeed`3.DeletePackageFromDatabase(PackageVersionId id, Context db) in C:\Users\builds\AppData\Local\Temp\InedoAgent\BuildMaster\192.168.44.60\Temp\_E400384\Src\ProGetCoreEx\Feeds\StandardPackageFeed.cs:line 760
and matching client-side errors like:
Response : StatusCode: 500, ReasonPhrase: 'Internal Server Error', Version: 1.1, Content: System.Net.Http.HttpConnectionResponseContent, Headers:
{
Date: Thu, 18 Jan 2024 09:24:04 GMT
Connection: keep-alive
Cache-Control: private
X-ProGet-Version: 23.0.24.3
Strict-Transport-Security: max-age=15724800; includeSubDomains
Content-Type: text/plain
Content-Length: 337
}
HttpRequestError : Unknown
StatusCode : InternalServerError
TargetSite : Void ThrowTerminatingError(System.Management.Automation.ErrorRecord)
Message : Response status code does not indicate success: 500 (Internal Server Error).
Data : {}
InnerException :
HelpLink :
Source : System.Management.Automation
HResult : -2146233088
StackTrace : at System.Management.Automation.MshCommandRuntime.ThrowTerminatingError(ErrorRecord errorRecord)
If I run the diagnostic steps in Analyze and prevent deadlocks in Azure SQL Database it tells me that the deadlock is being caused by two instances of the following query running at the same time:
SELECT @FPCT = (
SELECT COUNT(*)
FROM [FeedPackages] FP WITH (UPDLOCK, SERIALIZABLE)
LEFT JOIN [PackageVersionIds] PVI WITH (UPDLOCK,SERIALIZABLE) ON
PVI.[PackageName_Id] = FP.[PackageName_Id]
LEFT JOIN [FeedPackageVersions] FPV WITH (UPDLOCK,SERIALIZABLE) ON
PVI.[PackageVersion_Id] = FPV.[PackageVersion_Id]
WHERE FP.[Feed_Id] = @Feed_Id AND FP.[PackageName_Id] = @PackageName_Id
In terms of impact, my script has cleaned up about 99% of the historic package versions that we need to delete, so I've resolved the issue by running it again single-threaded to delete the remaining few package versions (using foreach-object -throttlelimit 1 -parallel) - this worked fine so it's not a blocker, but it would be nice to be able to run the entire script in one go using multiple threads for improved performance.
Cheers,
M
