Doing Flush() inside active Transaction

Question

I have a scenario that I commonly run into. It's simple to do with a standard ADO Transaction, but not so much with NH (that I know of).

I have 2 tables to update. The first contains profile information (Profile) and the other (Work) contains records changes that need to be made and the status of those changes. For each update to the Profile table, there will be an update to the status on the Work table.

• If the update to the Profile table fails, I need to update the status on the Work table.
• If the update to the Profile table succeeds, and the update to the Work table fails, I need to rollback the transaction.

The problem is that I don't know if the update to the Profile table failed until I commit the transaction. I tried to do a Flush on the Profile to catch the exception so I could write the status to the Work table, but then my Commit fails with the exception caused from the Profile update.

How can I handle this? In a typical ADO Transaction, my first call will throw, but I can catch and still update the other tables in the transaction.

Here's sort of what my code looks like - pretty standard. This is not my actual code, so please focus on the problem, not that I'm not disposing my transaction or closing my session ;) :

try
{
    ITransaction trans = _session.BeginTransaction();

    var work = _repo.GetWork();
    var profile = _repo.GetProfile(work.ProfileId);

    try
    {
        profile.UpdateWithNewValues(work);
        _session.SaveOrUpdate(profile);
        _session.Flush();
        work.Status = "Success";

    }catch{
      work.Status = "Failure";
    }

    _session.SaveOrUpdate(work);
    trans.Commit();

}catch{

    trans.Rollback();

}

I realize that Flush() is not going to work, but I don't know how else to do this.

Answer 1

I don't see a problem with having a trans.Commit prior to the flush. Here's an example (slightly modified too look like yours):

Profile profile;
Work work;
ITransaction tx;

try
{
    session.SaveOrUpdate(profile);  

    work.Status = "Success";    
    session.SaveOrUpdate(work);

    tx.Commit();
}
catch (Exception)   // wroh oh...
{
    try
    {
        work.Status = "Failure";
        session.SaveOrUpdate(work);

        tx.Commit();
    }
    catch (Exception)
    {
        if (!tx.WasRolledBack)
        {
            tx.Rollback();
            session.Clear();
        }

        throw;
    }
}
finally
{
    if (session.IsOpen)
    {
        // Whatever happened, Flush/Persist at the end.
        session.Flush();
    }
}

Answer 2

Some clarifications needed on your requirements.

1) >> If the update to the Profile table succeeds, and the update to the Work table fails, I need to rollback the transaction

I would have thought that Work is like an audit trail update and should not have failed if Profile update works. If this is the case, then you should not rollback your transaction. However, having said this, your code already comply to this requirement.

2) >>If the update to the Profile table fails, I need to update the status on the Work table.

If update fails, then you would have rolled back your transaction. You will not be able to update the Work table unless you have two separate transactions (one for both Profile and Work (as current) and then a separate one just for Work). Does this make sense to you?