one TZConnection and two TZTransactions

[chico_lopes]

Good evening, I'm trying to use a TZConnection and two TZTransactions to create a connection with two isolated transactions, so that only one can see what the other is doing after the commit. However, without success.

Is there an example for this?

I tried to do it as follows, as shown below

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  ZConnection.Connect;
  ZTransaction1.Connection:= ZConnection;
  ZTransaction2.Connection:= ZConnection;
  
  qry1.Connection:= ZConnection; 
  qry2.Connection:= ZConnection; 
  qry1.transaction:= ZTransaction1; 
  qry2.transaction:= ZTransaction2; 
  
  if not ZTransaction1.Active then
    ZTransaction1.StartTransaction;
  with Qry1 do
    begin
      close;
      sql.Text := Memo1.Lines.Text;
      open;
    end;
  
  if not ZTransaction2.Active then
    ZTransaction2.StartTransaction;
  with Qry2 do
    begin
      close;
      sql.Text := Memo2.Lines.Text;
      open;
    end;
      
end;

[marsupilami]

Hello,

this example lacks some necessary information:
-> What SQL queries do you feed into Qry1 and Qry2?
-> What outcome do you expect?
-> What outcome do you see?
-> What is the selected Transaction Isolation Level in the Basic connection and in the TZTransaction components?
-> Which database do you use?
-> Which compiler do you use?

Maybe you can share a full test?

Best regards,

Jan

[chico_lopes]

-> What SQL queries do you feed into Qry1 and Qry2?
The Select is the same in both queries

> What outcome do you expect?
In Qry1 I did an update and in Qry2 I did a reflash and a rollback when I did a reflash in Qry1 it brought the result of Qry2

-> What outcome do you see?
I hope that one transaction does not see the other without commit

-> What is the selected Transaction Isolation Level in the Basic connection and in the TZTransaction components?
I tested several, I need to know which is the correct one

-> Which database do you use?
firebird

-> Which compiler do you use?
delphi

The example is attached

.PAS

Code: Select all

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, IniFiles, StdCtrls, ZAbstractConnection, ZConnection, DB, ZDbcIntfs,
  ZAbstractRODataset, ZAbstractDataset, ZDataset, Grids, DBGrids, ExtCtrls,
  ZTransaction, Vcl.DBCtrls;

type
  TForm1 = class(TForm)
    ZDbNext: TZConnection;
    Panel1: TPanel;
    Button1: TButton;
    Edt_Connection: TEdit;
    Panel2: TPanel;
    Panel3: TPanel;
    Splitter1: TSplitter;
    DBGrid1: TDBGrid;
    Panel4: TPanel;
    Memo1: TMemo;
    Panel5: TPanel;
    Button2: TButton;
    Qry1: TZQuery;
    DataSource1: TDataSource;
    Panel6: TPanel;
    Panel7: TPanel;
    Memo2: TMemo;
    Panel8: TPanel;
    Button3: TButton;
    DBGrid2: TDBGrid;
    Qry2: TZQuery;
    DataSource2: TDataSource;
    ZTransaction1: TZTransaction;
    ZTransaction2: TZTransaction;
    Button4: TButton;
    Button5: TButton;
    Edt_trans1: TEdit;
    Edt_trans2: TEdit;
    Button6: TButton;
    Rg_Isolation: TRadioGroup;
    procedure ZDbNextBeforeConnect(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure ZDbNextAfterConnect(Sender: TObject);
    procedure ZDbNextAfterDisconnect(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure ZTransaction1AfterStartTransaction(Sender: TObject);
    procedure ZTransaction1AfterCommit(Sender: TObject);
    procedure ZTransaction1AfterRollback(Sender: TObject);
    procedure ZTransaction1BeforeCommit(Sender: TObject);
    procedure ZTransaction2BeforeCommit(Sender: TObject);
    procedure ZTransaction2AfterCommit(Sender: TObject);
    procedure ZTransaction2AfterRollback(Sender: TObject);
    procedure ZTransaction2AfterStartTransaction(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure ZTransaction1BeforeStartTransaction(Sender: TObject);
    procedure ZTransaction2BeforeStartTransaction(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.ZDbNextBeforeConnect(Sender: TObject);
begin
  if ZDbNext.tag = 0 then
    begin
      with ZDbNext do
        begin
          Disconnect;
          case Rg_Isolation.ItemIndex of
            0 : TransactIsolationLevel := tiNone;
            1 : TransactIsolationLevel := tiReadCommitted;
            2 : TransactIsolationLevel := tiReadUncommitted;
            3 : TransactIsolationLevel := tiRepeatableRead;
            4 : TransactIsolationLevel := tiSerializable;
          end;
          AutoCommit := false;
          SQLHourGlass := false ;
          Protocol:='firebird';
          User := 'SYSDBA';
          Password := 'masterkey';
          Tag := 1;
          try
            Connect;
          except
            Disconnect;
          end;
        end;
    end;
end;

procedure TForm1.ZTransaction1AfterCommit(Sender: TObject);
begin
  Edt_trans1.Text := 'commit';
end;

procedure TForm1.ZTransaction1AfterRollback(Sender: TObject);
begin
  Edt_trans1.Text := 'Roolback';
end;

procedure TForm1.ZTransaction1AfterStartTransaction(Sender: TObject);
begin
  Edt_trans1.Text := 'Aberto';
end;

procedure TForm1.ZTransaction1BeforeCommit(Sender: TObject);
begin
  Qry1.close;
end;

procedure TForm1.ZTransaction1BeforeStartTransaction(Sender: TObject);
begin
  ZTransaction1.TransactIsolationLevel := ZDbNext.TransactIsolationLevel;
end;

procedure TForm1.ZTransaction2AfterCommit(Sender: TObject);
begin
  Edt_trans2.Text := 'commit';
end;

procedure TForm1.ZTransaction2AfterRollback(Sender: TObject);
begin
  Edt_trans2.Text := 'Roolback';
end;

procedure TForm1.ZTransaction2AfterStartTransaction(Sender: TObject);
begin
  Edt_trans2.Text := 'Aberto';
end;

procedure TForm1.ZTransaction2BeforeCommit(Sender: TObject);
begin
  Qry2.close;
end;

procedure TForm1.ZTransaction2BeforeStartTransaction(Sender: TObject);
begin
  ZTransaction2.TransactIsolationLevel := ZDbNext.TransactIsolationLevel;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ZDbNext.Connect;
end;

procedure TForm1.ZDbNextAfterConnect(Sender: TObject);
begin
  Edt_Connection.Text := 'Conectado';
end;

procedure TForm1.ZDbNextAfterDisconnect(Sender: TObject);
begin
  Edt_Connection.Text := 'Desconcetado!';
end;

procedure TForm1.Button2Click(Sender: TObject);
begin

  if not ZDbNext.Connected then
    ZDbNext.Connect;


  if not ZTransaction1.Active then
    ZTransaction1.StartTransaction;
  with Qry1 do
    begin
      close;
      sql.Text := Memo1.Lines.Text;
      open;
    end;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  if not ZDbNext.Connected then
    ZDbNext.Connect;

  if not ZTransaction2.Active then
    ZTransaction2.StartTransaction;
  with Qry2 do
    begin
      close;
      sql.Text := Memo2.Lines.Text;
      open;
    end;
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
  if ZTransaction1.Active then
    ZTransaction1.Rollback;
end;

procedure TForm1.Button5Click(Sender: TObject);
begin
  if ZTransaction2.Active then
    ZTransaction2.Rollback;
end;

procedure TForm1.Button6Click(Sender: TObject);
begin
  ZDbNext.Disconnect;
end;

end.

.DFM

Code: Select all

object Form1: TForm1
  Left = 192
  Top = 117
  Caption = 'Form1'
  ClientHeight = 513
  ClientWidth = 930
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  TextHeight = 13
  object Panel1: TPanel
    Left = 0
    Top = 0
    Width = 169
    Height = 513
    Align = alLeft
    BevelOuter = bvNone
    TabOrder = 0
    object Button1: TButton
      Left = 46
      Top = 50
      Width = 75
      Height = 25
      Caption = 'Conectar'
      TabOrder = 0
      OnClick = Button1Click
    end
    object Edt_Connection: TEdit
      Left = 22
      Top = 81
      Width = 121
      Height = 24
      Color = clScrollBar
      Font.Charset = DEFAULT_CHARSET
      Font.Color = clWindowText
      Font.Height = -13
      Font.Name = 'MS Sans Serif'
      Font.Style = []
      ParentFont = False
      ReadOnly = True
      TabOrder = 1
    end
    object Button6: TButton
      Left = 46
      Top = 19
      Width = 75
      Height = 25
      Caption = 'Disconectar'
      TabOrder = 2
      OnClick = Button6Click
    end
    object Rg_Isolation: TRadioGroup
      Left = 20
      Top = 111
      Width = 143
      Height = 105
      Caption = 'Isolation'
      ItemIndex = 0
      Items.Strings = (
        'tiNone'
        'tiReadCommitted'
        'tiReadUncommitted'
        'tiRepeatableRead'
        'tiSerializable')
      TabOrder = 3
    end
  end
  object Panel2: TPanel
    Left = 169
    Top = 0
    Width = 761
    Height = 513
    Align = alClient
    BevelOuter = bvNone
    TabOrder = 1
    object Splitter1: TSplitter
      Left = 0
      Top = 201
      Width = 761
      Height = 3
      Cursor = crVSplit
      Align = alTop
      ExplicitWidth = 769
    end
    object Panel3: TPanel
      Left = 0
      Top = 0
      Width = 761
      Height = 201
      Align = alTop
      BevelOuter = bvNone
      TabOrder = 0
      object DBGrid1: TDBGrid
        Left = 0
        Top = 105
        Width = 761
        Height = 96
        Align = alClient
        DataSource = DataSource1
        TabOrder = 0
        TitleFont.Charset = DEFAULT_CHARSET
        TitleFont.Color = clWindowText
        TitleFont.Height = -11
        TitleFont.Name = 'MS Sans Serif'
        TitleFont.Style = []
      end
      object Panel4: TPanel
        Left = 0
        Top = 0
        Width = 761
        Height = 105
        Align = alTop
        BevelOuter = bvNone
        TabOrder = 1
        object Memo1: TMemo
          Left = 0
          Top = 0
          Width = 666
          Height = 105
          Align = alClient
          Lines.Strings = (
            'select * from tabusuar')
          TabOrder = 0
        end
        object Panel5: TPanel
          Left = 666
          Top = 0
          Width = 95
          Height = 105
          Align = alRight
          BevelOuter = bvNone
          TabOrder = 1
          object Button2: TButton
            Left = 14
            Top = 16
            Width = 75
            Height = 25
            Caption = 'Executar'
            TabOrder = 0
            OnClick = Button2Click
          end
          object Button4: TButton
            Left = 12
            Top = 47
            Width = 75
            Height = 25
            Caption = 'Roolback'
            TabOrder = 1
            OnClick = Button4Click
          end
          object Edt_trans1: TEdit
            Left = 0
            Top = 81
            Width = 95
            Height = 24
            Align = alBottom
            Color = clScrollBar
            Font.Charset = DEFAULT_CHARSET
            Font.Color = clWindowText
            Font.Height = -13
            Font.Name = 'MS Sans Serif'
            Font.Style = []
            ParentFont = False
            ReadOnly = True
            TabOrder = 2
          end
        end
      end
    end
    object Panel6: TPanel
      Left = 0
      Top = 204
      Width = 761
      Height = 213
      Align = alTop
      BevelOuter = bvNone
      TabOrder = 1
      object Panel7: TPanel
        Left = 0
        Top = 0
        Width = 761
        Height = 105
        Align = alTop
        BevelOuter = bvNone
        TabOrder = 0
        object Memo2: TMemo
          Left = 0
          Top = 0
          Width = 666
          Height = 105
          Align = alClient
          Lines.Strings = (
            'select * from tabusuar')
          TabOrder = 0
        end
        object Panel8: TPanel
          Left = 666
          Top = 0
          Width = 95
          Height = 105
          Align = alRight
          BevelOuter = bvNone
          TabOrder = 1
          object Button3: TButton
            Left = 14
            Top = 8
            Width = 75
            Height = 25
            Caption = 'Executar'
            TabOrder = 0
            OnClick = Button3Click
          end
          object Button5: TButton
            Left = 12
            Top = 39
            Width = 75
            Height = 25
            Caption = 'Roolback'
            TabOrder = 1
            OnClick = Button5Click
          end
          object Edt_trans2: TEdit
            Left = 0
            Top = 81
            Width = 95
            Height = 24
            Align = alBottom
            Color = clScrollBar
            Font.Charset = DEFAULT_CHARSET
            Font.Color = clWindowText
            Font.Height = -13
            Font.Name = 'MS Sans Serif'
            Font.Style = []
            ParentFont = False
            ReadOnly = True
            TabOrder = 2
          end
        end
      end
      object DBGrid2: TDBGrid
        Left = 0
        Top = 105
        Width = 761
        Height = 108
        Align = alClient
        DataSource = DataSource2
        TabOrder = 1
        TitleFont.Charset = DEFAULT_CHARSET
        TitleFont.Color = clWindowText
        TitleFont.Height = -11
        TitleFont.Name = 'MS Sans Serif'
        TitleFont.Style = []
      end
    end
  end
  object ZDbNext: TZConnection
    ControlsCodePage = cGET_ACP
    Catalog = ''
    Properties.Strings = (
      'controls_cp=GET_ACP'
      'RawStringEncoding=DB_CP')
    AutoCommit = False
    TransactIsolationLevel = tiRepeatableRead
    BeforeConnect = ZDbNextBeforeConnect
    AfterConnect = ZDbNextAfterConnect
    AfterDisconnect = ZDbNextAfterDisconnect
    DisableSavepoints = False
    HostName = ''
    Port = 0
    Database = 
      'localhost/3051:C:\Next system Information\Projetos\Trunk\Alpes\D' +
      'atabank\ALPES.FDB'
    User = 'SYSDBA'
    Password = 'masterkey'
    Protocol = 'firebird'
    Left = 188
    Top = 42
  end
  object Qry1: TZQuery
    Connection = ZDbNext
    Transaction = ZTransaction1
    Params = <>
    Left = 625
    Top = 32
  end
  object DataSource1: TDataSource
    DataSet = Qry1
    Left = 657
    Top = 32
  end
  object Qry2: TZQuery
    Connection = ZDbNext
    Transaction = ZTransaction2
    Params = <>
    Left = 569
    Top = 248
  end
  object DataSource2: TDataSource
    DataSet = Qry2
    Left = 601
    Top = 248
  end
  object ZTransaction1: TZTransaction
    Connection = ZDbNext
    BeforeStartTransaction = ZTransaction1BeforeStartTransaction
    AfterStartTransaction = ZTransaction1AfterStartTransaction
    BeforeCommit = ZTransaction1BeforeCommit
    AfterCommit = ZTransaction1AfterCommit
    BeforeRollback = ZTransaction1BeforeCommit
    AfterRollback = ZTransaction1AfterRollback
    TransactIsolationLevel = tiSerializable
    Left = 560
    Top = 32
  end
  object ZTransaction2: TZTransaction
    Connection = ZDbNext
    BeforeStartTransaction = ZTransaction2BeforeStartTransaction
    AfterStartTransaction = ZTransaction2AfterStartTransaction
    BeforeCommit = ZTransaction2BeforeCommit
    AfterCommit = ZTransaction2AfterCommit
    BeforeRollback = ZTransaction2BeforeCommit
    AfterRollback = ZTransaction2AfterRollback
    TransactIsolationLevel = tiSerializable
    Left = 528
    Top = 248
  end
end

[chico_lopes]

Can @marsupilami help me?

[marsupilami]

Hello,

currently I can't help with this. I added a test to the Zeos test suite, which should demonstrate your problem:

Code: Select all

procedure ZTestCompInterbaseBugReport.TestForum225651;
var
  T1, T2: TZTransaction;
  Q1, Q2: TZQuery;

  function CreateTransaction: TZTransaction;
  begin
    Result := TZTransaction.Create(nil);
    Result.Connection := Connection;
    Result.TransactIsolationLevel := tiReadCommitted;
    Result.AutoCommit := false;
  end;
const
  SQL = 'select * from equipment where eq_id = 20241109';
begin
  Connection.Connect;
  try
    T1 := CreateTransaction;
    T2 := CreateTransaction;
    Q1 := CreateQuery;
    Q1.Transaction := T1;
    Q2 := CreateQuery;
    Q2.Transaction := T2;

    Connection.ExecuteDirect('delete from equipment where eq_id = 20241109');
    Connection.ExecuteDirect('insert into equipment (eq_id, eq_name) values (20241109, ''Equipment1'')');

    Q1.SQL.Text := SQL;
    Q2.SQL.Text := SQL;

    Q1.Open;
    Q2.Open;

    CheckEquals(1, Q1.RecordCount, 'Q1.RecordCount = 1? V1');
    CheckEquals(1, Q2.RecordCount, 'Q2.RecordCount = 1? V1');
    CheckEquals('Equipment1', Q1.FieldByName('eq_name').AsString, 'Q1->eq_id = Equipment1? V1');
    CheckEquals('Equipment1', Q2.FieldByName('eq_name').AsString, 'Q2->eq_id = Equipment1? V1');

    Q1.Edit;
    Q1.FieldByName('eq_name').AsString := 'Equipment2';
    Q1.Post;

    Q1.Close;
    Q2.Close;

    Q1.Open;
    Q2.Open;

    CheckEquals(1, Q1.RecordCount, 'Q1.RecordCount = 1? V2');
    CheckEquals(1, Q2.RecordCount, 'Q2.RecordCount = 1? V2');
    CheckEquals('Equipment2', Q1.FieldByName('eq_name').AsString, 'Q1->eq_name = Equipment2? V2');
    CheckEquals('Equipment1', Q2.FieldByName('eq_name').AsString, 'Q2->eq_name = Equipment1? V2');
  finally
    if Assigned(Q1) then
      FreeAndNil(Q1);
    if Assigned(Q2) then
      FreeAndNil(Q2);
    if Assigned(T1) then
      FreeAndNil(T1);
    if Assigned(T2) then
      FreeAndNil(T2);
  end;
end;

It will take some time to check what goes wrong. Currently I can only suggest to use two different connections instead of two transactions.

Best regards,

Jan