What is the benefit to wrapping every sql/stored proc invocation in a transaction?

Question

The following code executes one stored procedure. The stored procedure has only one command in it. Is there any benefit to wrapping everything in a transaction, even it only has one SQL statement in it (or one stored proc that has only one sql statement)?

In the sample code below, if the delete fails, it fails. There is nothing else to be rolled back (it seems). So why is everything wrapped in a transaction anyhow?

using (ITransactionManager transMan = repository.TransactionManager())
using (IController controller = repository.Controller())
{
    transMan.BeginTransaction();
    try
    {
        //DELETE FROM myTable where Id=@id
        controller.Delete(id);
        transMan.CommitTransaction();
    }
    catch
    {
        transMan.RollbackTransaction();
        throw;
    }
}

Answer 1

You don't really lose much by wrapping it in a transaction (as a lightweight transaction would be used), and if your add more logic to your business layer then you already have working transaction management.

This code will work well with nested transactions as well, whereas if you did not use a transaction at this level, you could lose some of the benefits depending on how the code and stored procedure were structured.

Another possibility is that the stored procedure may change and have multiple statements - again, you would still have working transaction management in place.

I would keep it.

Answer 2

From looking at the code, there is no apparent reason for the transaction. It doesn't hurt anything, but it can cause confusion, making one wonder why it's there.

However, if your commented-out code represents a stored procedure, the procedure might take multiple actions, and if the failure happens after a certain point, there could be actions to roll back. If so, there could be a very good reason for the transaction.

Answer 3

Even if you don't explicitly start a transaction, all database queries run in an implicit transaction. Have a look at this advise from the NHProf homepage. It very well describes why you nearly always should use an explicit transaction.