New transaction is not allowed because there are other threads running in the session

Question

Getting "new transaction is not allowed because there are other threads running in the session".

It has nothing to do with foreach loops or anything people usually have problems with in conjunction with this message.

I using a EF4 with a repositoy pattern and common context open throughout the request. Something happens, can't determine exactly what, and I get this message as soon as I try to savechanges with the context, across requests, and it only dissappears once I recycle the app pool.

Am I closing the connection? How can I tell? Am I using a fresh context for every request? Yes.

What's going on? Is there a work-around?

Edit: (context factory)

    private static Dictionary<string, CoinEntities> _instances;

    public static CoinEntities DefaultInstance
    {
        get
        {
            if (HttpContext.Current == null)
            { //todo: mock instead. testing.
                if (!Instances.ContainsKey("_TEST"))
                    Instances["_TEST"] = new CoinEntities();
                return Instances["_TEST"];
            }

            if (!Instances.ContainsKey("_DEFAULT"))
                Instances["_DEFAULT"] = new CoinEntities();

            return Instances["_DEFAULT"];
        }
    }

Answer 1

I don't think that this is only problem of not disposed contexts (context doesn't keep opened transaction - you would see it because of uncommitted changes). If you have this problem you most probably don't use the new context instance per request or you have some multi threaded / asynchronous processing on the shared context instance (= one connection). This exception says that multiple threads (probably multiple processed requests) are trying to use their own transaction on the same connection - that is not possible.

Corner case can be manual handling of connections provided to context but I guess you would mention it if you use it.

Edit:

Your factory doesn't provide per request context - it provides single context for all request!!! That static dictionary is shared among all request so the first creates instance and stores it under _DEFAULT key and all other requests will use it.

Answer 2

It has to do with the new EF4 implicit Transaction.

This means that when opening an AsEnumerable() or ObjectQuery Entity request, you are in a transaction, one way to evade it is to AsArray() the query, and then you are no longer in the transaction. Another would be by shutting down the transaction maybe with optimistic locking ? but i cant find it.

this is my solution

RespondableSites = model.HyperTextLinkEntitySets.OfType<Site>()
                        .Where(x =>
                        (
                            x.moz_RESPONSEDATA != null
                            && x.RowState.IndexOf("a=") < 0))
                        ).ToArray();


foreach (var siteObj in RespondableSites)
{
    using (var context = new brandshieldDBEntities())
    {
        ParseResponseData(siteObj);
        model.SaveChanges();
    }
}

and it works, it's slow but it works..

check here: SqlException from Entity Framework - New transaction is not allowed because there are other threads running in the session

Answer 3

You don't dispose the context when your request is finished. You can dispose the context either by applying a using block ...

using (var context = new MyContext())
{
    // Do Db stuff and SaveChanges, etc.
}
// context gets disposed automatically here

... or explicitly:

context.Dispose();

(That's my theory based on your input so far.)