Hi

A mult-user c/s setup with (win xp(server) , win 7(wkstn 1), win 8(wkstn 2), dbisam c/s 4.42 b1

The System Trans Counter does not update correctly.

e.g.

wkstn 1 gets trans no 199

wkstn 2 gets trans no 200

wkstn 1 finishes trans no 199

wkstn 1 starts new trans - but it gets trans 200 BUT IT SHOULD BE trans 201

Here is my code:

Procedure GenerateNewTransactionNumber;
Begin
 {generate a new unique Tran Number to be used throughout the system}
 //SHOWMESSAGE('NEW TRANSACTION NUMBER - SAVING');
 DataFrm.PCSDataBase.StartTransaction;
 try
 MainFrm.TranNumber:='';
 datafrm.tbSystem.First;
 MainFrm.TranNumber:=IntToStr(StrToInt(datafrm.tbSystem.FieldByName('SYSTEM_TRAN_NUMBER_ONE').AsString)+1);
 MainFrm.TranNumber:=AddChar('0', MainFrm.TranNumber,6);
 datafrm.tbSystem.Edit;
 datafrm.tbSystem.FieldByName('SYSTEM_TRAN_NUMBER_ONE').AsString:= MainFrm.TranNumber;
 datafrm.tbSystem.Post;
 DataFrm.PCSDataBase.Commit;
 except
  On E:Exception do
  begin
    showmessage('ERROR ' + E.Message);
    DataFrm.PCSDataBase.Rollback;
  end;
 end;
end;

what am I missing something here ?

regards

Kamran

Check the line added, this will solve your problem

<<
Procedure GenerateNewTransactionNumber;
Begin
 {generate a new unique Tran Number to be used throughout the system}
 //SHOWMESSAGE('NEW TRANSACTION NUMBER - SAVING');
 DataFrm.PCSDataBase.StartTransaction;
 try
 MainFrm.TranNumber:='';
 datafrm.tbSystem.First;

 datafrm.tbSystem.Refresh;  // <-------------------ADD THIS LINE
 MainFrm.TranNumber:=IntToStr(StrToInt(datafrm.tbSystem.FieldByName('SYSTEM_TRAN_NUMBER_ONE').AsString)+1);
 MainFrm.TranNumber:=AddChar('0', MainFrm.TranNumber,6);
 datafrm.tbSystem.Edit;
 datafrm.tbSystem.FieldByName('SYSTEM_TRAN_NUMBER_ONE').AsString:= MainFrm.TranNumber;
 datafrm.tbSystem.Post;
 DataFrm.PCSDataBase.Commit;
 except
  On E:Exception do
  begin
    showmessage('ERROR ' + E.Message);
    DataFrm.PCSDataBase.Rollback;
  end;
 end;
end;

what am I missing something here ?

regards

Kamran
>>

Eduardo

<< Here is my code: >>

You must *always* use a pessimistic (the default) record lock to grab/increment a system counter that is stored in a record in a table.  Transactions do *not* provide record locks, only database/table locks.

Use this code instead:

Procedure GenerateNewTransactionNumber;
Begin
 {generate a new unique Tran Number to be used throughout the system}
 //SHOWMESSAGE('NEW TRANSACTION NUMBER - SAVING');
 DataFrm.PCSDataBase.StartTransaction;
 try
 MainFrm.TranNumber:='';
 datafrm.tbSystem.First;
 datafrm.tbSystem.Edit;  <<<<<<<<<<<<<<< Must move this here !!!!!!!
 MainFrm.TranNumber:=IntToStr(StrToInt(datafrm.tbSystem.FieldByName('SYSTEM_TRAN_NUMBER_ONE').AsString)+1);
 MainFrm.TranNumber:=AddChar('0', MainFrm.TranNumber,6);
 datafrm.tbSystem.FieldByName('SYSTEM_TRAN_NUMBER_ONE').AsString:= MainFrm.TranNumber;
 datafrm.tbSystem.Post;
 DataFrm.PCSDataBase.Commit;
 except
  On E:Exception do
  begin
    showmessage('ERROR ' + E.Message);
    DataFrm.PCSDataBase.Rollback;
  end;
 end;
end;

Covering the read/increment with a pessimistic record lock is the *only* way to ensure that you get a clean read on the existing value in the desired field.

Also, you actually don't need a transaction for any of this, so you can get rid of the transaction.  Any time that you're only updating a single record, you don't need transactions.

Tim Young
Elevate Software
www.elevatesoft.com