TUniTransaction bug ?

[kyunghu]

MariaDB, PostgreSQL :
UniTransaction1.IsolationLevel := ilReadUnCommitted;

try
UniConnection.StartTransaction;
Query.Close;
Query.SQL.Clear;
Query.SQL.Text := 'insert into.....';
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;

UniQuery5.Close;
UniQuery5.SQL.Clear;
UniQuery5.SQL.Text := 'select * from TEST00';
UniQuery5.Open;
showmessage(UniQuery5.Fields[0].AsString);

==> ReadUnCommitted has been applied. No problem

SQL Server :
UniTransaction2.IsolationLevel := ilReadUnCommitted;

try
UniConnection.StartTransaction;
Query.Close;
Query.SQL.Clear;
Query.SQL.Text := 'insert into.....';
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;

UniQuery5.Close;
UniQuery5.SQL.Clear;
UniQuery5.SQL.Text := 'select * from TEST00';
UniQuery5.Open;
showmessage(UniQuery5.Fields[0].AsString);

==> Showmessage not showing due to lock

[Stellar]

Unfortunately, we can't reproduce the issue. To investigate this behavior of UniDAC, please compose a small sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

[Stellar]

In your example, your start a transaction with the ReadUnCommitted isolation level when a record is added to the table.
Then, you create a separate connection to read data from the same table with the default isolation level ReadCommitted.
If you want to perform a dirty read, set ilReadUnCommitted for the transaction which is used to read data.
For example:

Code: Select all

MSTransaction1.DefaultConnection := MSConnection1;
MSTransaction1.IsolationLevel := ilReadUnCommitted;

MSTransaction1.StartTransaction;
try 
  MSQuery1.SQL.Text := 'SELECT TEST1 FROM TEST00';
  MSQuery1.Open;

  MSTransaction1.Commit;
except
  MSTransaction1.Rollback;
end;

[kyunghu]

In PostgreSQL or MariaDB, you don't have to use StartTransaction when looking up. Is it necessary to do StartTransaction when looking up in SQL Server?
In PostgreSQL or MariaDB, it is not queried when it is not in Commit. When you select it, it is queried even though it is not in Commit.

const
SQLText = 'INSERT INTO TEST00(TEST1) values (''' + 'AAA' + ''')';
SQLText2 = 'SELECT TEST1 FROM TEST00';

ex) SQL Server
1. Insert
try
MSConnection.StartTransaction;
MSQuery.Close;
MSQuery.SQL.Clear;
MSQuery.SQL.Text := SQLText;
MSQuery.ExecSQL;
// MSConnection.Commit;
except on E:EUniError do begin
MSConnection.Rollback;
Memo1.Lines.Add(IntToStr(E.ErrorCode));
Memo1.Lines.Add(E.Message);
end;
end;

2. Select
try
MSTransaction2.DefaultConnection := MSConnection2;
MSTransaction2.IsolationLevel := ilReadUnCommitted;

MSTransaction2.StartTransaction;
MSQuery2.Close;
MSQuery2.SQL.Clear;
MSQuery2.SQL.Text := SQLText2;
MSQuery2.Open;
Showmessage(MSQuery2.Fields[0].AsString); ==> AAA is displayed in the message box
MSTransaction2.Commit;
except on E:EUniError do begin
Memo1.Lines.Add(IntToStr(E.ErrorCode));
Memo1.Lines.Add(E.Message);
end;
end;

ex) PostgreSQL or MariaDB
1. Insert
try
PostConnection.StartTransaction;
PostQuery.Close;
PostQuery.SQL.Clear;
PostQuery.SQL.Text := SQLText;
PostQuery.ExecSQL;
// PostConnection.Commit;
except on E:EUniError do begin
PostConnection.Rollback;
Memo1.Lines.Add(IntToStr(E.ErrorCode));
Memo1.Lines.Add(E.Message);
end;
end;

2. Select
try
PostQuery2.Close;
PostQuery2.SQL.Clear;
PostQuery2.SQL.Text := SQLText2;
PostQuery2.Open;
Showmessage(PostQuery2.Fields[0].AsString); ==> Spaces are displayed in the message box
except on E:EUniError do begin
Memo1.Lines.Add(IntToStr(E.ErrorCode));
Memo1.Lines.Add(E.Message);
end;
end;

[Stellar]

You can skip starting a transaction with a dirty read when retrieving data from a table. Note that is this case the data retrieval operation will be blocked by the transaction that adds records to the same table until all added records have been committed on the server.