Will a using statement rollback a database transaction if an error occurs?

Question

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();
    }
}

Solution 1

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.

Solution 2

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

Solution 3

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

Comments

[Manitra Andriamitondra]

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 :)

[nawfal]

See also stackoverflow.com/questions/6418992/…

[Pawel Krakowiak]

It will, I even tested this once by explicitly throwing an exception.

[user3953201]

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.

[Matt Hamilton]

That is awesome, but does it work for other implementations of IDbTransaction if you're using it for cross-db compatibility?

[Sedat Kapanoglu]

@mezoid: Commit will never happen automatically. @matt: They should, by design.

[awe]

Yes, the using will call Dispose on exit, which will call Rollback, not Commit.

[nawfal]

@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! :)

[Mike]

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 ;)).

[Medinoc]

If you're using a System.Data.OracleConnection, it won't rollback on dispose. Or at least, for us it doesn't.

[Triynko]

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?