SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[kyunghu]

When declaring 'StartTransaction' in Delphi
When using the 'SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED' statement

MariaDB and SQLServer get no error.

PostgreSQL gives an error.
==> SET TRANSACTION ISOLATION LEVEL must be called before any query

By default, PostTransSQL changes to READ committed when StartTransaction is executed in unidac even if SET TRANSACTION ISOLATION LEVEL READ uncommitted is declared.


delphi
sql server
procedure TForm1.Button8Click(Sender: TObject);
var
SQLText : string;
begin
SQLText := 'exec TR_TEST ''' + 'aa' + '''';
MyQuery2(UniConnection1, UniQuery1,SQLText);
end;

MariaDB
procedure TForm1.Button7Click(Sender: TObject);
var
SQLText : string;
begin
SQLText := 'call TR_TEST (''' + 'aa' + ''')';
MyQuery2(UniConnection2, UniQuery2,SQLText);
end;

postgreSQL
procedure TForm1.Button9Click(Sender: TObject);
var
SQLText : string;
begin
SQLText := 'select * from TR_TEST (''' + 'aa' + ''')';
MyQuery2(UniConnection3, UniQuery3,SQLText);
end;

procedure TForm1.MyQuery2(UniConnection : TUniConnection; Query : TUniQuery; SQLString : string);
begin
try
UniConnection.StartTransaction;
Query.Close;
Query.SQL.Clear;
Query.SQL.Text := SQLString;
Query.ExecSQL;
UniConnection.Commit;
except on E:EUniError do begin
UniConnection.Rollback;
Memo1.Lines.Add(IntToStr(E.ErrorCode));
Memo1.Lines.Add(E.Message);
end;
end;
end;


ex)
Table(Common table)
CREATE TABLE TEST00 (
TEST1 VARCHAR(50)
);

1. SQL Server Stored Procedure
CREATE PROCEDURE TR_TEST
@i_TEST1 VARCHAR(50)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

INSERT INTO TEST00
(TEST1)
VALUES
(@i_TEST1);

SET NOCOUNT OFF

2. MariaDB Stored Procedure
CREATE OR REPLACE PROCEDURE TR_TEST(
$i_TEST1 VARCHAR(50)
)
BEGIN
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

INSERT INTO TEST00
(TEST1)
VALUES
($i_TEST1);

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

END;

3. postgreSQL Stored Procedure
CREATE OR REPLACE FUNCTION public.TR_TEST(
i_TEST1 character varying
)
RETURNS void AS
$BODY$
BEGIN
SET TRANSACTION ISOLATION LEVEL READ uncommitted;

INSERT INTO TEST00
(TEST1)
VALUES
(i_TEST1);
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

[MaximG]

The specifics of transaction handling in PostgreSQL cause such behavior. Our components don't impact it by any means. You invoke a function which is executed within the current transaction. To set an isolation level for the transaction, you need to execute SET TRANSACTION ISOLATION LEVEL READ uncommitted before invoking public.TR_TES, instead of executing the function within the transaction:

Code: Select all

CREATE OR REPLACE FUNCTION public.TR_TEST(
i_TEST1 character varying
)
RETURNS void AS
$BODY$
BEGIN
INSERT INTO TEST00
(TEST1)
VALUES
(i_TEST1);
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Code: Select all

procedure TForm1.Button9Click(Sender: TObject);
var 
  SQLText : string;
begin
  UniConnection3.ExecSQL('SET TRANSACTION ISOLATION LEVEL READ uncommitted');
  SQLText := 'select * from TR_TEST (''' + 'aa' + ''')';
  MyQuery2(UniConnection3, UniQuery3,SQLText);
end;

[kyunghu]

Tested but won't change.

The Linux log is shown below.

procedure TForm1.Button8Click(Sender: TObject);
var
SQLText : string;
begin
PostConnection2.ExecSQL('SET TRANSACTION ISOLATION LEVEL READ uncommitted');
SQLText := 'select * from TR_TEST (''' + 'aa' + ''')';

PostConnection2.StartTransaction;
PostQuery2.Close;
PostQuery2.SQL.Clear;
PostQuery2.SQL.Text := SQLText;
PostQuery2.ExecSQL;
PostConnection2.Commit;
end;


< 2019-11-26 10:56:38.044 KST:[email protected] >LOG: statement: SET TRANSACTION ISOLATION LEVEL READ uncommitted
< 2019-11-26 10:56:38.044 KST:[email protected] >WARNING: SET TRANSACTION can only be used in transaction blocks
< 2019-11-26 10:56:38.047 KST:[email protected] >LOG: statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED
< 2019-11-26 10:56:38.049 KST:[email protected] >LOG: statement: select * from TR_TEST ('aa')
< 2019-11-26 10:56:38.053 KST:[email protected] >LOG: statement: COMMIT

[MaximG]

To set up the required isolation level for the transaction using our components, execute the following code instead of the statement "SET TRANSACTION ISOLATION LEVEL READ uncommitted" :

Code: Select all

    ...
    uses CRAccess;
    ...
    UniConnection.StartTransaction(ilReadUnCommitted);