Nested Transactions -Oracle

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
esgaltur
Posts: 2
Joined: Wed 27 Mar 2019 11:59

Nested Transactions -Oracle

Post by esgaltur » Thu 28 Mar 2019 00:18

Hi,
I have a problem with nested transactions with Oracle 12.2c driver version 6.10.15.0
For example:
I call function BeginTransaction from the SQLConnection. then I want to start a nested transaction then get an error Transaction is already active, but for example, when I use driver 4.20.0.8, the error does not appear.
Can anyone help, please?

I wrote a simple example with two buttons which start the new transactions, firstly I click one button then second. and got an error:
Image
Code:

Code: Select all

unit uMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxGraphics, cxLookAndFeels, cxLookAndFeelPainters, Menus, StdCtrls, cxButtons,uDM,DBXCommon;

type
  TfMain = class(TForm)
    btnStartFirstTransaction: TcxButton;
    btnStartSecondTransaction: TcxButton;
    procedure btnStartFirstTransactionClick(Sender: TObject);
    procedure btnStartSecondTransactionClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fMain: TfMain;

implementation

{$R *.dfm}

procedure TfMain.btnStartFirstTransactionClick(Sender: TObject);
begin
fDM.MainConnection.BeginTransaction;

end;

procedure TfMain.btnStartSecondTransactionClick(Sender: TObject);
begin
fDM.MainConnection.BeginTransaction;
end;
end.

Code: Select all

unit uDM;

interface

uses
  SysUtils, Classes, WideStrings, DB, SqlExpr;

type
  TfDM = class(TDataModule)
    MainConnection: TSQLConnection;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fDM: TfDM;

implementation

{$R *.dfm}

end.
settings of the connection:
Image
Looks like a bug, I thought that databases support nested transactions.
Thanks.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Nested Transactions -Oracle

Post by MaximG » Fri 26 Jul 2019 07:57

Oracle Database doesn't support nested transactions. To implement the necessary behavior, you can try to use SAVEPONT. See this page for details: https://docs.oracle.com/database/121/CN ... m#CNCPT016

esgaltur
Posts: 2
Joined: Wed 27 Mar 2019 11:59

Re: Nested Transactions -Oracle

Post by esgaltur » Fri 26 Jul 2019 09:20

MaximG, thanks for your answer,
But why with the previous version of the driver(4.20.0.8) this error does not appear? it was some kind of fix? Or requirements from the new version of Oracle Database?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Nested Transactions -Oracle

Post by MaximG » Fri 09 Aug 2019 13:31

We couldn't reproduce the issue by following the above steps in our test environment. Please try checking the behavior of the latest dbExpress driver for Oracle version 6.11.16 by creating a new project and executing the following code snippet:

Code: Select all

var
  SQLConnection: TSQLConnection;
begin
  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.DriverName := 'DevartOracleDirect';
    SQLConnection.Params.Values['User_Name'] := 'scott';
    SQLConnection.Params.Values['Password'] := 'tiger';
    SQLConnection.Params.Values['DataBase'] := '<your Oracle Sevrver>:1521:<your Service Name>';
    SQLConnection.Connected := True;
    SQLConnection.BeginTransaction;
    SQLConnection.BeginTransaction;
    SQLConnection.BeginTransaction;
  finally
    SQLConnection.Free;
  end;
end;
...
Please let us know the result.

Post Reply