ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Question

I've tried putting the COMMIT TRAN in a if else loop, and I'm still getting this error.

I have to enroll a student in a class. If the number of seats after enrollment falls in negative, I have to reverse it and print a message saying can't enroll. I have put other error messages just to see how transactions work.

CREATE PROCEDURE dbo.EnrollStudent ( @CourseID  AS INTEGER,
                                     @StudentID AS VARCHAR(20) ) AS
BEGIN
   DECLARE @StatusID INTEGER
   DECLARE @Status VARCHAR(50)
   DECLARE @CurrentSeats INTEGER
   DECLARE @ErrorCode INTEGER
   SET @StatusID=0



      IF EXISTS (SELECT 1 
                    FROM dbo.CourseEnrollment 
                    WHERE dbo.CourseEnrollment.CourseId=@CourseID AND dbo.CourseEnrollment.StudentId=@StudentID )
        BEGIN

         BEGIN TRAN Tr1
         SET @StatusID = 1
         SELECT @ErrorCode=@@ERROR
         IF (@ErrorCode<>0) GOTO OTHERPROBLEM
         ELSE 
         COMMIT TRAN Tr1

        END


     IF EXISTS ( SELECT 1
                    FROM dbo.CourseEnrollment
                    FULL OUTER JOIN dbo.Courses
                    ON dbo.Courses.CourseId=@CourseID     
                    WHERE dbo.CourseEnrollment.StudentId<>@StudentID  AND dbo.Courses.Faculty IS NULL ) 
            BEGIN
            BEGIN TRAN Tr2
                SET @StatusID=2
                SELECT @ErrorCode=@@ERROR
                 IF (@ErrorCode<>0) GOTO OTHERPROBLEM2
                 ELSE
                 COMMIT TRAN Tr2

                 END



    IF @StatusID=0
    BEGIN
        IF EXISTS ( SELECT 1
                    FROM dbo.Courses    
                    WHERE dbo.Courses.CourseId=@CourseID AND dbo.Courses.Faculty IS NOT NULL )

                BEGIN


                BEGIN TRAN Tr3

                SET @StatusID=3


                BEGIN TRAN InsertingValues
                INSERT INTO dbo.CourseEnrollment (dbo.CourseEnrollment.StudentId,dbo.CourseEnrollment.CourseId)
                                                VALUES          (@StudentID,@CourseID);

                SELECT @ErrorCode=@@ERROR
                 IF (@ErrorCode<>0) GOTO InsertProblem
                 ELSE
                 COMMIT TRAN InsertingValues




                BEGIN TRAN UpdateCourses
                UPDATE dbo.Courses  
                    SET OpenSeats = OpenSeats-1 
                       WHERE dbo.Courses.CourseId = @CourseID

                SELECT @ErrorCode=@@ERROR
                 IF (@ErrorCode<>0) GOTO UpdateProblem
                 ELSE
                 COMMIT TRAN UpdateCourses




                SELECT @CurrentSeats=OpenSeats  
                    FROM dbo.Courses
                        WHERE dbo.Courses.CourseId = @CourseID

                        IF (@CurrentSeats<0) GOTO PROBLEM
                        ELSE
                        COMMIT TRAN Tr3


                END

    END



    OTHERPROBLEM:
         BEGIN
            PRINT 'Unable to set status'
            ROLLBACK TRAN
         END


    OTHERPROBLEM2:
                 BEGIN
                     PRINT 'Unable to set status'
                     ROLLBACK TRAN
                 END


     UpdateProblem:
                 BEGIN
                     PRINT 'Not able to update values'
                     ROLLBACK TRAN InsertingValues
                 END



    InsertProblem:
                 BEGIN
                     PRINT 'Not able to insert'
                     ROLLBACK TRAN InsertingValues
                 END



    PROBLEM:
                BEGIN
                    PRINT 'Seats Full!'
                    ROLLBACK TRAN
                END




     IF @StatusID = 1
        BEGIN  
         SET @Status = 'The Student is already enrolled'
        END;

     ELSE IF @StatusID = 2
         BEGIN 
            SET @Status = 'Cannot enroll until faculty is selected' 
         END

     ELSE IF @StatusID = 3
         BEGIN 
            SET @Status = 'Student Enrolled' 
        END

   SELECT @Status

END;

This correctly updated the tables, but is giving the following errors:

(1 row(s) affected)

(1 row(s) affected)
Unable to set status
Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 101
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Unable to set status
Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 108
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Not able to update values
Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 115
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Not able to insert
Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 123
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Seats Full!
Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 131
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

(1 row(s) affected)

Majesty

I'm fairly certain your second query (the one with the FULL OUTER JOIN) isn't telling you useful information. I'm at least not able to understand what you expect it to tell you, or why that would be relevant. Also, unless you wrap the entire thing in a single serialized transaction, you can't trust your results; it's possible to add too many students, get negative seats, simply not report (future) negative seats... This isn't actually safe for a concurrent system. Personally, I'd probably start by INSERTing into a table with a high-resolution timestamp, with no transaction. – Responser

Answer 1

The error you are getting is because you are rolling back without having an open transaction (you have either already committed or rolled-back). Consider cleaning up the structure of your stored proc, try executing your entire stored proc as one transaction, and then rolling back if an error occurs. You can also test if a rollback is required by checking if a transaction is open:

BEGIN TRANSACTION;
BEGIN TRY

   --execute all your stored proc code here and then commit
   COMMIT;

END TRY
BEGIN CATCH

   --if an exception occurs execute your rollback, also test that you have had some successful transactions
   IF @@TRANCOUNT > 0 ROLLBACK;  

END CATCH

Taw

Answer 2

You need to specify the transaction name you want to rollback if it is named. begin with that.

After that you could tell us wich transaction is failing (ensure that the transaction it is not being commited before).

BEGIN TRAN Tr1

-- your code 

ROLLBACK TRAN Tr1

Mews

This still occurs with a named transaction for some reason, and the word TRANSACTION only occurs twice. Once to begin, and once to rollback. – Sean

Answer 3

DECLARE @Error varchar(max)        
SET @Error = ''        

BEGIN TRY        

 INSERT INTO OPERACION(CONTRATOID,FLUJO,MONTO,ID_ORIGINAL,ID_TRX,ESTADO,TIPO,REFERENCIA,        
 US_CREA,US_ACT,FEC_CRE,REQUEST,RESPONSE)        
 VALUES(@P_CONTRATOID,@P_FLUJO,@P_MONTO,@P_ID_ORIGINAL,@P_ID_TRX,@P_ESTADO,        
 @P_TIPO,@P_REFERENCIA,@P_US_CREA,@P_US_ACT,getdate(),@P_REQUEST,@P_RESPONSE)        

END TRY        
BEGIN CATCH        
 SELECT @Error = 'err: '+ ERROR_MESSAGE()      

 ROLLBACK ;  
END CATCH        

SELECT @Error 

Scanties

Thanks for the answer; can you add a little description to your code to highlight what the original post had wrong? – Avirulent

Answer 4

In my case I was using pyodbc to access SQL Server. Even a simple "SELECT * from table" was resulting in an error like "ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION".

Turned out to be permission related.

Lek

Answer 5

Just from a quick look - could it be because you have named your transaction when you start it (Tr1), but not referred to the name in your error handler?

Jenelljenelle

maybe try giving try catch a chance instead of @@Error – Majesty

Isn't there anyone to help on this ? :O – Majesty

Answer 6

IF @FailureCount = 0 
                    BEGIN
                         IF @@TRANCOUNT > 0  
                        BEGIN
                            COMMIT TRAN a
                        END
                    END
                ELSE
                    BEGIN
                     IF @@TRANCOUNT > 0  
                        BEGIN
                            ROLLBACK TRAN a
                        END
                    END

Simonize