Data loss in pessimistic lock mode

[zeltron73]

Hello,

When I execute this code :

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  Query1.LockMode := lmPessimistic;
  Query2.LockMode := lmPessimistic;
  Query1.Open;
  Query2.Open;

  Query1.Edit;
  Query2.Insert;
  Query2.FieldByName('Field1').AsString := 'Value1';
  Query2.Post;
  Query1.Post;  // no data is modified on that table
  // after this line, the record inserted by Query2 is lost (transaction rollback ?)
end;

After the second post, no data will be written as if the transaction was rolled back. Is that a normal behaviour ?

[AndreyZ]

Hello,

It is the correct behaviour. When you start editing the first dataset, MyDAC starts a transaction on a server (that works for a session, i.e. TMyConnection). When you post a new inserted record in the second dataset, it is done in the transaction context of a session. Because LockMode is used only for edited records (because several users can change them simultaneously), the transaction is not committed at the post operation of the second dataset. When you post changes made in the first dataset, MyDAC determines that there were no changes and rollbacks the transaction.
To avoid this problem, you should use two connections for inserting and editing records. Here is a code example:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  Query1.Connection := MyConnection1;
  Query2.Connection := MyConnection2;
  Query1.LockMode := lmPessimistic;
  Query2.LockMode := lmPessimistic;
  Query1.Open;
  Query2.Open;

  Query1.Edit;
  Query2.Insert;
  Query2.FieldByName('Field1').AsString := 'Value1';
  Query2.Post;
  Query1.Post;
end;

[zeltron73]

Hello,

In my opinion that behaviour could be logical if I had executed a "Query1.Cancel". It is not so obvious to rollback the transaction when you issue a "Post" command with no data changed; the transaction could be COMMITED even if no "UPDATE" command have been sent to the server... And I think this could be noted as a remark in the documentation of the TMyQuery.LockMode method.

Regards.

[AndreyZ]

We will try to fix this problem in one of the following MyDAC builds.

[AndreyZ]

We have fixed this problem. This fix will be included in the next MyDAC build.