Deleting multiple package versions in parallel via API causes SQL deadlock

[mike_5084]

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

[atripp]

Hi @mike_5084 ,

Thanks for sharing this! Few things I wanted to share...

1. Retention Rules are definitely the way to do house keeping; it's much simpler and is something that just works in the background

2. We're definitely aware of this particular deletion issue, but it's tricky to reproduce (even using your scenario)... that said, we do have a patch for FeedPackageVersions_DeletePackageVersion available, but we're waiting for some other users to test it before releasing in the mainline. It sounds like you already deleted the packages, so it's not a big impact.

3. We introduced the Common Packages in ProGet 2023, and we will likely introduce a "Rate limit" of some kind in ProGet 2024 Free edition, since retention policies are one of the features that help "sell" ProGet to managers

Cheers,
Adam

[mike.clayton_3236]

Hi Adam,

Thanks for the response.

I think a rate limit on the API for Free licenses would be fair - I'd prefer that to a capability restriction in any case :-).

The script I've been working is an evolution of a very basic one I wrote about 5 years ago that simply deletes the *.nupkg files off the filesystem storage if they're older than a certain date, so the package versions that my new one is deleting via the API are mostly a historic backlog of the version "stub" records - going forward the daily deletions will be a lot fewer so a rate limit wouldn't be an issue.

This is all part of some work we're doing to migrate our Azure VM-based ProGet Free instance to an Azure Kubernetes container instance - we actually got it working pretty quickly and we're happy we can remove another VM from our infrastructure landscape.

It's also going to allow us to reduce our Azure resource costs by quite a bit, so I'm hoping I can make a case to our team lead that we should re-invest that in an annual Basic license to take advantage of some of the additional features - I'm particularly interested in the package scanning and license scanning capabilities so .

As an aside, I did find a small snag with the official Inedo ProGet container registry - it was mostly a problem our side but I'll post a separate ticket about it for completeness...

Thanks again.

M