June 3, 2005 at 9:28 am
I'm trying to understand the logic of how a rollback might occur in nested stored procedures.
i thought when you use SET XACT_ABORT ON, any error raised would cause the rollback and termination of the procedure, but that's not what i'm seeing; can someone explain this one to me? in this case, i'm raising an error manually, where i might be checking for a business rule or something, or where a foreign key violation or something unthought of might be raised.
here's an example:
CREATE TABLE TEST(TESTID INT)
GO
CREATE PROCEDURE PR_SOMETHING1
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
PRINT 'EXECUTING PR_SOMETHING1'
INSERT INTO TEST (TESTID) VALUES(1)
COMMIT TRAN
PRINT 'COMMITING PR_SOMETHING1'
END --PROC
GO
CREATE PROCEDURE PR_SOMETHING2
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
PRINT 'EXECUTING PR_SOMETHING2'
INSERT INTO TEST (TESTID) VALUES(2)
RAISERROR ('SOMETHING WENT WRONG IN PR_SOMETHING2',17, 1)
COMMIT TRAN
PRINT 'COMMITING PR_SOMETHING2'
END --PROC
GO
CREATE PROCEDURE PR_SOMETHING3
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
PRINT 'EXECUTING PR_SOMETHING3'
INSERT INTO TEST (TESTID) VALUES(3)
COMMIT TRAN
PRINT 'COMMITING PR_SOMETHING3'
END --PROC
GO
CREATE PROCEDURE PR_SOMETHING4
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
PRINT 'EXECUTING PR_SOMETHING4'
INSERT INTO TEST (TESTID) VALUES(4)
COMMIT TRAN
PRINT 'COMMITING PR_SOMETHING4'
END --PROC
go
create PROCEDURE PR_MASTERPROC
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
EXEC PR_SOMETHING1
EXEC PR_SOMETHING2
EXEC PR_SOMETHING3
EXEC PR_SOMETHING4
COMMIT TRAN
PRINT 'COMMITING PR_MASTERPROC'
END --PROC
GO
EXEC PR_MASTERPROC
SELECT * FROM TEST
the output of the results is as follows:
Server: Msg 50000, Level 17, State 1, Procedure PR_SOMETHING2, Line 8
SOMETHING WENT WRONG IN PR_SOMETHING2
EXECUTING PR_SOMETHING1
COMMITING PR_SOMETHING1
EXECUTING PR_SOMETHING2
COMMITING PR_SOMETHING2
EXECUTING PR_SOMETHING3
COMMITING PR_SOMETHING3
EXECUTING PR_SOMETHING4
COMMITING PR_SOMETHING4
COMMITING PR_MASTERPROC
TESTID
-----------
1
2
3
4
I had expected only 1 and 2 to get performed, and that the calling procedure would stop executing, but it appears that the error is raised, but everything still is run and commited.
maybe the print commands, which i'm using instead of actual code, are exempt from the rollback logic?
if i want the master proc to rollback if any sub proc fails, what am i doing wrong in this example?
The BOL says that Compile errors, such as syntax errors, are not affected by SET XACT_ABORT, so think using the raiseerror function can't be used to automatically rollback like xact_abort does with a FK violation, for example.
Lowell
June 3, 2005 at 11:35 am
Not exactly an answer, but there is a series of articles that talks about the ins and outs of transactions and xact_abort.
http://www.sqlservercentral.com/columnists/dpeterson/allabouttransactionspart1.asp
http://www.sqlservercentral.com/columnists/dpeterson/allabouttransactionspart2.asp
http://www.sqlservercentral.com/columnists/dpeterson/allabouttransactionspart3.asp
June 3, 2005 at 12:14 pm
This also may help. It has a section on XACT_ABORT.
http://www.sommarskog.se/error-handling-II.html#presumptions
Here's part of the article:
"...Even if XACT_ABORT is ON, as a minimum you must check for errors when calling stored procedures, and when you invoke dynamic SQL. This is because XACT_ABORT does not affect compilation errors, and compilation errors are typically those that cause SQL Server to abandon execution of a procedure and return control to the caller. Nor will the batch be aborted because of a RAISERROR, so if you detect an error condition, you still need to return a non-zero value to the caller, that has to check for it. ..."
June 3, 2005 at 12:40 pm
Your main proc needs to test return values after execution of each procedure call and determine a course of action based on success or failure.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply