Запрос commit transaction не имеет соответствующей инструкции begin transaction

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.

Вероятно вы на версии платформы 8.2.13 обновили конфигурацию БП на версию 2.0.42.5, после чего при обновлении платформы на версию 8.2.16.368 или выше при запуске базы после конвертации происходит ошибка SDBL.
Способ обхода сначала обновить платформу, сконвертировать ИБ, и только после этого обновляться на 2.0.42

Если обновление конфигурации на 2.0.42.5 выполнялось на 8.2.13, то режим совместимости оказался с 8.2.16, а изменения структуры таблиц БД, которую сделала бы 8.2.16 при смене режима совместимости, не произошло, т.к. 13-й релиз этого не умеет. Таким образом, если далее запускается платформа 16-го релиза, то она считает, что изменение структуры таблиц уже выполнено, хотя этого не произошло. Это и приводит к описанному эффекту. Как обойти: сначала обновить платформу, сконвертировать ИБ, и только после этого обновляться на 2.0.42; либо 1. Открыть 13-м релизом Конфигуратора 2. Сохранить конфигурацию в файл 3. понизить режим совместимости до 8.1, реструктуризовать 4. установить режим совместимости «Не используется», реструктуризовать 5. Закрыть Конфигуратор 13-го релиза, открыть Конфигуратор 16-го. 6. Выполнить загрузку конфигурации из файла, реструктуризоваться.

FactoryBot.create(:calendar)

  AppointmentStatus Load (0.6ms)  EXEC sp_executesql N'SELECT TOP 1  [appointment_status].* FROM [appointment_status] WHERE [appointment_status].[name] = @0  ORDER BY [appointment_status].[id] ASC ', N'@0 varchar(35), @1 int', @0 = 'Open', @1 = 1  [["name", "Open"], ["LIMIT", 1]]
  SQL (0.3ms)  BEGIN TRANSACTION
  SQL (0.5ms)  select newid()
   (1.2ms)  select newid()
Calendar Exists (0.5ms)  EXEC sp_executesql N'SELECT TOP 1  1 AS one FROM [calendar] WHERE [calendar].[id] = @0  ORDER BY [calendar].[id] ASC ', N'@0 uniqueidentifier, @1 int', @0 = '5B0B8280-6237-4791-A023-A7989E9634DF', @1 = 1  [["id", "5B0B8280-6237-4791-A023-A7989E9634DF"], ["LIMIT", 1]]
  SQL (1.9ms)  EXEC sp_executesql N'INSERT INTO [calendar] ([id], [start_date], [stop_date], [short_notes], [subject], [appointmentstatusid], [date_modified], [staff]) VALUES (@0, @1, @2, @3, @4, @5, @6, @7)', N'@0 uniqueidentifier, @1 datetime2(7), @2 datetime2(7), @3 varchar(1000), @4 varchar(255), @5 uniqueidentifier, @6 datetime2(7), @7 varchar(max)', @0 = '5B0B8280-6237-4791-A023-A7989E9634DF', @1 = '04-09-2020 15:37:39.0', @2 = '04-09-2020 12:22:39.0', @3 = 'Optio dolores ab. Exercitationem eos veniam. Maiores nostrum voluptate.', @4 = 'Subject', @5 = 'D0BF3A9C-BA9C-4A45-8B30-A98500A81501', @6 = '04-09-2020 19:54:39.4235502', @7 = '8CFFF4E7-CBEE-4E44-8431-61170E4CD108'  [["id", "5B0B8280-6237-4791-A023-A7989E9634DF"], ["start_date", "04-09-2020 15:37:39.0"], ["stop_date", "04-09-2020 12:22:39.0"], ["short_notes", "Optio dolores ab. Exercitationem eos veniam. Maiores nostrum voluptate."], ["subject", "Subject"], ["appointmentstatusid", "D0BF3A9C-BA9C-4A45-8B30-A98500A81501"], ["date_modified", "04-09-2020 19:54:39.4235502"], ["staff", "8CFFF4E7-CBEE-4E44-8431-61170E4CD108"]]
  SQL (0.7ms)  COMMIT TRANSACTION

ActiveRecord::StatementInvalid (TinyTds::Error: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.: COMMIT TRANSACTION)

Ошибка СУБД

Модератор: Дмитрий Юхтимовский

Вернуться в MS SQL Server для целей 1С:Предприятие

Кто сейчас на форуме

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 1