Nested Transactions with Batch Operations

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Sebastian
Posts: 1
Joined: Mon 20 Apr 2020 14:26

Nested Transactions with Batch Operations

Post by Sebastian » 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:

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
GO

Code: 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
I looked everywhere but this behavior isn't documented.

Stellar
Devart Team
Posts: 347
Joined: Tue 03 Oct 2017 11:00

Re: Nested Transactions with Batch Operations

Post by Stellar » Tue 21 Apr 2020 10:28

Unfortunately, we can't reproduce the issue. To investigate this behavior of SDAC, please compose a full sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

Stellar
Devart Team
Posts: 347
Joined: Tue 03 Oct 2017 11:00

Re: Nested Transactions with Batch Operations

Post by Stellar » Fri 24 Apr 2020 08:20

Thank you for the information. Internal transactions were removed from batch operations in the latest version of SDAC.
Please check whether the issue occurs in the latest version 9.1.3 of SDAC and let us know the result.

Post Reply