Commit a transaction if part of it fails

Question

I have a method running in COM+ which looks a little like the following:

[AutoComplete(true)]
public bool DoSomething(string args)
{
    DoSomeDBWork(args);
    try {
        DBAccess.RunQuery("INSERT fail");
        return 0;
    }
    catch (Exception ex)
    {
        //Hide the error because it doesnt matter - log it out though for completeness
        DBAccess.RunQuery("INSERT INTO Log VALUES ('{0}')", ex.ToString());
        return -1
    }
}

OK So the method runs, the DoSomeDBWork() method runs, does some updates on the DB.

The 'insert fail' runs, which fails. I just want to ignore it, but log out that it has failed.

But I'm getting an error generated: System.Transactions.TransactionException: The operation is not valid for the state of the transaction.

I'm assuming that even though I catch the Exception, because it is a DB error it is rolling back the transaction automatically.
The error comes from the line 'insert into log ...'
and everything that was updated in DoSomeDBWork() is rolled back as well.

How do I ignore the failing line?

score 1 · Answer 1 · answered Mar 11 '13 at 12:18

1

You will have to rollback the transaction being used for "Insert Fail..." before trying to Insert into the log. Not familiar with DBAccess and how it works, but once you rollback you should be able to start a new transaction either implicitly or explicitly.

[AutoComplete(true)]
public bool DoSomething(string args)
{
    DoSomeDBWork(args);
    try {
        DBAccess.RunQuery("INSERT fail");
        return 0;
    }
    catch (Exception ex)
    {
        DBAccess.Rollback();
        DBAccess.RunQuery("INSERT INTO Log VALUES ('{0}')", ex.ToString());
        return -1
    }
}

answered Mar 11 '13 at 12:18

sanbornc

766
5
12

But if I do a rollback in my catch - it will also rollback everything done earlier in 'DoSomeWork' won't it? No way I can execute everything except for the line that fails? – jb. Mar 11 '13 at 13:15
1

I did not see that requirement, sorry. At this point you would have to introduce a nested transaction. Again, not sure how **DBAccess** is written, but my guess is that it would require modification to support the nested transaction and ensure "INSERT Fail" runs in the scope of that transaction. – sanbornc Mar 11 '13 at 14:37

Commit a transaction if part of it fails

1 Answers1