C# - TransactionScope - TransactionAbortedException - The transaction has aborted

Question

I've code like that:

class Importer 
{
    private DatabaseContext m_context;

    public: Importer() 
    {
        m_context = new DatabaseContext();
        m_context.CommandTimeout = 5400; //This is seconds
    }

    public bool Import (ref String p_outErrorMsg) 
    {
        List<SomeData> dataToImport = new List<SomeData>();
        getSomeData(ref dataTiImport);

        bool result = false;

        try 
        {
            using(TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(2, 0, 0))) 
            { //Two hours timeout
                result = importDatas(dataToImport);
                if (result == true) 
                {
                    scope.Complete();
                }
            }
        } 
        catch (TransactionAbortedException ex)
        {
            p_outErrorMsg = String.Format("TransactionAbortedException Message: {0}", ex.Message);
        } 
        catch (ApplicationException ex) 
        {
            p_outErrorMsg = String.Format("ApplicationException Message: {0}", ex.Message);
        }
    }

    bool importDatas(List<SomeData> p_DataToImport) 
    {
        foreach (SomeData data in p_DataToImport) 
        { //There can be somehitg about 3000 iterations
            if (!importSimpleData(data)) 
            {
                return false;
            }
            return true;
        }
    }

    bool importSimpleData(SomeData p_Data) 
    {
        //creation some object o1
        try 
        {
            m_context.objetc1s.InsertOnSubmit(o1);
            m_context.SubmitChanges();
        }
        catch (Exception e) 
        {
            //Error handlig
            return false
        }

        //creation some object o2
        o2.id_o1 = o1.id_o1;
        try 
        {
            m_context.objetc2s.InsertOnSubmit(o2);
            m_context.SubmitChanges();
        } 
        catch (Exception e) 
        {
            //Error handlig
            return false
        }

        //creation some object o3
        o3.id_o2 = o2.id_o2;
        try 
        {
            m_context.objetc3s.InsertOnSubmit(o3);
            m_context.SubmitChanges();
        } 
        catch (Exception e) 
        {
            //Error handlig
            return false
        }

        //creation some object o4
        o4.id_o1 = o1.id_o1;
        try 
        {
            m_context.objetc4s.InsertOnSubmit(o4);
            m_context.SubmitChanges();
        } 
        catch (Exception e) 
        {
            //Error handlig
            return false
        }

        return true;
    }
}

And if List has 500 records, all is writing fine. But when the list is near to 1000, I've always exception: TransactionAbortedException.Message = "the transaction has aborted".

Firstly I think that timeout was to small so I did introduce to code this two lines: ... m_context.CommandTimeout = 5400; //This is seconds (1.5 hour) ... using(TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(2, 0, 0))) { //Two hours timeout ...

As you can see in presented above code.

The same exception still occurs, did I miss something? What do I do wrongly? I have to add that data base is remote (not local)

Thanks in advance for the help!

Answer 1

I'd have to dig up the documentation again, but setting a transaction timeout to 2 hours may not be happening for you. There is a cap on how long the transaction timeout can be that comes down through machine.config and if you specify more than that cap, it quietly ignores you.

I ran into this a long time ago, and found a reflection-based way to tweak that setting here by Matt Honeycutt to make sure you're really getting the timeout you specify.

Answer 2

It seems that importSimpleData fails on some row and importData returns false. In such case you don't call scope.Complete() and it's the reason why transaction rolls back.