Rationale of using database-level transactions inside a store procedure when application layer already manages a transaction

One of the legacy applications my team has to maintain has almost always this pattern for dealing with data modification:

try
{
   // get the connection
   // begin transaction 
   // optional execution of some changes
   // optional call stored procedure
   // optional execution of other changes
   // commit transaction
}
catch (Exception exc)
{
   // logging
   // rollback transaction
}
finally
{
   // dispose connection
}

This pattern makes sense since for some "commands" the application layer knows the whole transaction scope (i.e. what to be changed atomically).

Some stored procedures manage a transaction of their own, thus getting a nested transaction for a while.

According to this rather old article nested transactions do not seem to be particularly useful.

I am wondering about benefits of using extra (nested) transactions if the application layer seems to do a good job of managing the transaction.

posted about 2 years ago

CC BY-SA 4.0

2y ago by Canina‭

Alexei‭

4928 reputation 111 101 679 474