Page 1 of 1
Data loss in pessimistic lock mode
Posted: Fri 08 Jun 2012 08:18
by 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 ?
Re: Data loss in pessimistic lock mode
Posted: Mon 11 Jun 2012 14:17
by 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;
Re: Data loss in pessimistic lock mode
Posted: Tue 12 Jun 2012 08:37
by 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.
Re: Data loss in pessimistic lock mode
Posted: Wed 13 Jun 2012 13:18
by AndreyZ
We will try to fix this problem in one of the following MyDAC builds.
Re: Data loss in pessimistic lock mode
Posted: Wed 10 Oct 2012 10:11
by AndreyZ
We have fixed this problem. This fix will be included in the next MyDAC build.