What happens is your [dbo].[PackageNameIds_GetOrCreatePackageNameId] picks up the first row and tries to update this the value of the second row (and fails with a constraint).

https://www.diffchecker.com/zyfzX4ev/

Does this fix seem reasonable?

We occasionally experience SQL timeouts with ProGet, which is set up in HA (3 nodes). These timeouts occur randomly, sometimes under heavy load, but also during lighter loads. Surprisingly, the CPU usage of the SQL server remains low, so it doesn't seem to be struggling. What is worrying is that when we profiled our SQL instance, we observed that ProGet is sending thousands of 'Attention' events per minute. Is this expected?

We experienced the same problem with version 2023.31 and recently updated to 2024.3, hoping it would solve the issue, but unfortunately, it still occurs.

Example Npm exception:

Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
---> System.ComponentModel.Win32Exception (258): The wait operation timed out.
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at Microsoft.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadByteArray(Span`1 buff, Int32 len, Int32& totalRead)
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadPlpBytes(Byte[]& buff, Int32 offset, Int32 len, Int32& totalBytesRead)
at Microsoft.Data.SqlClient.SqlDataReader.TryGetBytesInternal(Int32 i, Int64 dataIndex, Byte[] buffer, Int32 bufferIndex, Int32 length, Int64& remaining)
at Microsoft.Data.SqlClient.SqlDataReader.GetBytes(Int32 i, Int64 dataIndex, Byte[] buffer, Int32 bufferIndex, Int32 length)
at Inedo.Data.StrongDataReader.ReadBytes(DbDataReader reader, Int32 ordinal, String propertyName, BufferData bufferData)
at lambda_method55(Closure, DbDataReader, BufferData)
at Inedo.Data.StrongDataReader.<Read>g__readRow|11_0[TRow](<>c__DisplayClass11_0`1&)
at Inedo.Data.StrongDataReader.Read[TRow](IDbDataResult dbResult)+MoveNext()
at Inedo.Data.StrongDataReader.Read[TRow](Func`1 getReader, Boolean disposeReader)+MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Inedo.ProGet.WebApplication.FeedEndpoints.Npm.NpmPackageMetadataHandler.TryProcessRequestAsync(AhHttpContext context, WebApiContext apiContext, NpmFeed feed, String relativeUrl)
at Inedo.ProGet.WebApplication.FeedEndpoints.Npm.NpmHandler.ProcessRequestAsync(AhHttpContext context, WebApiContext apiContext, NpmFeed feed, String relativeUrl)
at Inedo.ProGet.WebApplication.FeedEndpoints.FeedEndpointHandler.FeedRequestHandler.ProcessRequestAsync(AhHttpContext context)
ClientConnectionId:8e300222-5b4c-40cb-8100-1334cf7d17db
Error Number:-2,State:0,Class:11

Example Nuget exception:

Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
---> System.ComponentModel.Win32Exception (258): The wait operation timed out.
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at Microsoft.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadByteArray(Span`1 buff, Int32 len, Int32& totalRead)
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadPlpBytes(Byte[]& buff, Int32 offset, Int32 len, Int32& totalBytesRead)
at Microsoft.Data.SqlClient.SqlDataReader.TryGetBytesInternal(Int32 i, Int64 dataIndex, Byte[] buffer, Int32 bufferIndex, Int32 length, Int64& remaining)
at Microsoft.Data.SqlClient.SqlDataReader.GetBytes(Int32 i, Int64 dataIndex, Byte[] buffer, Int32 bufferIndex, Int32 length)
at Inedo.Data.StrongDataReader.ReadBytes(DbDataReader reader, Int32 ordinal, String propertyName, BufferData bufferData)
at lambda_method39(Closure, DbDataReader, BufferData)
at Inedo.Data.StrongDataReader.ReadAllAsync[TRow](IDbDataResult dbResult)
at Inedo.Data.DatabaseContext.ExecuteTableAsync[TRow](String storedProcName, GenericDbParameter[] parameters)
at Inedo.Data.DatabaseContext.ExecuteTableAsync[TRow](String storedProcName, GenericDbParameter[] parameters)
at Inedo.ProGet.Feeds.NuGet.NuGetFeed.GetPackageV3Async(String packageId, PackageVersion`1 nuGetVersion)
at Inedo.ProGet.WebApplication.FeedEndpoints.NuGet.V3.PackageMetadataHandler.ProcessRequestAsync(NuGetFeed feed, AhHttpContext context, WebApiContext apiContext, String relativeUrl)
at Inedo.ProGet.WebApplication.FeedEndpoints.NuGet.NuGetFeedHandler.ProcessRequestAsync(AhHttpContext context, WebApiContext apiContext, NuGetFeed feed, String relativeUrl)
at Inedo.ProGet.WebApplication.FeedEndpoints.FeedEndpointHandler.FeedRequestHandler.ProcessRequestAsync(AhHttpContext context)
ClientConnectionId:bf5213a4-e81f-4eb1-80ff-5cd43685e3f0
Error Number:-2,State:0,Class:11

Regards,
Pawel

Thanks @stevedennis for the answer.
Can we expect any improvement on that in the near future?

Regards,
Pawel

We found some potential issues with the ProGet npm feed. We can observe this on both our instances (test - version 2024.3, and prod - 23.0.31)

So when using public npm registry npm works like expected and is using cached packages:
npm http fetch GET 200 https://registry.npmjs.org/xml-name-validator 2575ms (cache hit)

While using ProGet registry it's updating the cache every time:
npm http fetch GET 200 https://proget.wtg.zone/npm/Registry/xml-name-validator 2924ms (cache updated)

(logs from npm ci --verbose)

Any hints on this?

Regards,
Pawel Ostrowski