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:
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
