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"];
        }
    }

Dupe to http://stackoverflow.com/questions/2113498/sqlexception-from-entity-framework-new-transaction-is-not-allowed-because-ther — Korayem, Sep 20 '12 at 12:50

Ladislav Mrnka · Accepted Answer · 2011-06-04T22:39:24.477

16

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.

edited Jun 04 '11 at 22:39

answered Jun 04 '11 at 16:15

Ladislav Mrnka

360,892
59
660
670

All my repos are initiated in the controller constructors. And I don't have any multithreaded processing, as far as I know, unless I introduced something unwittingly. And no manual connection handling except in one place which I very strict control over, called very rarely - I'm sure it's not related. So, I don't know where this leaves me. – Martin Jun 04 '11 at 21:54
Do you handle transactions manually (`TransactionScope`)? Are these scopes correctly completed / disposed? – Ladislav Mrnka Jun 04 '11 at 21:59
ok, the repos are provided the context from a static factory method, but I still don't see how it can survive between requests. Right? And it's been that way for ages. – Martin Jun 04 '11 at 21:59
TransactionScope: no, not anywhere. – Martin Jun 04 '11 at 22:00
How does you static factory looks like? (pass the code to your question). – Ladislav Mrnka Jun 04 '11 at 22:02
Your factory doesn't provide per request context - it uses single context for all requests. – Ladislav Mrnka Jun 04 '11 at 22:37
You're quite right. I never new. Non of that singleton nonsense, now it works perfectly. – Martin Jun 14 '11 at 12:20
I am using repository pattern, Single context for whole application , don't have access of context in my business layer , facing same error. now here What I do ? – user1006544 Nov 07 '13 at 13:05

score 4 · Answer 2 · edited May 23 '17 at 12:16

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

score 1 · Answer 3 · answered Jun 04 '11 at 14:48

1

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.)

answered Jun 04 '11 at 14:48

Slauma

175,098
59
401
420

Isn't the context disposed once the request is over? Using MCV. This has never happened before, that's the thing. Started happening after a year of development. Can't really trace it to anything. – Martin Jun 04 '11 at 16:04
@Martin: It doesn't dispose automatically. You have to program it somehow. Controllers in MVC have a virtual `Dispose` method you can overwrite. This method is called automatically once the request is processed. It is a good place to dispose the context (if you create the context in the controller's constructor). – Slauma Jun 04 '11 at 17:28

score 0 · Answer 4 · edited Aug 24 '14 at 18:26

Try this code. It may help. In this code, I take all of the IDs from the database and iterate over them with a new object of the database context named ctx:

var listOfCourseId = db.Courses.Where(c => c.CourseStatus == 1).Select(c => c.CourseId); 
        using (var ctx = new UniversityDbContext()) 
        {
            foreach (var acourseId in listOfCourseId)
            {
                Course selectedCors = new Course();
                selectedCors = ctx.Courses.Where(id => id.CourseId == acourseId).Single(); //hence use ctx.tableName instead of db.tableName
                selectedCors.CourseStatus = 0;
                ctx.Entry(selectedCors).State = EntityState.Modified;
                ctx.SaveChanges();
            }
        }

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

4 Answers4

Linked