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

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for PostgreSQL in Delphi and C++Builder
Post Reply
FisaKV
Posts: 2
Joined: Tue 24 Mar 2020 07:34

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

Post by FisaKV » Tue 24 Mar 2020 10:14

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
Devart Team
Posts: 65
Joined: Wed 11 Mar 2020 08:28

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

Post by oleg0k » Fri 27 Mar 2020 09:25

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
Posts: 2
Joined: Tue 24 Mar 2020 07:34

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

Post by FisaKV » Tue 21 Apr 2020 06:59

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
Devart Team
Posts: 65
Joined: Wed 11 Mar 2020 08:28

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

Post by oleg0k » Mon 27 Apr 2020 10:14

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

Post Reply