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?

Fossorial

Answer 1

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.

Planospore

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

@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 msdn.microsoft.com/en-us/library/ms189336.aspx for reference. – Briquette

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

Answer 2

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.

Briquette

To whoever decided to score a -1, it would be polite to at provide your reasoning. – Briquette

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.

Sclerodermatous

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. – Wolverhampton

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.

Wolverhampton

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

Depends on your database, some databases don't have "nested transactions", – Wolverhampton

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.

Antipater

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

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

Ingathering