Should transactions be specified outside a stored procedure or inside?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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