87

I've got an IDbTransaction in a using statement but I'm unsure if it will be rolled back if an exception is thrown in a using statement. I know that a using statement will enforce the calling of Dispose()...but does anyone know if the same is true for Rollback()?

Update: Also, do I need to call Commit() explicitly as I have below or will that also be taken care of by the using statement?

My code looks sort of like this:

using Microsoft.Practices.EnterpriseLibrary.Data;

...

using(IDbConnection connection = DatabaseInstance.CreateConnection())
{
    connection.Open();

    using(IDbTransaction transaction = connection.BeginTransaction())
    {
       //Attempt to do stuff in the database
       //potentially throw an exception
       transaction.Commit();
    }
}
mezoid
  • 28,090
  • 37
  • 107
  • 148
  • 3
    Hi, just to clarify the "commit" case. It is off course mandatory because, the using(){} just call the Dispose() method. The Transaction.Dispose class could not know if it should Commit or Dispose if the Commit was also automatic :) – Manitra Andriamitondra Aug 11 '10 at 10:53
  • See also http://stackoverflow.com/questions/6418992/is-it-a-better-practice-to-explicitly-call-transaction-rollback-or-let-an-except – nawfal Feb 01 '13 at 08:08

3 Answers3

107

Dispose method for transaction class performs a rollback while Oracle's class doesn't. So from transaction's perspective it's implementation dependent.

The using statement for the connection object on the other hand would either close the connection to the database or return the connection to the pool after resetting it. In either case, the outstanding transactions should be rolled back. That's why an exception never leaves an active transaction lying around.

Also, yes, you should call Commit() explicitly.

Sedat Kapanoglu
  • 46,641
  • 25
  • 114
  • 148
  • 1
    It will, I even tested this once by explicitly throwing an exception. – Pawel Krakowiak Mar 13 '09 at 08:01
  • That's awesome! One question on my mind now though is do I need to explicitly call commit...or will the using statement handle that one too effectively making my current commit statement redundant. – mezoid Mar 13 '09 at 08:27
  • 1
    That *is* awesome, but does it work for other implementations of IDbTransaction if you're using it for cross-db compatibility? – Matt Hamilton Mar 13 '09 at 08:35
  • 4
    @mezoid: Commit will never happen automatically. @matt: They should, by design. – Sedat Kapanoglu Mar 13 '09 at 10:14
  • 2
    @MattHamilton exactly as ssg said. I checked MySQL's .net connector source, they did the same too as shown above. `Rollback` is called in `Dispose`! :) – nawfal Feb 01 '13 at 07:35
  • 1
    If you're using a `System.Data.OracleConnection`, it won't rollback on dispose. Or at least, for us it doesn't. – Medinoc Feb 21 '17 at 14:01
  • A transaction is either rolled back or committed. Why do you guys keep saying an OracleConnection won't rollback on dispose? Surely it doesn't commit on dispose. I'm pretty sure the transaction WILL rollback if dispose is called without committing, so what are you actually trying to say? – Triynko Jun 23 '17 at 19:05
19

You have to call commit. The using statement will not commit anything for you.

jhale
  • 1,790
  • 3
  • 14
  • 21
  • 8
    Yes, the using will call Dispose on exit, which will call Rollback, not Commit. – awe May 10 '11 at 11:39
6

I believe that if there's an exception such that Commit() was never called, then the transaction will automatically rollback.

Azeem
  • 11,148
  • 4
  • 27
  • 40
Tommy Hui
  • 1,306
  • 6
  • 9
  • Yeah that is my understanding. A transaction lives until a commit is called or the connection ends. At that point the transaction log is actually updated with the changes or rolled back in the case of a closed connection (you know you'll never get a commit out of a closed connection ;)). – Mike Jul 25 '14 at 18:18