Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is Explicit Transaction Rollback Necessary?

Many examples out there advocate explicit rollback of database transactions, along the lines of:

using (var transaction = ...) {     try     {         // do some reading and/or writing here          transaction.Commit();     }     catch (SqlException ex)     {         // explicit rollback         transaction.Rollback();     } } 

However, I tend to do this:

using (var transaction = ...) {     // do some reading and/or writing here      transaction.Commit(); } 

When an exception occurs, I'm just relying on the implicit rolling back of transactions that aren't committed.

Is there any problem relying on this implicit behavior? Does anyone have a convincing reason why I shouldn't be doing it this way?

like image 703
Kent Boogaart Avatar asked Feb 17 '10 13:02

Kent Boogaart


People also ask

What transactions should be ROLLBACK?

Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction. You can use ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.

When would you implement a ROLLBACK transaction?

4. ROLLBACK: If any error occurs with any of the SQL grouped statements, all changes need to be aborted. The process of reversing changes is called rollback. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

Does a transaction automatically ROLLBACK?

Queries can be automatically or manually rolled back via transactions. Automatic rollback happens when a query fails to execute for any reason. Manual rollback occurs depending on user-defined conditions. The rollback SQL statement is used to manually rollback SQL queries in SQL Server.

Why is ROLLBACK needed?

The purpose of rollback is to "roll back" any and all data modifications have been done between BEGIN TRANSACTION and ROLLBACK in case if any unit of work fails to execute due to any errors.


2 Answers

No, its not specifically needed, however I can think of 2 reasons why it might be a good idea:

  • Clarity

Some might argue that using transaction.Rollback() makes it clearer under what circumstances the transaction will not be committed.

  • Releasing locks

When dealing with transactions it is important to realise the certain locks will only be released when the transaction is rolled back or committed. If you are using the using statement then the transaction will be rolled back when the transaction is disposed of, however if for some reason you need to do some error handling inside the using block, it may be advantageous to rollback the transaction (removing the locks) before performing complex / time consuming error handling.

like image 141
Justin Avatar answered Sep 28 '22 05:09

Justin


Most properly written ADO.NET connection will rollback transactions not explicitly committed. So it's not strictly necessary.

The main benefit I see of an explicit Rollback() call it the ability to set a breakpoint there and then inspect either the connection or the database to see what was going on. It's also clearer to future maintainers of the code what happens under different execution paths.

like image 22
LBushkin Avatar answered Sep 28 '22 05:09

LBushkin