SQLTeam.com | Weblogs | Forums

Can't rollback multiple updates from a Loop inside of Transaction... why not?

sql2008

#1

Hi. This should be an easy question, but I can't find anyone else addressing this same issue in the forums. I am writing a procedure that will perform updates inside of a loop for a batch operation. If any one of those updates fail, I need all updates to roll back and throw an error. I need the transaction to be defined inside of the procedure rather than defining a transaction and then executing the procedure inside of that transaction (I can't depend on the end-user to remember to execute the procedure inside of a transaction).

Below is a simplified example of the scenario to demonstrate. The 3rd and 5th record are setup to cause an error when the update occurs inside of the loop. As I understand it, it should roll back all 5 updates... but it doesn't. Why? The last select statement shows first 2 records updated and last 3 records did not. Can anyone show me what I'm doing wrong?

--Declare a table variable
	declare @Tbl table(
		Position int,
		Value varchar(30)
						)
						
--Insert some data						
		Insert into	@Tbl
			Select 1, '23232'
				union
			Select 2, '22342'
				union
			select 3, '34422a'  
				union 
			select 4, '66345'
				union 
			select 5, '52342b'
			
--Prepare variable for Loop			
	declare @value varchar(30)
	declare @i int
	declare @numrows int			

--Perform loop operation inside of a transaction.  Rollback whole transaction if any operation inside of loop fails.
BEGIN TRY
    BEGIN TRANSACTION;
		SET @i = 1
		SET @numrows = (SELECT COUNT(*) FROM @Tbl)
		IF @numrows > 0
			WHILE (@i <= (SELECT MAX(Position) FROM @tbl))
				BEGIN

					SET @value = (SELECT value FROM @Tbl WHERE Position = @i)

					--Update the Value w/ addition operation knowing that 3rd and 5th record will fail.
						update @tbl
							set value = cast(cast(@value as int) + 100000000000 as varchar(30))
							where position = @i
					

					SET @i = @i + 1
				END


        If (@@RowCount <> 1) -- Error condition
        Begin
            Raiserror('Error Message',16,1)
        End


    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;

    -- Test XACT_STATE for 1 or -1.
    -- XACT_STATE = 0 means there is no transaction and
    -- a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. ' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT
            N'The transaction is committable. ' +
            'Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END CATCH;


-- Check the table variable to see what records were updated
	Select * from @Tbl

#2

I can't find the reference in BOL but I do recall that variables are not subject to Transactions. Or rather BEGIN TRAN - COMMIT / ROLLBACK does not have effect on the variables

Try changing your @Tbl to a temp table.


#3

Thank you... I'll try that and report back. It's been driving me nuts.


#4

Thanks so much for resolving this issue for me. I tested w/ temp table and found that it did roll back everything as it should. Good to know... I can stop pulling my hair out now.