0

I am getting the error "System.Data.SqlClient.SqlException: New transaction is not allowed because there are other threads running in the session." when we hit 2 or more simultaneous users conducting the same DB action.

It doesn't matter whether its a query or we are making actual data changes using the entity framework, we continue to get this issue when multiple users hit the same web functions.

From looking at other posts it is my understanding we should ensure the DbContext is initialized/unique for each web request which should ensure the transaction issue is resolved.

We are using ASP.NET MVC 4 w/ UnityConfig, EntityFramework and a Multi-Tenancy architecture connecting to Azure SQL from an Azure Web App.

The DB gets initialized in Unity here: 

container.RegisterType<IStorageUnitOfWork, StorageElasticScaleUoW<int>>(new InjectionConstructor(sharding.ShardMap, tenantId, connStrBldr.ConnectionString));

and this IStorageUnitOfWork is setup like:

    public class StorageElasticScaleUoW<T> : DbContext, IStorageUnitOfWork
    {


        public StorageElasticScaleUoW()
        {
            this.Database.CommandTimeout = 0;
        }


        protected internal StorageElasticScaleUoW(string connectionString)
            : base(SetInitializerForConnection(connectionString))
        {
            this.Database.CommandTimeout = 0;
        }

I am unclear on the best approach to move this to a per web request architecture (assuming that is the be the appropriate solution).

I have seen some posts talking about using TransientLifetimeManager and PerThreadLifetimeManager on initializing the unitofWork but I am not clear how that will work with multi-tenancy. In case it helps, our multi-tenancy/sharding setup mirrors the one Microsoft suggest here: https://learn.microsoft.com/en-nz/azure/sql-database/saas-tenancy-elastic-tools-multi-tenant-row-level-security Thanks!

SaulC
  • 11
  • 1

1 Answers1

0

The tenant ID is (or should be) simply another parameter you should be passing into your DbContext constructor that you then use to define query filters (different depending on version you're using). You want each request to your application to have their own instance of that DbContext, so you'd use PerRequestLifetimeManager (see this answer).

Moho
  • 15,457
  • 1
  • 30
  • 31
  • This was useful Moho. However, it turned out that in our setting up of our DB connection we were using a static method and sharing our DbContext - we thought for now, it was less risky to move back to the standard SQL pool functions and just have our constructor call that e,g. StorageElasticScaleUoW(string connectionString) : base(connectionString) This did involve stripping some of the Multi-tenancy /Sharding code out but it is not in active use at the moment. We saw on other links that the PerRequestLifetimeManager has some downsides (e.g. harder to diagnose issues). – SaulC Dec 05 '19 at 12:13