How do I fix "multiple threads" and "new transaction is not allowed" errors when using Telerik Open Access?

Question

We have a asp.net MVC application that uses Telerik Open Access. We're having all kinds of problems just saving data once we put it into production. Telerik no longer supports Open Access, so we can't get any help from them, but we're supposed to be going live right now and don't have the budgeted hours to change now. Can someone give me some suggestions on how to get around these problems?

We're getting the same errors when updating and inserting records, but these problems don't always occur. I never get these errors running the solution from Visual Studio on my computer or once the project is deployed to our testing server. On the production server, when several users are using the application, we start to see errors.

Example code would be this insert function:

public void CreateAttachments(tblCoDoc obj)
{
    try
    {
        dat.Add(obj);
        dat.SaveChanges();
    }
        catch (Exception exception)
        {
            throw exception;
        }
}

and this update function:

public void UpdateWorkOrderApprGen(tblWorkOrder obj)
    {
        var context = new KoorsenOpenAccessContext();
        var upd =
        (
            from workOrder in dat.tblWorkOrders
            where workOrder.WorkOrderId == obj.WorkOrderId
            select workOrder
        ).FirstOrDefault();

        if (upd != null)
        {
            upd.ReferenceNumber = obj.ReferenceNumber;
            upd.CustomerContract = obj.CustomerContract;
            upd.VendorContract = obj.VendorContract;
            upd.DateResolved = obj.DateResolved;

            try
            {
                context.SaveChanges();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

In both cases, those methods are in a class called Repository. There is a private variable defined for this class (KOpenAccessContext is the class defined in the project implementing a OpenAccessContext class):

private static KOpenAccessContext dat = null;

and then in the Repository constructor, that private variable is assigned to a new KOpenAccessContext:

dat = new KoorsenOpenAccessContext();

The error messages we're getting are

Telerik.OpenAccess.Exceptions.DataStoreException: Telerik.OpenAccess.RT.sql.SQLException: New transaction is not allowed because there are other threads running in the session.

and

[InvalidOperationException: Unable to start second transaction]

The first one is the most common.

This post: SqlException from Entity Framework - New transaction is not allowed because there are other threads running in the session. suggests the problem is from the save being in a for loop, which is not the case.

The 3rd answer down suggests putting the code in a using transaction and using context blocks; I get this error:

Telerik.OpenAccess.OpenAccessException: System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.

I found this post: http://www.telerik.com/forums/how-do-i-fix-a-new-transaction-is-not-allowed-error-is-telerik-open-access#swcnW_tPGEWglUih1TEAKg suggesting that I create "use short living context instances". To me that meant to create a new Open Access. I tried this and still got the "new transaction is not allowed" error:

public void CreateAttachments(tblCoDoc obj)
{

    try
    {
        var db = new KoorsenOpenAccessContext();
        db.Add(obj);
        db.SaveChanges();
    }
        catch (Exception exception)
        {
            throw exception;
        }
}

I'm really at a loss and I've got a client and boss looking to me for a solution. I'd love to know the cause of this (thinking it may be because there are multiple users), but what I really need is a solution. How can I get around this problem?

Answer 1

I think you have a couple problems going on. You mention you are using a repository pattern with a private static Context that is initialized with this code:

dat = new KoorsenOpenAccessContext();

If your create and update functions are inside the repository then they should use this instance (dat) of the context and not go about creating their own function private instances:

public void UpdateWorkOrderApprGen(tblWorkOrder obj)
    {
        // Don't do this, use the repository instance of the context instead
        // var context = new KoorsenOpenAccessContext();
        var upd =
        (
            from workOrder in dat.tblWorkOrders
            where workOrder.WorkOrderId == obj.WorkOrderId
            select workOrder
        ).FirstOrDefault();

        if (upd != null)
        {
            upd.ReferenceNumber = obj.ReferenceNumber;
            upd.CustomerContract = obj.CustomerContract;
            upd.VendorContract = obj.VendorContract;
            upd.DateResolved = obj.DateResolved;

            try
            {
                dat.SaveChanges();  // Use the repository's context here
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

Make sure your repository implements IDisposable and you dispose of the repository's context properly by calling dat.Dispose()

Your other option is to skip over the repository pattern and remove the repository private context instance (dat). Use the function private instance of the context instead. Telerik OpenAccess best practices suggest a using statement to ensure the context is closed and disposed of properly:

public void CreateAttachments(tblCoDoc obj)
{    
    try
    {
        using (var db = new KoorsenOpenAccessContext())
        {
            db.Add(obj);
            db.SaveChanges();
        }
    }
    catch (Exception exception)
    {
        throw exception;
    }
}