If a sproc is written to be executed both independently and as a nested sproc when both the inner and outer sprocs are transactions, is it a good practice to use named trans?
Tha advantage is that inner named rollback statement do not rollback the outer tran. Maintains encapsulation that is so dear to procedural code.
One disadvantage can be a naming nightmare, because it seems that all the tran names, the nested and outer ones are, kind of, "in scope" (at least the system is aware of them especially when from the outer tran) and there is not a sophisticated naming system with namespaces etc, or the editing tool to warn against conflicts at design time.
So what's a girl to do?
Carl
Yes, I generally do write all of my procedures like this. However, SQL Server doesn't support named transactions like that. Named transactions are only used to mark to trasaction in the log.
To do what you describe, you need to use SAVE TRANSACTION <savepointName>. Annoyingly, you will use ROLLBACK TRANSACTION <savepointName> to do the partial transaction rollback.
In 2005, it is a bit messy as you start to roll in the whole TRY...CATCH, especially when triggers are involved (Use XACT_STATE()), but the overall answer is that it is best to try to be kind when you execute procedures not to change the @.@.trancount if possible.
|||How about in all sprocs always
Code Snippet
IF (@.@.TRANCOUNT > 0)
COMMIT TRAN
That is not the encapsulation I was looking for that the inner sproc should provide, but rather it is protection against violation of it.
Carl
|||I would often have a 'driving' sproc, that called one or more other sprocs. I MAY not want the inner sprors to COMMIT or ROLLBACK a TRANSACTION -but to pass a 'success'/'failure' return value back to the driving 'sproc'
However, sometimes, it would be OK if the inner sproc did a ROLLBACK on only its portion of the TRANSACTION -using a SAVEPOINT.
But in ALL cases, the driving sproc MUST be notified that the called sproc failed so that it may take appropriate action.
|||Arnie,
Still an encapsulation problem. Without the outer 'driving' sproc the inner sproc you describe are nothing more than glorified "regions" or "includes" or snippets that only facilitate readablity. Not much reusability to run them independently.
Oh well, SQL does date back to the 50's and it shows.
Since I am not really getting the reusability, I went ahead and put all the code in one sproc. It's >1000 lines.
The try-catch and error handling syntax is very chatty. Plus, I like all of my column lists vertical, so lots of lines.
BTW, we bought a terrific plugin SqlAssist that does formatting, intellisense and supports collapsible regions in SSMS much like Visual Studio.
Carl
|||Let me get a bit deeper into the situation.
sprocA starts a transaction
then calls sprocB
sprocB checks to see if it is in a transaction, and if so, sets a 'SAVEPOINT',
if not, sprocB starts a transaction.
When finished, sprocB, if having set a SAVEPOINT, and if successful, COMMITS to the SAVEPOINT and passes a 'success' return value to sprocA,
if unsuccessful, sprocB, rolls back to the SAVEPOINT and passes a 'failure' value to sprocA,
if not started with a SAVEPOINT sprocB rolls back the TRANSACTION.
sprocA rolls back or commits as is appropriate after evaluating the return values of all called sprocs.
SprocA or sprocB can be called independent of the other.
|||Arnie,
I am not sure I like the concept of savepoints especially since they are ignored in case of rollback.
How about this:
Code Snippet
CREATE PROCEDURE InnerSproc
AS
BEGIN
BEGIN TRAN
BEGIN TRY
RAISERROR ('Inner oops' , 16, 1);
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
ROLLBACK TRAN;
RETURN ERROR_NUMBER();
END CATCH
IF (@.@.TRANCOUNT > 0)
COMMIT TRAN
RETURN @.@.ERROR
END
The inner sproc is unkind to the outer one but the outer sproc protects itself.
Code Snippet
CREATE PROCEDURE OuterSproc
AS
BEGIN
BEGIN TRAN
BEGIN TRY
DECLARE @.RC INT
EXEC @.RC = InnerSproc
IF (@.RC != 0)
RAISERROR ('Inner failed and caused outer oops' , 16, 1);
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
/* protect against possible intrusion of trancount in
the 'unknown' inner sproc code */
IF (@.@.TRANCOUNT > 0)
ROLLBACK TRAN;
RETURN ERROR_NUMBER();
END CATCH
IF (@.@.TRANCOUNT > 0)
COMMIT TRAN
RETURN @.@.ERROR
END
"Fatal" and "error handling" is an oxymoron. So I am concerned with serious but non-fatal error handling.
Essentially, I am treating rolback as an error action, which it is not in SQL Server. @.@.trancount and all statements like "commit tran " and "rollback tran " that affect @.@.trancount are not part of non-fatal error handling, unless we explicitly make them so in our catch blocks. If we do so then we are applying re-throw logic but to the trancount.
When it comes to next-to-fatal errors, all languages allow you to raise them from anywahere, which is ,kind of, an exception (pun intended) to the encapsulation rule, because you are affecting all kinds of other code from inside the current code.
Well, isn't that what I am doing? Issuing a next-to-fatal error from inside, so why should I care what happens outside? Just mop up the trancount and get out. Likewise in the outer sproc.
BTW, the outer sproc can also be called from another super-outer sproc etc. So all these sprocs can be used independently or in a cascade of sprocs and the next-to-fatal error can bubble up and the trancount is maintained.
The reason I am asking this is to get a blessing to template this for all my nested sprocs. I tested it and it seems to be kosher, but what do I know.
Carl
|||
>>I am not sure I like the concept of savepoints especially since they are ignored in case of rollback.<<
This is very true. You do not have the power to control rolbacks at any grain without properly coding for it. Transactions are not nested in SQL Server, there is one transaction and one counter to tell it when it is done. ROLLBACK with anything other than a known savepoint name will reset the counter and end the tran. Savepoints do not affect the transaction counter.
>>Essentially, I am treating rolback as an error action, which it is not in SQL Server.<<
Nope, it sure isn't 
>>
Well, isn't that what I am doing? Issuing a next-to-fatal error from inside, so why should I care what happens outside? Just mop up the trancount and get out. Likewise in the outer sproc.
BTW, the outer sproc can also be called from another super-outer sproc etc. So all these sprocs can be used independently or in a cascade of sprocs and the next-to-fatal error can bubble up and the trancount is maintained.<<
You can do this with a savepoint, if you code it right (and usually if no triggers are involved, they can invalidate the transaction quite often) but the key to the problem is that transactions are not nested, so there is no rolling back part of the transaction. The savepoint lets you simulate this by marking a spot in the log to go back to.
No comments:
Post a Comment