Should transactions be specified outside a stored procedure or inside?

We can wrap a call to a stored procedure in a transaction and specify an isolation level.

Or we can put the transaction inside the stored procedure specify an isolation level there.

Which is it better to do?

From stackoverflow

• It depends on the business logic, if the SP is atomic it should implement its own transaction. If you don't do that you run the risk of errant code in the future not creating the wrapping transaction. so in answer to your question I think the transaction should go inside the SP.

  Of course there's nothing to stop you doing both, atomic SPs implement their own transactions, and outside of that scope other broader transactions may already exist.

  In general when creating using transactions within SPs you may already be within a transaction scope, you have to code for this instance when doing a Commit/Rollback.

  Jakob Christensen : Won't the transaction inside the SP just join the outer transaction scope so that commit/rollback will work transparently?

• You should adopt a consistent approach. Be aware that rolling back a transaction within a stored procedure will roll back any nesting transaction scope, including any outside scope.

  I would advise you to keep your transactions outside the procedures. That way, you retain full control.

  Dems : this is not correct. If you nest multiple BEGIN TRANSACTION startments and the deepest one rolls back, they all roll back...

  John Sansom : @Dems: You are partly correct in that, this is only the case if you do not use a transaction with a transaction_name parameter. This is why transactions should be explicitly defined for complete control. See http://msdn.microsoft.com/en-us/library/ms189336.aspx for reference.

  Tor Haugen : Dems: for some reason I had written 'accept' instead of 'roll back', which made little sense. Thanks for pointing it out:-)

• Inside the stored procedure is the most appropriate location in my opinion.

  One of the fundamental rules of good transaction design is to keep the life of the transaction as short as possible and so the commit should occur immediately after the transaction logic has been completed. Controlling a transaction outside of the stored procedure will result in unnecessarily extending the life of the transaction.

  You should also consider that defining the transaction within the procedure will also provide more clarity to your code. Otherwise, should another coder need to modify a given stored procedure, they would have to rely on the fact that the caller does indeed wrap the procedure in a transaction. Including the transaction within the procedure explicitly defines your transaction handling.

  John Sansom : To whoever decided to score a -1, it would be polite to at provide your reasoning.

• Just as an FYI, Oracle doesn't supported nested transactions, and if you begin a transaction at an outer level and then call a series of stored procedures, any stored-proc that issues a commit will commit the entire transaction so far, not just the transaction it instigated. Therefore you have to manage the transaction outside the stored-proc when calling from languages like C#

  Just thought you might be interested, for comparison.

  Matthew Watson : It does support autonomous_transactions though, which allows you to essentially start a new transaction outside of your current one. They are almost always a bad idea though. It also supports save points. which allows partial rollbacks, although its not something i've used.

• Outside, or at least, in the outer layer of your database API.

  If you commit inside every stored procedure, then you might as well have autocommit turned on, image the following stored procedures

  create_user_with_email_address
    calls -> create_user
    calls -> create_email_address
  

  if you commit within either create_user/create_email_address then create_user_with_email_address can no longer be transactional, if create_email_address fails, create_user has already been committed, and you have broken data.

  Put the transaction as high up as needed to keep everything within it.

  tpower : I don't think this is true, if the outer transaction rolls back it also rolls back any nested transactions.

  Matthew Watson : Depends on your database, some databases don't have "nested transactions",

  tpower : I was only thinking of SQL server, but good point +1

• We do the following, within the Sproc, because if we just rollback it mucks up the transaction count in the outer SProcs, which can generate a warning back to the application - and if it isn't expecting / handling it can cause an application error.

  However, this method only rolls back the "local" transaction, so outer "callers" must interpret the Return Value appropriately; alternatively use a RAISERROR or similar.

  BEGIN TRANSACTION MySprocName_01
  SAVE  TRANSACTION MySprocName_02
  ...
  IF @ErrorFlag = 0
  BEGIN
      COMMIT TRANSACTION MySprocName_01
  END
  ELSE
  BEGIN
      ROLLBACK TRANSACTION MySprocName_02
      COMMIT TRANSACTION MySprocName_01
  END