1

Let's say I have a stored procedure that is doing 3 inserts. To make sure everything is working fine, I add a begin and commit tran in the stored procedure.

Then from the code side (.NET, C#), the programmer is also creating a transaction.

What will be the best approach for that?

  • Having in both places?
  • Having that in the C# code only?
  • Having that in the stored procedure only?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
carlosm
  • 687
  • 2
  • 14
  • 29
  • 3
    I vote for inside the procedure. Adding a layer of application-side transaction scope very rarely works out very well. – Aaron Bertrand Sep 30 '21 at 20:08
  • 2
    I second what @AaronBertrand said. Leave the database work to the database. – Sean Lange Sep 30 '21 at 20:25
  • 3rded. If you don't need the overhead of a distributed transaction, don't use one; transactions should encompass the bare minimum required. – Stu Sep 30 '21 at 21:01

1 Answers1

1

It's better to only do it in the stored procedure for a number of reasons:

  • The procedure can keep better control of when to begin and commit the transaction.
  • It can also control the isolation level, which should usually be set before the transaction starts.
  • It keeps database code close to the database (somewhat subjective).
  • If the connection is severed and the server does not realize, a transaction opened by the client may not be committed or rolled back for some time, and could leave locks hanging, causing a huge chain of blocking
  • The client starting and committing the transaction requires two extra round-trips over the network, which in a low-latency app may be problematic. (SET NOCOUNT ON should be used for the same reason.) The transaction and associated locking is also extended for that time, casuing further blocking problems.

Do use SET XACT_ABORT ON, in case of exceptions this will cause an automatic rollback and prevent them from leaving hanging transactions.

It may still may sense to use client-side transactions, especially with distributed transactions.

Having transactions in both client code and the procedure is silly and wasteful. Choose one or the other option and stick to it.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • Also the duration of the transaction is now extended by not just the extra round-trips involved with sending begin/commit but also the time it takes for any data involved with the transaction to be transmitted back and forth. Imagine you're sending a TVP with 50,000 rows and returning and displaying data as a result. Do you really want the transaction to start before you even start sending that data over the wire? And not commit until after you consume all of the data coming back? – Aaron Bertrand Oct 01 '21 at 13:31
  • Yeah agreed, that's partly what I meant by the last point, have clarified. Although AFAIK the start of the transaction doesn't matter, because the LSN only gets set with the first modification (and TVPs are table variables, which do not particiapte in user transactions), so it's mainly the commit you need to worry about – Charlieface Oct 01 '21 at 13:35
  • It's possible I'm remembering wrong but I have been involved in cases where we were troubleshooting idle/sleeping connections that had opened a transaction and never did anything - and these were causing issues on the server. I just think it's another variable you don't need to introduce. – Aaron Bertrand Oct 01 '21 at 13:47