Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Using a control table for integer keys
Fri, Nov 14 2008 11:29 PMPermanent Link

Tony Pomfrett
Hi,

I am using a Control table to issue integer keys for various tables in my client/server application. This is to avoid using autoinc (for historical
reasons that I can't recall).

This involves locking the control table, obtaining the integer key, incrementing the key and then unlocking the control table ready for the next
access. The Control table has one record of around 30 fields each representing the next key for a particular table.

The plan worked well initially. But with more users accessing the database over the Internet we are experiencing slower operation due to less
available bandwidth per user. The effect is that some users are unable to lock the control table within their allotted number of attempts and this
occurrence is not handled properly - basically, their app crashes. Secondly, some users are managing to retrieve keys that are null valued  -
probably a coding error allowing this to slip through. The first time it happens a user obtains and uses a null key. The second user to obtain a null
key gets a duplicate key error and his app crashes.

So what I need is a bullet proof routine for retrieving keys from a control table and updating the next key value. I also need to make the app
tolerant to the situation where a client app cannot lock the control table due to heavy usage. i.e. ask the user to wait and try again.

Does anybody have such a routine or can anybody point me in the right direction?
Sat, Nov 15 2008 3:29 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tony


If you have to continue with this rather than switch to AUTOINCs the first thing I'd do is use a record rather than a field paradigm ie one record for each integer you want to manage.

I don't know what the spread of required new numbers is but any time anyone wants a new number for any one of those 30 fields you have to lock the record. That's increasing the odds of timeouts. With 30 records you have a minor performance hit as they have to do a FindKey each time but you're reducing contention.

This is a function I used to use for managing record locks

function LockTheRecord(WhichTable: TDBISAMTable; AbortAllowed: boolean): boolean;
var
ExitControl: boolean;
TempMsg: string;
WhichButtons: set of TMsgDlgBtn;
begin
ExitControl := True;
Result := True;
if AbortAllowed then WhichButtons := [mbOK, mbIgnore] else WhichButtons := [mbOK];
while ExitControl do begin
 try
  WhichTable.Edit;
  ExitControl := False;
 except
  on E: EDataBaseError do begin
   TempMsg := 'Unable to lock ..' + #13#10;
   TempMsg := TempMsg + #13 + E.Message;
   TempMsg := TempMsg + #13 + 'Try again';
   if AbortAllowed then TempMsg := TempMsg + ' or ignore';
   TDBISAMTable(WhichTable).FlushBuffers;
   WhichTable.Refresh;
   if MessageDlg(TempMsg, mtConfirmation, WhichButtons, 0) = mrIgnore then begin
    ExitControl := False;
    Result := False;
   end;
  end;
 end;
end;
end;

Naturally you'll have to manage program flow around it.

Roy Lambert [Team Elevate]
Mon, Nov 17 2008 3:53 PMPermanent Link

"John Easley"
> I am using a Control table to issue integer keys for various tables in my
> client/server application. This is to avoid using autoinc (for historical
> reasons that I can't recall).

Have you thought about using GUIDs as keys?

John

Mon, Nov 17 2008 5:26 PMPermanent Link

"Robert"

"Tony Pomfrett" <tonyp@aline.com.au> wrote in message
news:6A0B301F-43CB-466E-9FA8-32A1A3943F75@news.elevatesoft.com...
>
> Does anybody have such a routine or can anybody point me in the right
> direction?

Make sure you do a refresh before you edit, otherwise you could be assigning
a duplicate key. Then trap the timeout exception, and simply try again.

Robert
>

Mon, Nov 17 2008 9:24 PMPermanent Link

"Jeff Cook"
Robert wrote:

>
> "Tony Pomfrett" <tonyp@aline.com.au> wrote in message
> news:6A0B301F-43CB-466E-9FA8-32A1A3943F75@news.elevatesoft.com...
> >
> > Does anybody have such a routine or can anybody point me in the
> > right  direction?
>
> Make sure you do a refresh before you edit, otherwise you could be
> assigning a duplicate key. Then trap the timeout exception, and
> simply try again.
>
> Robert
> >

Like this ...

procedure TapmDM.GetNewKey(Tab: TDBISAMTable; 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;
begin
 Waited := False;
 OpenTable(LastKeyTable);
 with LastKeyTable do
 begin
   Refresh;
   if FindKey([Tab.Name]) then
   begin
     repeat
       try
         Edit;
         Break;
       except
         on E: Exception do
         begin
           if (E is EDBISAMEngineError) then
           begin
             if (EDBISAMEngineError(E).ErrorCode =
DBISAM_RECLOCKFAILED)
               or (EDBISAMEngineError(E).ErrorCode = DBISAM_LOCKED)
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 (EDBISAMEngineError(E).ErrorCode =
DBISAM_KEYORRECDELETED) 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.Value := Tab.Name;
     NewKey := Min;
   end;
   apmDM.LastKeyTableLastValueUsed.Value := NewKey;
   Post;
 end;
end;

.... mainly copied out of these newsgroups Wink

Obviously tailored for my own needs, but appears bulletproof for years
now (famous last words!).

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com
Tue, Nov 18 2008 8:08 AMPermanent Link

Tony Pomfrett
Hi,

Thanks for all the suggestions. I'll check my code and see if I can incorporate some of your ideas.
I'll post what I come up with, just in case anybody is interested.

Tony.
Image