Nested Transactions with Batch Operations
Posted: Mon 20 Apr 2020 14:53
I'm using Batch Operations according to the documentation (https://www.devart.com/sdac/docs/batchops.htm), but there is a problem with using a Transaction around the Batch-Operation.
It seems like Execute(x, x) is trying to use an implicit_transaction, but doesn't expect an already open transaction.
Wenn using Execute it executes "set implicit_transactions on SET NO_BROWSETABLE OFF" before the actual CommandText and afterwards "IF @@TRANCOUNT > 0 COMMIT TRAN" gets executed, but when a transaction is already open "implicit_transactions on" doesn't start a new transaction, which results in getting the outer transaction committed.
Here is a simplified example of what happens:
I looked everywhere but this behavior isn't documented.
It seems like Execute(x, x) is trying to use an implicit_transaction, but doesn't expect an already open transaction.
Wenn using Execute it executes "set implicit_transactions on SET NO_BROWSETABLE OFF" before the actual CommandText and afterwards "IF @@TRANCOUNT > 0 COMMIT TRAN" gets executed, but when a transaction is already open "implicit_transactions on" doesn't start a new transaction, which results in getting the outer transaction committed.
Here is a simplified example of what happens:
Code: Select all
SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS OFF;
WHILE (@@TRANCOUNT > 0) COMMIT TRANSACTION;
CREATE TABLE #t1 (a INT);
GO
PRINT N'[01] BEGIN TRANSACTION';
BEGIN TRANSACTION;
PRINT N'[01] @@TRANCOUNT == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
PRINT N'[02] SET IMPLICIT_TRANSACTIONS ON (SDAC)';
SET IMPLICIT_TRANSACTIONS ON;
PRINT N'[02] @@TRANCOUNT == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
PRINT N'[03] INSERT INTO';
INSERT INTO #t1 VALUES (11), (12);
PRINT N'[03] @@TRANCOUNT == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
go
PRINT N'[04] IF @@TRANCOUNT > 0 COMMIT TRAN (SDAC)';
IF @@TRANCOUNT > 0 COMMIT TRAN;
PRINT N'[04] @@TRANCOUNT == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
PRINT N'[05] COMMIT TRANSACTION';
COMMIT TRANSACTION;
PRINT N'[05] @@TRANCOUNT == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
DROP TABLE #t1
GOCode: Select all
[01] BEGIN TRANSACTION
[01] @@TRANCOUNT == 1
[02] SET IMPLICIT_TRANSACTIONS ON (SDAC)
[02] @@TRANCOUNT == 1
[03] INSERT INTO
[03] @@TRANCOUNT == 1
[04] IF @@TRANCOUNT > 0 COMMIT TRAN (SDAC)
[04] @@TRANCOUNT == 0
[05] COMMIT TRANSACTION
Meldung 3902, Ebene 16, Status 1, Zeile 28
Die COMMIT TRANSACTION-Anforderung hat keine entsprechende BEGIN TRANSACTION-Anweisung.
[05] @@TRANCOUNT == 0