SQL Server nested transactions

[alt.ua]

Hello,

Does SQL Server nested transactions supported in UniDAC ?

[alt.ua]

UniDAC 8.2.5
Delphi 10.2 Version 25.0.31059.3231
Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

UniConnection is in SQL Server direct mode

Code: Select all

  Conn.StartTransaction;
  Conn.StartTransaction;
  Conn.Commit;
  Conn.Commit;

This code raises exception with message 'Can't perform operation on active transaction' on second StartTransaction.

Code: Select all

DECLARE @cnt INT

BEGIN TRAN
SELECT @cnt = @@TRANCOUNT
PRINT '1: ' + CONVERT(VARCHAR, @cnt)

  BEGIN TRAN
  SELECT @cnt = @@TRANCOUNT
  PRINT '2: ' + CONVERT(VARCHAR, @cnt)

  COMMIT
  SELECT @cnt = @@TRANCOUNT
  PRINT '3: ' + CONVERT(VARCHAR, @cnt)

COMMIT
  SELECT @cnt = @@TRANCOUNT
  PRINT '4: ' + CONVERT(VARCHAR, @cnt)

This code does not raised exception

[alt.ua]

If such transactions are not supported, how to make a feature request for registered customer?

[ertank]

Hello,

You might want to check below old blog and do same tests on your SQL Server 2017 to be sure that nested transactions does actually work.

https://www.sqlskills.com/blogs/paul/a- ... uter%20one.

Thanks & Regards,
Ertan

[alt.ua]

OK, why don't you do such as in this example:

Code: Select all

program nested_transaction_mock;

{$APPTYPE CONSOLE}

uses
  Classes,
  SysUtils,
  Generics.Collections;

type

  TUniConnectionMock = class(TComponent)
  private
    fTransansactionStack: TStack<string>;
    function NewTransactionName: string;
    function GetInTransaction: Boolean;
    function GetTransactionDepth: Integer;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;

    procedure StartTransaction; virtual;
    procedure Commit; virtual;
    procedure Rollback; virtual;

    property InTransaction: Boolean read GetInTransaction;
    property TransactionDepth: Integer read GetTransactionDepth;
  end;





{ TUniConnectionMock }

constructor TUniConnectionMock.Create(AOwner: TComponent);
begin
  inherited Create( AOwner );
  fTransansactionStack := TStack<string>.Create;
end;

destructor TUniConnectionMock.Destroy;
begin
  fTransansactionStack.Free;
  inherited Destroy;
end;


function TUniConnectionMock.GetInTransaction: Boolean;
begin
  Result := GetTransactionDepth > 0;
end;

function TUniConnectionMock.GetTransactionDepth: Integer;
begin
  Result := fTransansactionStack.Count;
end;

function TUniConnectionMock.NewTransactionName: string;
var Guid: TGUID;
begin;
  CreateGUID( Guid );
  Result := GUIDToString( Guid );
  Result := Copy(Result, 2, Length(Result) - 2);
end;


procedure TUniConnectionMock.StartTransaction;
var
  lTransactionname: string;
begin
  lTransactionname := NewTransactionName;
  fTransansactionStack.Push( lTransactionname );
  // place here native code for start SQL Server transaction with generated name
  // BEGIN TRAN <lTransactionname>;
end;

procedure TUniConnectionMock.Commit;
var
  lTransactionname: string;
begin
  if InTransaction then begin
    lTransactionname := fTransansactionStack.Extract;
    // place here native code for commit SQL Server transaction with saved name
    // COMMIT TRAN <lTransactionname>;
  end;
end;


procedure TUniConnectionMock.Rollback;
begin
  if InTransaction then begin
    // place here native code for rollback all nested SQL Server transaction
    // ROLLBACK TRAN
    fTransansactionStack.Clear;
  end;
end;


var
  m: TUniConnectionMock;

  procedure Print;
  begin
    Writeln('InTransaction: ', m.InTransaction,  ', TransactionDepth: ', m.TransactionDepth);
  end;

begin
  m := TUniConnectionMock.Create( nil );
  try
    Print;
    m.StartTransaction;
    Print;
    m.StartTransaction;
    Print;
    m.Commit;
    Print;
    m.Commit;
    Print;

    Writeln('-----------------');

    m.StartTransaction;
    Print;
    m.StartTransaction;
    Print;
    m.Rollback;
    Print;
  finally
    m.Free;
  end;

end.

[Stellar]

UniDAC doesn't support nested transactions in the TUniConnection.StartTransaction and TUniConnection.Commit methods for SQL Server. You may try calling the SQL statements BEGIN TRAN and COMMIT, respectively.

[alt.ua]

how to make a feature request for registered customer?

[Stellar]

UniDAC uses third-party libraries if any of the following providers is used: prAuto, prSQL, prNativeClient, prMSOLEDB.
We're not able to affect third-party libraries, we can only consider implementing nested transactions for Direct mode. If you'd like to request such a feature, please post your suggestion on our user voice forum:
https://devart.uservoice.com/forums/104 ... y_id=18939.