Microsoft Data SqlClient SqlException (0x80131904) New transaction is not allowed because there are other threads running in the session

Question: How do you resolve the error in C# Asp.Net 6 that says "Microsoft.Data.SqlClient.SqlException (0x80131904): New transaction is not allowed because there are other threads running in the session."?


Login to See the Rest of the Answer

Answer: This error might be occurring because of the specified injected Database Context in Startup.cs as a Singleton. Objects that are defined as Singleton in the Dependency Injection Container can only be instantiated once in a Life Time of an application.

This means that, when you make an update to the application, then stop the production app to deploy a new version, the new application Life Cycle starts. This is when the Dependency Container injects or loads all defined Services. During the existence of the now deployed application, if a function tries to instantiate a Repository Class (Service class) this error occurs.



If you happen to explicitly specify that the Life Time of the Database Context should be of a Singleton,  then you simply are saying that there will be only one instance of the Database Context running throughout the Life Cycle of your application until the Application Pool Recycles the.

This can create a nightmare to debug the code in case of an error in Production, defining the Database Context Life Time as Singleton means that all requests and transactions going to the Database should only use one initialized DB Context. Needless to say, it is almost the same as declaring a Static Property in the Class which is evil.

If the described scenario is indeed the cause of the error, take a look around and you might find that you are calling a function that calls the Database Context in an Async Function, this call runs the request on a background thread.

When the initial call to the database grabs the only one Singleton Initialized database that every request should use, and runs away with it in the background thread, leaving other requests stranded waiting for that background thread to return the instance of the Database Context.

In addition, this scenario halts the execution of an application and creates a bad experience for users or just throws an error.

A rule of thumb is to have every abstract/interfaced function have its Async counterpart. Define the same function in both Async and None Async (ad-hoc), meaning define two functions one that utilizes Async Await and another one that does not.

This will help you in the future in case you realized that calling a none Async Function from an Async Function is simply a bad idea, then you can change to call the none Async Interfaced function from a none Async Controller function.

I hope this helps explain why the error occurs, again this is just one scenario, there could be many that could lead to this error.