Welcome to Software Development on Codidact!

Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.

Review Suggested Edit

You can't approve or reject suggested edits because you haven't yet earned the Edit Posts ability.

~~Rationale of using nested transactions inside a store procedure when application layer already manages the transaction~~

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:

```c#
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](https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/) 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.

sql-server transactions nested-transactions

Communities

Review Suggested Edit