SqlClient: Queries with MultipleActiveResultSets=True (MARS) are very slow / time out on Linux
Describe the bug
TL;DR:
Queries using connections with MARS enabled, even when they don’t use MARS, are much slower or even time out on Linux. The same queries are fast and reliable on Windows no matter whether MARS is disabled or enabled and on Linux when MARS is disabled.
Context Octopus Cloud hosts Octopus Deploy instances in Linux containers on Azure AKS with data stored in Azure Files and Azure SQL. A couple of months ago we noticed that some of the SQL queries were much slower or even started timing out which is not something we’ve experienced before on Windows using Full .NET Framework. Some of the slowdown might be caused by AKS (K8s) but we think that the SqlClient might also be playing a role here. 119112824000676 is our Azure Support Request if that helps in any way.
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): Unknown error 258
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at Microsoft.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at Microsoft.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName, Boolean shouldReconnect)
at Microsoft.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso, String transactionName)
at Microsoft.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso)
at reprocli.Program.Scenario4(String connString, Int32 number)
at reprocli.Program.<>c__DisplayClass0_0.<Main>b__0(Int32 n)
at System.Linq.Parallel.ForAllOperator`1.ForAllEnumerator`1.MoveNext(TInput& currentElement, Int32& currentKey)
at System.Linq.Parallel.ForAllSpoolingTask`2.SpoolingWork()
at System.Linq.Parallel.SpoolingTaskBase.Work()
at System.Linq.Parallel.QueryTask.BaseWork(Object unused)
at System.Linq.Parallel.QueryTask.<>c.<.cctor>b__10_0(Object o)
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
ClientConnectionId:005d2aae-9409-4711-aaa0-b03b70f2832e
Error Number:-2,State:0,Class:11
ClientConnectionId before routing:e3300799-fdd0-40a4-84ea-b9f383596b12
Routing Destination:fed2c41af7dc.tr5.westus2-a.worker.database.windows.net,11063<---
We also captured TCP dumps while running the tests on Linux and it looks like enabling MARS causes TCP RST.
Full TCP Dumps: https://github.com/benPearce1/k8s-sql-timeout-repro/tree/tiny/source/reprocli/tcpdumps
To reproduce
Code
Repo with the sample app: https://github.com/benPearce1/k8s-sql-timeout-repro/blob/tiny/source/reprocli/Program.cs. Compiled folder contains pre-compiled versions of the app so .NET Core SDK doesn’t have to be present on the target VMs.
The first parameter is the level of parallelism. The second parameter is the connection string.
using System;
using System.Data;
using System.Diagnostics;
using System.Linq;
using Microsoft.Data.SqlClient;
namespace reprocli
{
class Program
{
static void Main(string[] args)
{
try
{
var count = int.Parse(args[0]);
var connectionString = args[1];
var total = Stopwatch.StartNew();
PrepareData(connectionString);
total.Restart();
Enumerable.Range(0,count)
.AsParallel()
.WithDegreeOfParallelism(count)
.ForAll(n => Scenario4(connectionString, n));
Console.WriteLine($"Total: {total.Elapsed}");
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
private static void Scenario4(string connString, int number)
{
var userStopWatch = Stopwatch.StartNew();
var buffer = new object[100];
for (var i = 0; i < 210; i++)
{
var queryStopWatch = Stopwatch.StartNew();
using (var connection = new SqlConnection(connString))
{
connection.Open();
using (var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
using (var command = new SqlCommand("SELECT * From TestTable", connection, transaction))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
reader.GetValues(buffer);
}
}
}
transaction.Commit();
}
}
queryStopWatch.Stop();
Console.WriteLine($"Number: {number}. Query: {i} Time: {queryStopWatch.Elapsed}");
}
userStopWatch.Stop();
Console.WriteLine($"Number: {number}. All Queries. Time: {userStopWatch.Elapsed}");
}
static void PrepareData(string connectionString)
{
var createTable = @"
DROP TABLE IF EXISTS TestTable;
CREATE TABLE TestTable
(
[Id] [nvarchar](50) NOT NULL PRIMARY KEY,
[Name] [nvarchar](20) NOT NULL
);";
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
using (var command = new SqlCommand(createTable, connection, transaction))
{
command.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
}
}
This is how we reproduced the problem which doesn’t mean you need this exact config.
The database was hosted in an Azure SQL Elastic Pool (Standard: 300 eDTUs) on a SQL Server in West US 2 region.
LINUX
Run the sample app with the following arguments on a Linux (ubuntu 18.04) VM (Standard D8s v3 (8 vcpus, 32 GiB memory) in Azure West US 2 region.
MARS ON
dotnet reprocli.dll 200 'Server=tcp:YOURSERVER.database.windows.net,1433;Initial Catalog=TestDatabase;Persist Security Info=False;User ID=YOURUSER;Password=YOURPASSWORD;MultipleActiveResultSets=True;'
The expected result is that the app finishes without throwing any errors but that’s not the case and Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. is thrown.
Reducing the level of parallelism to 20 stops the app from crashing.
Also, when MARS is ON the console doesn’t show any progress for 10+ seconds. This is not the case when MARS is OFF.
MARS OFF
dotnet reprocli.dll 200 'Server=tcp:YOURSERVER.database.windows.net,1433;Initial Catalog=TestDatabase;Persist Security Info=False;User ID=YOURUSER;Password=YOURPASSWORD;MultipleActiveResultSets=False;'
The expected result is that the app finishes without throwing any errors which is the case. The app finished in just under 25 seconds. Total: 00:00:24.9737616. The app also worked with much higher levels of parallelism (e.g. 500)
AKS
Same spec as above: Linux (ubuntu 18.04) VM (Standard D8s v3 (8 vcpus, 32 GiB memory) in Azure West US 2. We also ran this test in a container in AKS and the results were pretty much the same. The only difference was that we had to lower the level of parallelism even more. K8s networking adds a bit of overhead which might make the problem more pronounced.
WINDOWS
Run the sample app with the following arguments on a Windows (Windows Server 2016 Datacenter) VM (Standard D8s v3 (8 vcpus, 32 GiB memory) in Azure West US 2 region.
dotnet reprocli.dll 200 'Server=tcp:YOURSERVER.database.windows.net,1433;Initial Catalog=TestDatabase;Persist Security Info=False;User ID=YOURUSER;Password=YOURPASSWORD;MultipleActiveResultSets=True;'
The expected result is that the app finishes without throwing an exception which is the case. The app finished in just under 24 seconds. Total: 00:00:23.4068641. It also worked with level of parallelism set to 500. We achieved similar results with MARS disabled.
Note: We used .NET Core to run tests in Windows.
Expected behavior
The sample app should not crash and connections with MARS feature enabled should behave in the same way on both Linux and Windows.
Further technical details
Microsoft.Data.SqlClient version: 1.1.0 and 2.0.0-preview1.20021.1 .NET target: (Core 2.2 and Core 3.1) SQL Server version: (Azure SQL) Operating system: (Ubuntu 18.04 and AKS with Ubuntu 18.4)
Additional context We’ve been battling this issue for a long time now so we are happy to help in any way we can to get it resolved.
About this issue
- Original URL
- State: open
- Created 4 years ago
- Reactions: 40
- Comments: 105 (44 by maintainers)
Links to this issue
Commits related to this issue
- Tryiing https://github.com/dotnet/SqlClient/issues/422#issuecomment-645023845 — committed to OctopusDeploy/Nevermore by pawelpabich 4 years ago
Good that this github issue exists (thanks!), we seem to have run into the same issue. Problem only appears when running the (asp core + ef core 3.1.2) app on Docker with Kubernetes with MARS on. Our background service handling lots of data would simply “die”, sometimes with and sometimes without any exception thrown. As it is a BackgroundService/IHostedService, the web app continues to run, just the BackgroundService is gone.
I turned MARS off and now it works.
I got two kinds of exceptions, this one with default settings of DbContext.
When setting the command timeout to five minutes, I got this exception - same as the opener of this issue.
This issue caused lots of working days of diagnosing, as there is no clear indication what is wrong, hindering troubleshooting.
This bit us big time. Setting
MultipleActiveResultSets=truecaused lots of timeouts when running .net core app on linux pod on K8s. Removing it from connection string made the app very very fast and responsive and the “Connection Timeout Expired” errors are all gone.Interesting. Perhaps all those extra eyes can help identify a solution. What I think we need is a cross platform way to do non-blocking async io which doesn’t rely on the threadpool. On windows that’s IOCP but dropping to platform specifics in this implementation is highly undesirable. The goal of the managed SNI is to have implementation parity for all supported OS’ so behaviour and perf are identical at this layer
@Wraith2
sure, I will test it today.
I am a bit confuses by the code behavior and totally agree with you, we need to fix this issue and put reliability back to managed SNI.
The replication works on windows and forced managed mode. By works I mean that I get a connection failure request pretty quickly (should be 10 seconds) after I start the program. If you confirm that’s what’s expected then the repro is good.
Initial impression is that it’s simple threadpool starvation. If you add in:
before you start the stopwatch you should find that it runs through successfully.
So why does that work? You’re using Parallel which is attempting to start
countnumber of tasks and then have as many as the specified maxdop running at once. It does this by creating tasks and then queuing them to the threadpool. When you start those tasks they all try to connect to the database synchronously which blocks but it blocks in a particular way in the managed implementation, it ends up at https://github.com/dotnet/SqlClient/blob/bac3ab4494c2a5874e56f6d8e0b27265e04f9ca7/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNIMarsHandle.cs#L391 which sits in a loop waiting for a ManuarlResetEventSlim to be signalled telling it that a packet has arrived. The problem is that the thing that has to set that event need to run somewhere and if you’ve consumed all the threadpool threads with synchronous waits there’s no thread for it to run on, so the wait times out, and that causes the receive to fail and then the connection isn’t established and all this lack of being able to do things either manifests as a connection or query timeout eventually.It’s be nice if we weren’t going sync over async in the library so this problem couldn’t occur inside the library. Changing that is challenging and short to mid term impractical, it’s pretty much a core rewrite of the non-windows implementation with all the reliability and performance concerns that it would come with. So don’t expect that to happen soon.
So why is this your problem? Well you’re the one using all the threadpool theads. The threadpool starts with a minimum of 10 ready threads in it and at various times tasks are queued to those threads and then completed. Unless you hit a period of sustained load on the pool it’s unlikely to need to grow past the minimum value so most of the time you’ll have a small number of threads. The threadpool is balanced for general workloads and a lot of research and tuning has gone into it. One of the behaviours that results from this tuning is that it doesn’t immediately add large numbers of threads when there are more tasks waiting to be processed than there are threads available to do them. The threadpool usually only adds one thread a second to the pool. If you load it with 100 tasks quickly you’ll get 10, then 11, then 12 etc climbing slowly.
So how do you fix it? Don’t do this. Don’t try to use large number of simultaneous synchronous connections like this. Unless you’re talking to a different server or database on each connection then it’s going to be more efficient to use a pipeline approach feeding results to an open connection and having it process multiple commands with a single connection. Or, switch to using async if possible because the awaited suspension will free up threadpool threads allowing them to be used or callback signalling.
Looks like this issue has hit The Register 😃 https://www.theregister.com/2020/12/11/buggy_behavior_sqlclient/
I tested on Unnix VM got same result as yours @pawelpabich. I am not sure why Managed SNI works on Windows, but fails on Unix. I am going to investigate 2 options:
Thanks again for your patience.
As I understand it what is happening here is that SNIMarsHandle.Receive is being called and is either pulling a packet out of the
_receivedPacketQueueif one is already available or if there isn’t a packet ready it hits the wait that you highlight above. At that point we really are waiting for data but we’re doing sync over async so we can’t release the current thread until we either have a packet or have failed by timing out. Whether you block or spin at this point it’s still got to be a sync behaviour.Can you identify why the packet isn’t being received setting
_packetEventin the mars connection HandleReceiveComplete? tracing back through the jungle that is ManagedSNI I end up atSNIPacket.ReadFromStreamAsyncwhich contains modern async code:and that is going to use a threadpool thread to implement callback, no threadpool thread available no packet receipt. It might be worth looking at a custom task scheduler which handles this and give it a dedicated thread so threadpool exhaustion can’t affect it. Let me know what you think.
Also looking at that we’ve not observing the read task which we should be.
Here is an update on the issue. As it was mentioned before the issue was about using all available threads while using MangedSNI on windows or Linux. It took me a while to understand the pattern and connections in different classes.
When a packet is received there is a part of code that makes
_packeEvent,which is an implementation of ManuaResetEventSlim, to wait for connectiontime period. By default connection timeout is 15 seconds. if you run the application you will see a delay of 15-17 seconds and errors start to run down. If you increasethe ConnectTimeOut then the delay will be increased. This all go back to implementation of SNIMarsHandle inside the driver. The part that makes the delay is insidepublic override uint Receive(out SNIPacket packet, int timeoutInMilliseconds)which is get called, at some point, in the hierarchy by syncOverAsync method. it forces the threads to wait byI am trying to change the logic to use SpinWait class and SpinCount parameter inside the driver.
I will update here hopefully by end of this week.
@junchen2000 Hey, man, are you sure you’d like to share connection string containing password? (even if it is local and test)
Hi Team,
We have multiple users reporting the same issue. The issue only happens on Linux. The issue does not happen on Windows.
To narrow down the issue, we have simplified the reproducing code to the following:
Note in the above code:
We have spawned 40 threads in parallel. With only 40 threads, thread pool starvation should not be a problem. (We can only see 51 threads when ManualResetEventSlim is being signaled)
Each thread only connects to Azure SQL Database for once. In that connection, it only starts a transaction, and commits the transaction. There’re no queries.
We have modified the connection string to include several properties:
a. Connect Timeout=0 The execution timeout error can be avoided by setting connect timeout to 0. Without hitting the timeout, we can observe the slow behavior more clearly.
b. Max Pool Size=20 We specify the pool size to half number of the parallel threads so that we can see how connection pool takes effect here.
c. Packet Size=12000 We increase the packet size, but it does not show any differences.
Test result for Windows
Total execution took only 5.8 seconds. Each thread running in parallel took no more than 3 seconds. Most of the time were spent on Connection.Open:
Test Result for Linux
Run the same on Linux. Total execution took 34 seconds. The connection.Open does not really execute in parallel. Once a BeginTransaction starts in one connection, it slows down the other thread to start Connection.Open. The more connection.open, the more delay. Once the connection pool was full, it was blocked for x0 seconds until one of the BeginTransaction finished. Then the later connections queued into the connection pool could run properly without any delays:
The interesting thing is that from the managed call stack, we see the threads were waiting on SNIMarsHandle.Receive:
And we can track to where ManualResetEventSlim was signaled:
The above managed call stack gives the impression that thread was waiting to receive data from the network.
However, from TCPDump trace, we see server side had already sent back the packet (Frame 487) and client had acknowledged it (Frame 562). And then the client side waited for 35+ seconds, and then sent another packet to server (Frame 2177). And later network traffic became normal without delays.
(This TCPDump trace was captured in a different run. So the delay time was slightly different from the above output.)
It looks like the client socket layer received the data, but it did not notify the application in real time and was blocked somewhere.
How can we tell what blocked it?
@pawelpabich
I have’t been responding lately, but just to let you know I am investigating this issue. I’m able to reproduce the problem in linux docker containers when connecting to any SQL Server, irrespective of being Azure DB and it does seem to be related to linux memory management, as when running the same app on Windows using the same codepath of Managed SNI the problem does not reproduce. @Wraith2 seems to have provided some details.
However I’m investigating if it is specifically related to containerized environment limitations. I’m not sure at this point if this has anything to with transactions, I don’t think so… but I’ll share updates soon!
From the little I know of it threads are even more expensive on Linux than they are on windows, iirc windows is setup for fast thread creation and Linux for fast process creation. So as you say not cheap and it’s a poor way to scale. Setting MinThreads on the pool is a way to get around it. Not a great idea for production but it’s a public api so it is provided andf supported for you to use if you need to do so. Read around the method and see if you think it’s an acceptable thing to do in your case?
SQL server is not the bottleneck, the server is barely doing anything throughout the benchmark. The problem is the client process thread creation as far as I can see.
A1: Turning off mars on Linux removes an intermediate handle object and changes where and how receives are done. In the code I linked to you see the Receive using a packetEvent wait handle. Without mars you’d see it’s caller calling into https://github.com/dotnet/SqlClient/blob/bac3ab4494c2a5874e56f6d8e0b27265e04f9ca7/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNITcpHandle.cs#L493 which doesn’t do that kind of wait at all and instead does a direct socket call. So you don’t hit the problem because the wait is done in the socket implementation which may not require a threadpool. Really if you can avoid using mars I would advise doing so, it doesn’t seem to have many upsides and significantly increases the likelihood of issues on the managed network implementation. So yes one packet really can’t make all the difference because that one packet on the mars path immediately causes a synchronous wait that can’t be unlocked without the resource it’s blocking.
A2: At a guess if it’s coming from the sqlclient side I’d say it’s because the socket is being shutdown because of the failure. Once successfully connected sockets are kept open until they’re removed from the pool even if you close the SqlConnection object the SqlConnectionInternal object it contains stays alive for a while unless there is a failure which dooms it to immediate closure. If it’s from the server side I couldn’t say.
I am not sure there is much point as the design issue seems already acknowledged by greater minds than I, in this discussion you linked here
We have a mostly under utilised Azure Sql database (auto scaling) and ubuntu VM running a Linux docker container. We aren’t using MARS. I am curious to know if you / anyone has tried that repro you linked above for the non MARS case, as my understanding is that the same issues in linux will surface at some point with MARS disabled - as it still does sync over async and still relies on a bcl network api that requires threadpool?
I think you’ve basically said the only real solution (workaround) for now, is to prep the threadpool manually by boosting the min threads in advance of placing load… However I’m wondering how best one can anticipate the load to adjust the thread pool min thread count in advance? In our scenario we have cron jobs which cause stuff to ramp up but those jobs query external systems and depending on the data read from external systems their may be more or less work to do for the threadpool. Also if this is the official workaround is it possible to get some better guidance for how to set min / max threads to a reasonable number and whether we should decrease it again etc? Perhaps also is it worth talking with the EF Core team to ensure sql server examples or docs, show or atleast mention this issue for awareness? Otherwise its just going to catch more and more teams out.
And if I have understood correctly and the Microsoft sql client team are aware of the issue with sync over async and scalability on linux, may I ask if there are any tests that they have done to probe for points of failure and increase the understanding of the acknowledged issue - i.e what sorts of loads will cause the issue that is acknowledged in the design? This will be helpful for us to create a repro, but also teams designing new apps with load patterns that sql client has issues with, may want to use something else like postgres etc.
@cheenamalhotra thanks, will do. Enabling MARS and we still get the same issue just FYI.
@ErikEJ @Wraith2 Many thanks for your input!
Regarding opening 100 connections: We do not open any connections manually. That is all handled by EF Core 5. So I think we should be fine there and will see doing some load-testing.
MARS is disabled in our connection string. And all usages of EF are implemented with async.
That’s why we are testing the app. We didn’t just deploy it to production.
I also agree that
The fundamental building blocks of the platform need to be predictable no matter what supported OS they run on.but we must acknowledge that is fantasy… unless we mean predictable=same output… if predictable=same output in the same time framewe are going to have a big problem that will probably never bepredictable.When dealing with such specific matters (OS Thread pooling strategy such as IOCP for windows vs. ePoll for linux) I think its completely reasonable to have OS specific implementations because the underlying strategy is different! It would be lovely to have a cross-platform API to unify this feature but the hard truth is that there is not and we need to do deliver the best performance we can, so, with that in mind, I think that a nasty, cringe-worthy,
#if Linuxapproach is the way to go, until new opportunities comes along.I agree. The fundamental building blocks of the platform need to be predictable no matter what supported OS they run on.
Are there any updates on a fix for this?
We’ve been having some major problems and have started rewriting anything that touches the database to use async as @cheenamalhotra recommended, but it’s a massive refactor for us. Should we see any improvement from moving our most hit calls to be async, or will we only see a reduction in problems when we are no longer making any synchronous calls?
We’re also considering moving back to Windows because it sounds like that would resolve the issue here?
This changes multi-threaded code into single-threaded code. Unfortunately, that is not a realistic workaround.
It took longer than what we expected and it is going to take longer to solve the issue completely, but a quick workaround to use PLINQ with the M.D.S is changing the PLINQ part to the following:
Enumerable.Range(0, count) .AsParallel() .WithDegreeOfParallelism(count) .ToList().ForEach(n => Scenario4(connectionString, n));using this condition the code will run with no delay and hesitation.Hi, I have exactly the same issue as @tunger. I’m running under .net core 3.1.3 (Linux docker container as Azure Web App) with Azure MSSQL DB. I’m just using Dapper instead of EF. With MARS disabled everything works fine.
I do experience this issue on Azure Web App For Containers (on Linux), but not when running the same container locally on Docker Desktop (on Windows). Hope this helps.
Well, about the validity of the tests, MAC OSX behaves very similarly to Linux, including the thread management, after all Mac OSX is a unix based… So I would take my reproducing with a grain of salt, but I’m quite confident on the analysis…
About the transactions blocking each other, there are several workarounds, but they rely on actual code changes and business-rules validations and etc. This is a database issue, not a implementation issue. If you’re out-of-your-mind you could remove the transaction all together (and when questioned, you can say that the fever from the coronavirus made you hallucinate), but in a more realistic scenario, you can try to set the isolation level to “ReadUncommited”, this way the blocking/deadlocking will decrease immensely…