Error on read-only transaction

[Rafael Dipold 0]

Hello friends,

 

Would anyone have a tip why the read-only error happens when I run the FireDAC commands in this order, like the MRE below?

 

    //READ ONLY TRANSACTION
    LTransactionRO := TFDTransaction.Create(nil);
    LQueryRO := TFDQuery.Create(nil);
    try
      LTransactionRO.Connection := FDManager.AcquireConnection('MY_DB', '');
      LTransactionRO.Options.ReadOnly := True;

      LQueryRO.Connection := FDManager.AcquireConnection('MY_DB', '');
      LQueryRO.Transaction := LTransactionRO;
      LQueryRO.Open('SELECT * FROM mY_TABLE'); //OK
    finally
      LQueryRO.Free;
      LTransactionRO.Free;
    end;

    //READ WRITE TRANSACTION
    LTransactionRW := TFDTransaction.Create(nil);
    LQueryRW := TFDQuery.Create(nil);
    try
      LTransactionRW.Connection := FDManager.AcquireConnection('MY_DB', '');
      LTransactionRW.Options.ReadOnly := False;

      LQueryRW.Connection := FDManager.AcquireConnection('MY_DB', '');
      LQueryRW.Transaction := LTransactionRW;

      //ERROR: cannot execute UPDATE in a read-only transaction.
      LQueryRW.ExecSQL('UPDATE MY_TABLE SET COLUM1 = COLUM1 WHERE 1 = 1');
    finally
      LQueryRW.Free;
      LTransactionRW.Free;
    end;

if I comment the line `//LTransactionRO.Options.ReadOnly := True;`, the SQL UPDATE run without errors.

 

Thanks for any tips!

[PeterBelow 218]

Have you tried to explicitly commit or rollback the first transaction before you free the object?

[Rafael Dipold 0]

Yes, same thing.

 

In my project transactions are always started and finished, I removed everything I could in the example above for simplification.

[Stano 140]

Um, in that case, it looks like

• there's a typo somewhere, or
• there's an unintentional transaction substitution

Personally, I would check the code.

[Rafael Dipold 0]

 

Here is a verifiable example, just enter a valid database and table.

 

I don't see anything that could be wrong here, it seems to be a Firedac bug:

 

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  FireDAC.Comp.Client,
  FireDAC.DApt,
  FireDAC.Phys.PG,
  FireDAC.Phys.PGDef,
  FireDAC.Stan.Async,
  FireDAC.Stan.Def,
  FireDAC.Stan.Intf,
  System.SysUtils;

var
  LConnectionDef: IFDStanConnectionDef;
  LQueryRO: TFDQuery;
  LQueryRW: TFDQuery;
  LTransactionRO: TFDTransaction;
  LTransactionRW: TFDTransaction;
begin
  try
    FDManager.DriverDefFileAutoLoad := False;
    FDManager.ConnectionDefs.AutoLoad := False;
    FDManager.ConnectionDefs.Clear;

    FDManager.ConnectionDefs.BeginWrite;
    try
      LConnectionDef := FDManager.ConnectionDefs.AddConnectionDef;
      LConnectionDef.Name := 'MY_DB';

      with TFDPhysPGConnectionDefParams(LConnectionDef.Params) do
      begin
        DriverID := 'PG';
        CharacterSet := TFDPGCharacterSet.csUTF8;
        Server := '127.0.0.1';
        Database := 'MY_DB';
        Port  := 11786;
        UserName := 'myuser';
        Password := '123456';
      end;
    finally
      FDManager.ConnectionDefs.EndWrite;
    end;

    LTransactionRO := TFDTransaction.Create(nil);
    LQueryRO := TFDQuery.Create(nil);
    try
      LTransactionRO.Connection := FDManager.AcquireConnection('MY_DB', '');
      LTransactionRO.Options.ReadOnly := True;

      LQueryRO.Connection := FDManager.AcquireConnection('MY_DB', '');
      LQueryRO.Transaction := LTransactionRO;

      LQueryRO.Open('SELECT * FROM MY_TABLE');
    finally
      LQueryRO.Free;
      LTransactionRO.Free;
    end;

    LTransactionRW := TFDTransaction.Create(nil);
    LQueryRW := TFDQuery.Create(nil);
    try
      LTransactionRW.Connection := FDManager.AcquireConnection('MY_DB', '');
      LTransactionRW.Options.ReadOnly := False;

      LQueryRW.Connection := FDManager.AcquireConnection('MY_DB', '');
      LQueryRW.Transaction := LTransactionRW;

      //ERROR: cannot execute UPDATE in a read-only transaction.
      LQueryRW.ExecSQL('UPDATE MY_TABLE SET COLUMN_1 = COLUMN_1 WHERE 1=1');
    finally
      LQueryRW.Free;
      LTransactionRW.Free;
    end;

    Writeln('OK');
    Readln;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.

 

[Rolphy Reyes 0]

Hi!

Maybe I'm wrong but I think that you get two "different" connections when the AcquireConnection method is call.

 

I would do this:

 

LTransactionRW.Connection := FDManager.AcquireConnection('MY_DB', '');
LTransactionRW.Options.ReadOnly := False;

LQueryRW.Connection := LTransactionRW.Connection;
LQueryRW.Transaction := LTransactionRW;

 

[Stano 140]

I didn't even know about TFDManager. There is no answer here:

Personally, I always work with a single Connection (in the DataModule) with a single transaction that I store in a variable.

[Rafael Dipold 0]

Hi, 

 

I can consider now this problem solved.

As you can read here: Currently, explicit transaction object assignment is supported only for InterBase and Firebird.

I have always worked with Firebird and I was guided by the same logic to manage PostgreSQL transactions. And now I see that I will have to redesign my connection layer.

Thanks