Concurrent update

[duzenko]

Hi,

I can't get concurrent updates working
I created a new test program that only has a ZConnection component with Transaction Isolation set to read commited, and a Timer. The timer code is this:

Code: Select all

procedure TForm2.Timer1Timer(Sender: TObject);
begin
  Timer1.Enabled := false;
  ZConnection1.AutoCommit := false;
  with TZQuery.Create(nil) do try
    SQL.Text := 'update nextrans set pizza=pizza+1 ';
    Connection := ZConnection1;
    ExecSQL;
  finally
    Free;
  end;
  Timer1.Enabled := True;
  ZConnection1.AutoCommit := true;
end;

It runs very well, but when I start a second instance of the program, the second one immediately gives this error:
SQL Error: lock conflict on no wait transaction deadlock update conflicts with concurrent update concurrent transaction number is 5759. Error Code: -901. Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements.
Just in case here's my ZConnection dfm:

Code: Select all

  object ZConnection1: TZConnection
    ControlsCodePage = cGET_ACP
    AutoEncodeStrings = False
    Properties.Strings = (
      'no_record_version'
      'controls_cp=GET_ACP')
    TransactIsolationLevel = tiReadCommitted
    Connected = True
    Port = 0
    Database = 'POS'
    User = 'sysdba'
    Password = 'masterkey'
    Protocol = 'firebird-2.5'
    Left = 440
    Top = 128
  end

What am I missing here?

[marsupilami]

Hello Anton,

as a side note: did you put the no_record_version parameter into the TZConnection object on purpose? Because to me it seems that this parameter has no effect and the thing it probably would do is the default already for Read Committed transactions. (see http://firebird.1100200.n4.nabble.com/N ... 40606.html).

I assume that in this case this has to do with the run time of the transactions. You might want to try something like this:

Code: Select all

procedure TForm2.Timer1Timer(Sender: TObject);
begin
  Timer1.Enabled := false;
  with TZQuery.Create(nil) do try
    SQL.Text := 'update nextrans set pizza=pizza+1 returning pizza';
    Connection := ZConnection1;
    // these two lines commit the current transaction and start a new one.
    Connection.StartTransaction;
    Connection.Commit;
    // now use the new transaction for doing the update and then finish it off immediately afterwards
    Connection.StartTransaction;
    try
      Open;
      Connection.Commit;      
    except
      Connection.Rollback;
      raise;
    end;
  finally
    Free;
  end;
  Timer1.Enabled := True;
end;

I took your code from the Ticket that you created. You have to use StartTransaction to tell Zeos that you want to take control of the current transaction and don't want to have automatic control. Then finish of the last transaction because you never know what has been done in it. Then Take Control again - do your job and commit it immediately.
The problem here seems to be that Zeos doesn't seem to recognize when to automatically commit a transaction - for efficiency it is not done after each and every statement...
Honestly I didn't test the code above. I didn't have the time to do so. Could you please try it?

But then - I wonder why you don't use Sequences (Generators in Firebird terms)?

With best regards,

Jan

[duzenko]

Perfect! This code works like a charm

Code: Select all

procedure TForm2.Timer1Timer(Sender: TObject);
begin
  Timer1.Enabled := false;
  with TZQuery.Create(nil) do try
    ZConnection1.StartTransaction;
    SQL.Text := 'update nextrans set pizza=pizza+1 returning pizza';
    Connection := ZConnection1;
    Open;
    Caption := Fields[0].AsString;
    Connection.Commit;
  finally
    Free;
  end;
  Timer1.Enabled := True;
end;