I am currently getting quite a few deadlock exceptions. These are occurring every few minutes at peak traffic times. They tend to gradually get worse and then the application starts to become a little sluggish and an app service restart usually resets the clock.
System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 138) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) in D:\a\1\s\src\Umbraco.Core\Persistence\FaultHandling\RetryPolicy.cs:line 172
at NPoco.Database.ExecuteNonQueryHelper(DbCommand cmd)
at NPoco.Database.Execute(String sql, CommandType commandType, Object[] args)
at NPoco.Database.Delete[T](String sql, Object[] args)
at Vendr.Core.Persistence.Repositories.OrderRepository.Save(OrderState state, OrderState originalState)
at Vendr.Core.Services.OrderService.SaveOrder(Order entity)
I have since refactored this and we wrap the FormSubmit and FormRender (mutli step order flow, we are doing some updates to the order on form render) methods within a _uowProvider.Create() using statement after that we get the current order or create one as writeable and pass that writable order through to any other methods which need to update the order. After all these methods have done their thing, we then do _orderService.Save(order) and uow.Complete() (within the top level calling method, FormSubmit | FormRender)
The site is setup on Azure app service P2V2 with autoscaling up to 10 instances, and is using a Azure SQL S6 DB. Typically the site would get around 300concurrent users peaking at around 600~ during very busy periods
Running Umbraco v8.10.1, Vendr v1.5.2.
This is not replicable locally, or other environments so seems like a concurrency issue. Does Vendr have a limit on the number of concurrent users it can handle?
Deadlock debugging is a pain, but unfortunately without knowing what exactly is causing the deadlock, both the code being called and specifically what in the DB is locking, it’s a bit difficult to suggest a fix or implement a fix in Vendr.
Best I can suggest is googling for debugging deadlocks and see if you can find what is actually locking and from what calls these come from.
The only time we’ve seen deadlocks early on was when we had a different order number strategy which required a dB call to get the next order number. But we don’t do that anymore. It’s plausible custom code in event handlers or something could be doing this, but like I say, it’s really hard to say without some debug info.
foreach (var orderLine in order.OrderLines)
{
order.RemoveOrderLine(orderLine.Id);
}
Then directly after that statement, We also add products to the order, I think the use of this logic was to clear the basket prior to adding items to basket
In theory the code for removing the order lines is fine because all that is affecting at that point is the in memory state. It's only when you call SaveOrder that the items are removed from the DB, which is done by looking for what order lines have been removed compare to it's initial state (before you called AsWritable) and then it attempts to delete them in a batch.
The SQL around this has changed a little over time, but not anything I would deem significant and I know some people are running Vendr in high traffic sites so it really should be able to handle it.
I guess really it may ultimately depend on what else is going on in the transaction and whether concurrent connection are causing problems performing everything 🤔
Database Deadlocks
I am currently getting quite a few deadlock exceptions. These are occurring every few minutes at peak traffic times. They tend to gradually get worse and then the application starts to become a little sluggish and an app service restart usually resets the clock.
The application was previously not following the UOW best practice noted here https://vendr.net/docs/core/1.4.0/key-concepts/unit-of-work/#unit-of-work-best-practice
I have since refactored this and we wrap the FormSubmit and FormRender (mutli step order flow, we are doing some updates to the order on form render) methods within a _uowProvider.Create() using statement after that we get the current order or create one as writeable and pass that writable order through to any other methods which need to update the order. After all these methods have done their thing, we then do _orderService.Save(order) and uow.Complete() (within the top level calling method, FormSubmit | FormRender)
The site is setup on Azure app service P2V2 with autoscaling up to 10 instances, and is using a Azure SQL S6 DB. Typically the site would get around 300concurrent users peaking at around 600~ during very busy periods
Running Umbraco v8.10.1, Vendr v1.5.2.
This is not replicable locally, or other environments so seems like a concurrency issue. Does Vendr have a limit on the number of concurrent users it can handle?
Hey,
Deadlock debugging is a pain, but unfortunately without knowing what exactly is causing the deadlock, both the code being called and specifically what in the DB is locking, it’s a bit difficult to suggest a fix or implement a fix in Vendr.
Best I can suggest is googling for debugging deadlocks and see if you can find what is actually locking and from what calls these come from.
The only time we’ve seen deadlocks early on was when we had a different order number strategy which required a dB call to get the next order number. But we don’t do that anymore. It’s plausible custom code in event handlers or something could be doing this, but like I say, it’s really hard to say without some debug info.
Hey Matt,
I've just got the deadlock log of an example of a deadlock that occurs
I think this corresponds to this piece of code:
Then directly after that statement, We also add products to the order, I think the use of this logic was to clear the basket prior to adding items to basket
In theory the code for removing the order lines is fine because all that is affecting at that point is the in memory state. It's only when you call
SaveOrderthat the items are removed from the DB, which is done by looking for what order lines have been removed compare to it's initial state (before you calledAsWritable) and then it attempts to delete them in a batch.The SQL around this has changed a little over time, but not anything I would deem significant and I know some people are running Vendr in high traffic sites so it really should be able to handle it.
I guess really it may ultimately depend on what else is going on in the transaction and whether concurrent connection are causing problems performing everything 🤔
is working on a reply...