CODEWITHSUNDEEP
sqlsql-servertransactions
Maz
Maz

Reputation: 59

SQL Server Begin and Rollback Not working

I have an issue working with rollback and transactions.

The set up is like this: I have 1 main stored procedure and 1 inner loop stored procedure. Both have their own begin and rollback transaction. Loop begin transaction can't be changed as its part of the existing application but the outer main one I have added now which is causing the problem.

I have reproduced the problem on a smaller application.

I have 2 rows of data:

enter image description here

Loop Logic:

What the loop does is goes through each record and checks if the cash is >= 5 then it will update the status on the table to 2 and commits it.

If the cash is not >= 5 then I will raise an error which jumps to begin catch and rollback the transaction. It will update the status to 3.

Problem:

If I run records which only have cash >= 5 meaning no rollbacks occur then all accounts are updated successfully to 2.

However, when I run for example the data shown above (row with cash < 5). The first record is indeed set to 2 but when the second row is processed, the first record resets back to the previous status 1 which does not make sense. 2nd record does correctly update status to 3 and rollbacks any changes but this should be a separate transaction from the first row. I have no idea why they are connected.

Also if I remove the first begin transaction from the main stored procedure then it will run fine however, I need a begin transaction for the main process so if anything fails in the main stored procedure, I can rollback everything. Any idea why the records are rolling back on loop stored procedure even though it supposed to commit it before moving to next row?

Main stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE sp_runMain
    @pBulkID INT
AS
BEGIN
    DECLARE @errorMessage AS VARCHAR(255) = ''
    DECLARE @error AS INT = 0

    BEGIN TRY
            BEGIN TRANSACTION

            exec spRun_Process @pBulkID
            if(@@TRANCOUNT > 0)
            COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
            set @error = @@ERROR 
            set @errorMessage= ERROR_MESSAGE()
            ROLLBACK TRANSACTION

        END CATCH

    END
    GO

Inner loop stored procedure:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE spRun_Process
        @pBulkID INT
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Declare @totalCount As int = 0
    Declare @rowCount As int = 1

    Declare @processID as int = 0
    Declare @name as varchar(255) = ''
    Declare @cash as int = 0


    Declare @processTable as table(
    rowid int identity(1,1),
    processID int ,
    name varchar(255) ,
    cash int ,
    status int
    )

    INSERT INTO @processTable
    SELECT Process_ID, Name , Cash , Status FROM dbo.process WHERE BulkID = @pBulkID

    SELECT @totalCount = count(*) FROM dbo.Process WHERE BulkID = @pBulkID

    WHILE @rowCount <= @totalCount
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION

            SELECT @processID = pt.processID,
                    @name = pt.name ,
                    @cash = pt.cash
            FROM @processTable AS pt
            WHERE rowid = @rowCount

            if @cash >= 5
            BEGIN
                PRINT 'WORKS!'
            END
            ELSE
            BEGIN
                RAISERROR ('cash less than 5', 16, 1)
            END

            UPDATE dbo.Process set status = 2 where Process_ID = @processID and BulkID = @pBulkID

            COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION
            UPDATE dbo.Process set status = 3 where Process_ID = @processID and BulkID = @pBulkID
        END CATCH

        SET @rowCount = @rowCount + 1
     END 
    END
    GO

Script to create data table:

    SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Process](
    [Process_ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](255) NULL,
    [Cash] [int] NULL,
    [Status] [int] NULL,
    [BulkID] [int] NULL,
 CONSTRAINT [PK_Process] PRIMARY KEY CLUSTERED 
(
    [Process_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Upvotes: 2

Views: 1375

Answers (1)

Twelfth
Twelfth

Reputation: 7180

I think you will want a table to emulate the 'rollback' process you are looking for. Create a table that stores 'proposed changes' that has all the columns that you will need to make the updates you are looking for. Insert to this table throughout the loop. At the very end of the giant procedure (and outside of the first commit), go through and make the changes to the data (add rollback/commits here if desired, though I'd write this last proc as set based queries to do all the changes at once instead of a loop...use a script to detect all records that would cause the need for a rollback and have a column in the proposed changes that records that it will cause a rollback).

As an added benefit, if you put a 'date entered' and 'date processed' column to the 'proposed changes' table, it makes a decent audit/tracking log (records with a 'date processed' entry are archived, records with that as null are awaiting processing).

Upvotes: 1

Related Questions