Hi

I have a multi-user application that needs data integrity for making a unique transaction number.
The system is running as client server e.g. dbsrvr and client access.
A unique session id is used for each client software run.
I am using optimistic locking for any post of data

So when I load 2 clients at the same time ) (Small time frame between each) I get a record in use message. .

I have written some code below which generates the number and takes care of the error if the table is busy.
I then call the procedure again after flushing the buffers.

1. Is that the right approach ?
2. Is there a better way to do this?

Here is my code:

Procedure GenerateNewTransactionNumber;
Begin
 {generate a new unique Tran Number to be used throughout the system}
 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;
 except
  On E:Exception do
  begin
    showmessage('ERROR ' + E.Message);
    datafrm.tbSystem.FlushBuffers;
    GenerateNewTransactionNumber;
  end;
 end;
end;


kind regards

Kamran

Den 06.12.2014 18:01, skrev kamran:
> Hi
>
> I have a multi-user application that needs data integrity for making a unique transaction number.
> The system is running as client server e.g. dbsrvr and client access.
> A unique session id is used for each client software run.
> I am using optimistic locking for any post of data
>
> So when I load 2 clients at the same time ) (Small time frame between each) I get a record in use message. .
>
> I have written some code below which generates the number and takes care of the error if the table is busy.
> I then call the procedure again after flushing the buffers.
>
> 1. Is that the right approach ?
> 2. Is there a better way to do this?
>
> Here is my code:
>
> Procedure GenerateNewTransactionNumber;
> Begin
>    {generate a new unique Tran Number to be used throughout the system}
>    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;
>    except
>     On E:Exception do
>     begin
>       showmessage('ERROR ' + E.Message);
>       datafrm.tbSystem.FlushBuffers;
>       GenerateNewTransactionNumber;
>     end;
>    end;
> end;
>
>
> kind regards
>
> Kamran
>


I haven't written SQL in dbISAM dialect for some time, but I'll try to
answer:
1.
If there is an atomic operation which will return the "next"
SYSTEM_TRAN_NUMBER, this is a good solution. This is like generators in
InterBase and Firebird:
  select gen_id(SYSTEM_TRAN_NUMBER,1) from rdb$database
-- rdb$database is a table with _excactly_ 1 record
2.
If there is an update (or insert) that will return a value, like
  update MYTABLE
  set SYSTEM_TRAN_NUMBER=SYSTEM_TRAN_NUMBER+1
  where <some condition specifying a specific record>
  return SYSTEM_TRAN_NUMBER
this might work (assuming it is an atomic operation).
Alternatively, have a table with an (additional) auto-increment field:
  insert into MYTABLE
  (some field)
  values
  (0)
returning <value of auto increment field>
3.
Use a GUID (if dbISAM SQL can generate them).
I find them "ugly", but they might be a solution for you.
4.
Use pessimistic locking by doing a "dummy" update.  In a transaction, no
one else should be able to change fields in that record until you commit.

start transaction

update MYTABLE
set SYSTEM_TRAN_NUMBER = SYSTEM_TRAN_NUMBER
where <some condition specifying a specific record>

If this fails, just wait (a fraction of a second) and try again.
If success, you can now increment SYSTEM_TRAN_NUMBER (and read it)
without interference from other users.

commit

Of course, this is a method that all users (programs) will have to use.



--
Aage J.

kamran

>1. Is that the right approach ?

It will work but I used to get occasional problems when I did it that way.

>2. Is there a better way to do this?

Aage has given you some ideas. I'd go for autoinc if that's possible, if not GUID. Neither of those require exception handling.

You could also use a composite of the unique session id and a number, have one entry in your lock number table for each session id - again no clashes.

There are other options, some depend on what you're using the unique transaction id for and wether you're using table methods or sql.

Roy Lambert

On 7/12/2014 6:01 a.m., kamran wrote:> Hi
>
> I have a multi-user application that needs data integrity for making
a unique transaction number.
> The system is running as client server e.g. dbsrvr and client access.
> A unique session id is used for each client software run.
> I am using optimistic locking for any post of data
>

Hi Kamran

The following code has served me well for the last dozen years or so.

It looks like a lot of code but seems to cover all eventualities (famous
last words).

Based on a table that looks like this:-

LastKeyName CHAR(20)  ... primary key and usually the table name.
LastValueUsed INTEGER

procedure TapmDM.GetNewKey(Tab: TEDBTable; Min: Integer; Max: Integer);
{ Get a sequential key - returned in NewKey
  - Min is the start number of the sequence
  - Max is the top of the sequence - then revert to Min }
var
  Waited: boolean;
  sSimpleText: string;
  iColor: Integer;
  sTable: string;
begin
  Waited := False;
  OpenTable(LastKeyTable);
  with LastKeyTable do
  begin
    Refresh;
    sTable := Copy(Tab.Name, 1, 20);
    if FindKey([sTable]) then
    begin
      repeat
        try
          Edit;
          Break;
        except
          on E: Exception do
          begin
            if (E is EEDBError) then
            begin
              if EEDBError(E).ErrorCode = EDB_ERROR_LOCK then
              begin
                Waited := True;
                with mainForm.mainStatusBar do
                begin
                  sSimpleText := SimpleText;
                  iColor := Color;
                  SimplePanel := True;
                  SimpleText := 'Waiting to lock LastKey record for ' +
                    Tab.Name;
                end;
                Application.ProcessMessages;
                Screen.Cursor := crHourGlass;
                Continue;
              end
              else if (EEDBError(E).ErrorCode = EDB_ERROR_ROWMODIFIED) then
              begin
                apmDM.LastKeyTable.Refresh;
                Continue;
              end
              else
              begin
                MessageDlg(E.Message, mtError, [mbOK], 0);
                Break;
              end;
            end
            else
            begin
              MessageDlg(E.Message, mtError, [mbOK], 0);
              Break;
            end;
          end;
        end;
      until False;
      if Waited then
      begin
        mainForm.mainStatusBar.SimpleText := sSimpleText;
        mainForm.mainStatusBar.Color := iColor;
        Application.ProcessMessages;
        Screen.Cursor := crDefault;
      end;
      NewKey := apmDM.LastKeyTableLastValueUsed.Value + 1;
      if NewKey > Max then
        NewKey := Min;
    end
    else
    begin
      Insert;
      apmDM.LastKeyTableKeyName.AsString := sTable;
      NewKey := Min;
    end;
    apmDM.LastKeyTableLastValueUsed.Value := NewKey;
    Post;
  end;
end;
-----------------------------------
Used like this, it allows for the wrapping round of the sequence and
filling the blanks ...

procedure TapmDM.PropertyManagersTableAfterInsert(DataSet: TDataSet);
begin
  GetNewKey(PropertyManagersTable, 1000, 9999);
  while apmDM.QuickSQL('SELECT PMCode FROM PropertyManagers WHERE
PMCode = ' +
    Engine.QuotedSQLStr(IntToStr(NewKey))) <> 0 do
    GetNewKey(PropertyManagersTable, 1000, 9999);
  PropertyManagersTablePMCode.AsString := IntToStr(NewKey);
end;

On 8/12/2014 9:39 a.m., Jeff Cook wrote:
> On 7/12/2014 6:01 a.m., kamran wrote:> Hi
>  >
>  > I have a multi-user application that needs data integrity for making
> a unique transaction number.
>  > The system is running as client server e.g. dbsrvr and client access.
>  > A unique session id is used for each client software run.
>  > I am using optimistic locking for any post of data
>  >
>
> Hi Kamran
>
> The following code has served me well for the last dozen years or so.
>

I released as I pressed the send button that I had pasted the EDB
version of my code and not the DBISAM ... There is very little
difference anyway

Cheers

Jeff

Thank you to everyone  for their suggestions

Kamran

Jeff Cook wrote:

On 8/12/2014 9:39 a.m., Jeff Cook wrote:
> On 7/12/2014 6:01 a.m., kamran wrote:> Hi
>  >
>  > I have a multi-user application that needs data integrity for making
> a unique transaction number.
>  > The system is running as client server e.g. dbsrvr and client access.
>  > A unique session id is used for each client software run.
>  > I am using optimistic locking for any post of data
>  >
>
> Hi Kamran
>
> The following code has served me well for the last dozen years or so.
>

I released as I pressed the send button that I had pasted the EDB
version of my code and not the DBISAM ... There is very little
difference anyway

Cheers

Jeff