ADO.NET: Do you need to Rollback a transaction?

Question

Consider the following code which does not rollback the transaction if an exception is caught.

transaction = connection.BeginTransaction();
command.Transaction = transaction;
try {
    // interact with database here
catch {}
finally {
    connection.Close();
}

What are the consequences of this and is it necessary to rollback the transaction?

Answer 1

The best is to generate your transaction inside a using block like this:

using( /*code to create the transaction you want )
{
  //perform your transaction here
  transaction.Commit();
}

If you code fails before the call to commit, it will automatically be rolled back as the using block is exited.

Answer 2

It will leave an open transaction on the database, which could potential block other queries.

Taken from here:

Consider the following general guidelines when you use transactions so that you can avoid causing deadlocks:

• Always access tables in the same order across transactions in your application. The likelihood of a deadlock increases when you access tables in a different order each time you access them.

• Keep transactions as short as possible. Do not make blocking or long-running calls from a transaction. Keep the duration of the transactions short. One approach is to run transactions close to the data source. For example, run a transaction from a stored procedure instead of running the transaction from a different computer.

• Choose a level of isolation that balances concurrency and data integrity. The highest isolation level, serializable, reduces concurrency and provides the highest level of data integrity. The lowest isolation level, read uncommitted, gives the opposite result.