If you’re receiving error Msg 3902, Level 16, which reads “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION”, it’s probably because you’ve got a stray COMMIT statement.

You could be getting this due to implementing error handling, and forgetting that you’ve already committed or rolled back the transaction elsewhere in your code.

Example of Error

Here’s a simple example to demonstrate the error:

SELECT ProductName, ProductPrice FROM Products;
COMMIT TRANSACTION;

Result:

(7 rows affected)
Msg 3902, Level 16, State 1, Line 2
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

This will occur if your SET IMPLICIT_TRANSACTIONS is OFF. See below for what happens when SET IMPLICIT_TRANSACTIONS is ON.

Example of Error due to Error Handling

You could be getting this due to implementing error handling, and forgetting that you’ve already committed or rolled back the transaction elsewhere in your code.

For example:

BEGIN TRANSACTION
    BEGIN TRY 

        INSERT INTO Orders ( OrderId, OrderDate, CustomerId )
        VALUES ( 5006, SYSDATETIME(), 1006 );
        
        INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
        VALUES ( 5006, 1, 1, 20, 25.99 );
        
        INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
        VALUES ( 5006, 2, 7, 120, 9.99 );

        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
    END CATCH
COMMIT TRANSACTION;

Result:

(1 row affected)
(1 row affected)
(1 row affected)
Msg 3902, Level 16, State 1, Line 20
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

In this case, I already had COMMIT TRANSACTION in the TRY block. So by the time the second COMMIT TRANSACTION was encountered, the transaction had already been committed.

We would see the same even if the transaction had encountered an error, and was rolled back. A rollback will end the transaction, and therefore, no further COMMIT statements are required.

So to fix this issue, we’d simply remove the last COMMIT TRANSACTION, and the transaction code would look like this:

BEGIN TRANSACTION
    BEGIN TRY 

        INSERT INTO Orders ( OrderId, OrderDate, CustomerId )
        VALUES ( 5006, SYSDATETIME(), 1006 );
        
        INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
        VALUES ( 5006, 1, 1, 20, 25.99 );
        
        INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
        VALUES ( 5006, 2, 7, 120, 9.99 );

        COMMIT TRANSACTION;
        
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
    END CATCH

Implicit Transactions

If you have implicit transactions enabled, you might get different results to the first example.

If we set IMPLICIT_TRANSACTIONS to ON, here’s what we get:

SET IMPLICIT_TRANSACTIONS ON;
SELECT ProductName, ProductPrice FROM Products;
COMMIT TRANSACTION;

Result:

+---------------------------------+----------------+
| ProductName                     | ProductPrice   |
|---------------------------------+----------------|
| Left handed screwdriver         | 25.99          |
| Long Weight (blue)              | 14.75          |
| Long Weight (green)             | 11.99          |
| Sledge Hammer                   | 33.49          |
| Chainsaw                        | 245.00         |
| Straw Dog Box                   | 55.99          |
| Bottomless Coffee Mugs (4 Pack) | 9.99           |
+---------------------------------+----------------+
(7 rows affected)

No error occurs.

This is because, certain T-SQL statements automatically start a transaction when they run. It’s as if they were preceded by an invisible BEGIN TRANSACTION statement.

When IMPLICIT_TRANSACTIONS is OFF, these statements are automatically committed. It’s as if they’re succeeded by an invisible COMMIT TRANSACTION statement. In this scenario, the transaction is in autocommit mode.

When IMPLICIT_TRANSACTIONS is ON, there is no invisible COMMIT TRANSACTION statement. These statements are still started by an invisible BEGIN TRANSACTION, but they need to be ended explicitly.

An implicit transaction remains in progress until it is either explicitly committed or explicitly rolled back.

Therefore, in this example, our stray COMMIT TRANSACTION statement was actually needed to end the implicit transaction.