How to set transaction isolation level (in PostgreSQL DBExpress driver)?

[FisaKV]

Hi,
I'm struggling to set up a transaction isolation of TCRSQLConnection. I need repeatable read isolation level. I've tried this:

Code: Select all

function SQLSelectInt( SQLcmd: string; conn: TSQLConnection; defaultVal: int64 = 0): int64;
var
  DS                         :TCustomSQLDataSet;
  v                          :variant;
begin
  try
    conn.Execute( SQLcmd, nil, @DS);
    v := DS.Fields[0].Value;
    if VarIsNull( v) then
      result := defaultVal
    else
      result := v;
  except
    on E:exception do
      raise exception.Create( e.Message + #13#10 + #13#10 + SQLcmd);
  end;
end;

procedure TForm2.FormCreate(Sender: TObject);
var
  tr: TDBXTransaction;
  i: int64;
begin
  CRSQLConnection1.params.Values['TransIsolation'] := 'RepeatableRead';                                           //doesn't work
  CRSQLConnection1.Open;
  CRSQLConnection1.ExecuteDirect( 'SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;'); //doesn't work
  tr := CRSQLConnection1.BeginTransaction(
    TDBXIsolations.RepeatableRead);                                                                               //doesn't work
  CRSQLConnection1.ExecuteDirect( 'SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;');                            //doesn't work 
  i := SQLSelectInt('select max(sr_id) from public.sortiment', CRSQLConnection1); //breakpoint here for insert record from another connection
  showMessage( IntToStr(i));                                                      //here I see sr_id inserted after I started transaction :-( 
  CRSQLConnection1.CommitFreeAndNil(tr);
end;

We use Devart PgSQL DBExpress driver v.2.1.3, but now I've tried the last v.4.1.3 with same result. If I try the same scenario (same SQL commands) in DBeaver (with repeatable read transIsolation set), it works well - I don't see new record inserted from another connection after starting my transaction.

Thanks a lot for any reaction.
Pavel
(Delphi XE2, PostgreSQL 9.6.6)

[oleg0k]

Hello,

Thank you for the information. We've reproduced the issue and are currently working to resolve it. We'll keep you updated on the progress.

wbr, Oleg
DAC Team

[FisaKV]

Hello DevArt team,
is there any progress in this issue?
Or at least an estimate of when it will be solved?

Thanks a lot for any reaction.
Pavel

[oleg0k]

Hello,

We're still working on the issue, but cannot provide any ETA at the moment. We'll notify you when we fix it.

wbr, Oleg
Devart Team

[FisaKV]

Hello DevArt team,
Since Mon 27 Apr 2020 - no progress, no reaction :-(

Is there any progress in this issue?
Or at least an estimate of when it will be solved?

Thanks a lot for any reaction.
Pavel

[oleg0k]

Hello,

We're still working on the issue, but cannot provide any ETA at the moment. We'll notify you when we fix it.

wbr, Oleg
Devart Team