Where is the best place to handle transactions in stored procedure or in application?

Question

Hello gurus,

When my C#.net application updates records in more than one table, I use transactions so if anything should fail during the transaction I can rollback.

Which one is a better practice?

-Use stored procedure with BEGIN TRANSACTION/ROLLBACK/COMMIT TRANSACTION; -Use TransactionScope in the application as below:

    using (TransactionScope ts = new TransactionScope())
    {
    }

Answer 1

In application. Generally speaking the less logic you have in the database the better.

Answer 2

TransactionScope is a really nice way to manage transactions in code. It allows you to nest transacted code across multiple methods, and automatically scales up to distributed mode, if necessary.

I prefer to use TransactionScope over stored proc transactions, because it gives you a lot more control in the code.

Answer 3

Or you can do it in both. Check the link: http://www.4guysfromrolla.com/webtech/080305-1.shtml

Answer 4

This isn't a business logic issue, it's a data integrity issue and I feel that is ok to do in the stored procedure. I like to keep transaction logic as close to the operations as possible to shorten their duration.

Answer 5

If your transaction is going to one database, than it's better to do transaction in stored procedure.The other way can be caused only by logistic issue (DBA don't like you, or he is on vacation). If you call different transacted sources (SQL Server and Oracle) in one transaction - than there is no choice other than do transaction in code.

Answer 6

I would strongly recommend setting up one procedure for a page and managing all sql actions there. If there are multiple tasks to perform on the page that require multiple procedures, just have one procedure manage the other procedures. Your proceedure can always return multiple recordsets if needed.

• You page will perform faster - not lots of data going back and forth, just one pull. All code on the sql is already compiled with executions plans.
• You will be able to handle your errors much more efficiently - in one place as opposed to in two places - having two separate systems to decide if it's cirtical enough to fail - passing any errors back and forth to maintain your data integrity.
• You minimize your points of failure. If the transaction is going well, but the web server hiccups, the sql server is left waiting for a response.
• You will save tons of time troubleshooting and debugging.
• It will help modularize your code on the sql server. You can reuse sprocs to perform like tasks and end up with a more fleixble scalable robust system. HTH

Answer 7

Here are my 2 simple rules for when to use transactions:

• if the procedure has more than one data changing statement, it will contain a transaction.
• if the application calls more than one stored procedure that changes data, it will contain a transaction.