回滚事务请求没有对应的开始事务。

Question

我尝试将COMMIT TRAN放在if else循环中，但仍然出现错误。

我需要在班级中注册学生。如果注册后的座位数为负数，我必须撤销并打印一条消息，说明无法注册。我已经添加了其他错误消息，以了解事务如何工作。

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;

这样做正确更新了表格，但是出现以下错误：

(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)

- user3573343

---

我相当确定你的第二个查询（带有FULL OUTER JOIN的那个）没有给你提供有用的信息。 我至少无法理解它会告诉你什么或者为什么那很重要。 此外，除非你将整个事务都包装在单个序列化事务中，否则你不能信任你的结果； 可能会添加太多的学生，得到负座位数，简单地不报告（未来的）负座位数... 这对于并发系统来说实际上并不安全。 就个人而言，我可能会从一个高分辨率时间戳的表中开始INSERT，并且不使用事务。 - Clockwork-Muse

Answer 1

您遇到的错误是因为在没有开启事务的情况下进行了回滚操作（您已经提交或回滚）。请考虑整理存储过程的结构，尝试将整个存储过程作为一个事务执行，如果出现错误再进行回滚。您也可以通过检查是否存在打开的事务来测试是否需要回滚：

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

- MarzSocks

Answer 2

如果事务有名称，您需要指定要回滚的事务名称。请从那里开始。

之后，您可以告诉我们哪个事务失败了（确保在提交事务之前没有提交该事务）。

BEGIN TRAN Tr1

-- your code 

ROLLBACK TRAN Tr1

- Taochok

---

由于某种原因，即使使用命名事务，这种情况仍然会发生，并且单词“TRANSACTION”只出现了两次。一次是用于开始，另一次是用于回滚。 - Douglas Gaskell

Answer 3

在我的情况下，我正在使用 pyodbc 访问 SQL Server。即使是一个简单的 "SELECT * from table" 也会导致出现错误，如 "ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION"。

结果证明与权限有关。

- Alex Hamilton-Smith

Answer 4

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

- harmeet kukreja

Answer 5

仅从快速查看来看 - 这可能是因为您在启动事务时命名了事务（Tr1），但在错误处理程序中未引用该名称吗？

- Julian Joseph

---

也许可以尝试使用try catch来代替@@Error。 - user3573343

有没有人能帮忙一下？:O - user3573343

Answer 6

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 

- Carlos Javier Bazan Human

---

谢谢回答；你能在你的代码中增加一点描述来突出原帖有哪些不对吗？ - Mark Stewart