Re : "Nested Transaction Rollback"

Re : "Nested Transaction Rollback"

  
Hello, 

I have a stored procedure(SP)  that is called by outsystems.

I have transactions inside the SP because sometimes i need to run the SP via the SQL Management Studio, but, i have a problem.
When the SP has to rollback, from a outsystems call, i have several problems.

1: If the SP called via outsystems issues a rollback, i get the following error.



I am using a "Named transaction" inside the SP transaction name = UNI_REC_MULTI_OP

2: The thing is, my error handler is set to "No" when related to the abort transaction, shouldnt this bypass the previous error?



I know there are no "Pure" nested transactions, but in this case, it has to be like this. The SP must have transaction control of his own.
Is there anyway to bypass this issue?

Thank you,
Fábio


Found the solution, i will post a guide on how to bypass this issue asap.


Please do post that guide, and thanks in advance for sharing, Fábio!


Miguel
Hi Fábio,

Why exactly does the SP need a nested transaction?
"need to run the SP via the SQL Management Studio" is a bad excuse since either you can:
  • have a different SP to wrap it when it's needed to be called in SQL Management
  • or turn off Implicit Transactions in SQL Management and do them manually
  • or add some backoffice logic in your eSpace to call it for you
Anyway nested transactions shouldnt be a problem, but the SP code needs to have all possible codepaths prepared for either COMMIT or ROLLBACK the nested transaction.

Regards,
João Rosado
Hi João, 

It was specified by the costumer. Thats why its was a "pure" Sp and not a "wrapped" one.

I had ALL the commit and Rollback transactions codepaths. The thing is, when you issue a rollback it  was rolling back all the transactions, including the outsystems transaction even with a named transaction specified.

To bypass this issue is quite simple to be honest.

This is the code i was using that was causing the error: this code was working fine when @L_OK = 1 the transaction was commited with no problem, when issuing a rollback the errors started.

BEGIN TRANSACTION UNI_REC_MULTI_OP
[insert SP logic here]
IF @L_OK = 0 
     ROLLBACK TRANSACTION UNI_REC_MULTI_OP
 
IF @L_OK = 1  BEGIN
      INSERT INTO dbo.OSUSR_VKH_TRATAMENTOFICHEIROS_ERROS VALUES 
     ('Ficheiro Processado com sucesso ',@TIMESTAMP,'VC',@X_COD_PRD_DEF,@ANO_F,@LOTE_F,'N')
     COMMIT TRANSACTION UNI_REC_MULTI_OP
     GOTO REPORT;
END

--- To Fix this, i used savepoints and Rollbacks either to the savepoint , or to the transaction that was created by the SP.

-> Declare - In this declare, i detect if there is already a transaction going on, if so,  i create a savepoint in order to revert to that savepoint when a rollback is issued.
 
DECLARE @TRANCOUNTER AS INT
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
      SAVE TRANSACTION savepoint_name
ELSE
      BEGIN TRANSACTION
 
 
-> Rollback  - If there was no previous transaction, and the Stored procedure was "manually" run, the SP opens a transaction of his own and i can either revert to the save point, or the transaction that was created in the SP
 
IF @TRANCOUNTER = 0 BEGIN
     ROLLBACK TRANSACTION
END
ELSE 
     rollback transaction savepoint_name
 
 
-> Commit - Same as Rollback
 
IF  @TRANCOUNTER = 0 BEGIN
     COMMIT TRANSACTION
END
ELSE BEGIN
    COMMIT TRANSACTION savepoint_name
END

Best Regards,
Fábio
Hi,

Problem is that "COMMIT TRANSACTION" code inside advanced queries can cause the application code to crash. Since in .net commiting a transaction inside a query can have the side effect of closing the connection and cause you strange problems after the query executes.

I really think you should have 2 different procedures, one to use inside the application and other if you want to use it outside.
This is, the code that already is executing inside a transaction, should be kept using that transaction. If there were problems and needs a rollback, then you should throw an error and let who catches the exception to handle it.

Regards,
João Rosado
Hi, 
Thank you for your feedback João.
Your approach seems like a good, and above all, safer one.

Thank you once again.

Best Regards, 
Fábio Pedro
João Rosado wrote:
Problem is that "COMMIT TRANSACTION" code inside advanced queries can cause the application code to crash. Since in .net commiting a transaction inside a query can have the side effect of closing the connection and cause you strange problems after the query executes.
 
Interesting info.
Can you provide us with some more (official)  documents (trying to google it but cannot find it)
Humm, never searched for official microsoft documentation about it.
A quick search right now led me to http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.connection%28v=vs.110%29.aspx
Also alot of non-official threads about "After commit the SqlTransaction sets Connection to null".

From my personal experience from throubleshooting problems about this is that is not only the Connection property of those kind of objects that get null. It actually sets to null ALL variables referencing that connection object.

For example: (note that I use a .Commit() but doing a commit inside a query does exactly the same)

IDbConnection myConnection = someTransaction.Connection;
// here "myConnection" is not null
someTransaction.Commit();
// here "myConnection" is set to null now

Note: this also only happens if the underlying connection is to a mssql database. With oracle it keeps the connections.


Regards,
João Rosado