Philip, see below.
>Hi.
>We have a number of legacy aplications here which are written in fox for dos. Many of the tables used in the applications contain unique keys. We often get problems caused by duplication of unique keys and I can't work out why or how to stop it happening.
>For each table in the system the value for the last unique key generated is stored in a table called nextref.
>When a new record is added to a table the nextref table is locked. The appropriate value is read from nextref and stored in the table being appended as the unique identifier for the new record. The value for that identifier is incremented in the nextref table before the lock is released.
When a table is written to (new record), various scenarios exist that won't let the new record show at the other users immediately. This depends on a. the writing PC and b. the reading PC. Obviously the writing PC is first, and generally the record has to be flushed to the network rather explicitly (I'm not talking FLUSH here). It can be done by RLock() of the record - Replace etc., followed by Unlock. Note that implicitly it will arrive at the network's disk as well, but not really at the time you wanted (as soon as possible). (This is all vague btw).
I assume (!) that when the write operation is subject to an FLock(), you are rather out of control (never tried that, never used FLock()).
See more below for better understanding.
>We have also inserted a function which checks that the identifier does not already exist in the table before storing it to the new record.
Take this as just not working. Referring to the NextRef table as well, you will not be able to see new records only after the Set Refresh timer has elapsed. This applies to the reading PC (but note : in VFP7 this can be set to a value implying "immediate"; earlier versions (I think) can't do this).
Another means to obtain the record, now explicitly, is RLock(). It will (re)fetch the record from the server. However, how to do that with records which don't exist yet ? But, it can be done by some manipulation, knowing that the header of the table always will have been updated with the Reccount();
Now there's another problem, because "just" asking for Reccount() won't get you the most current status of it. An append sure does, but that's overdone for checking only. A Go Bottom will do the trick; now Reccount() is accurate, though you won't need it anymore. You'll be needing the last record. And you have it.
Now things become tricky, because at that moment, again another record can be added by the other user and you won't know that, unless you check again. And again ... and again. It doesn't suffice.
In the end, this is what you should do :
Just perform the Append after one initial check as described above. Apply the key, unlock the record, and then check once more. A Skip -1 will suffice (your Index active). If it's there already, another user was ahead of you. Delete the record, and restart the process starting from the NexRef table.
And to be sure : apply this to the NextRef table just as well (no matter whether you FLocked the table).
In order to check a bit upon my sayings above, just have a small loop with appending records (no matter indexes for now), and have another loop in another task (or PC) showing the last record(number). Do this normally, and you will see that all shows up at the interval of Refresh. With Set Refreh To 1,1 you'll be seeing the last records as recno 17, 80, 150, 215 etc. (similar). This shows your problem. Now apply some Go Bottom and Rlock (and unlock) in the checking loop, and you will see the records appear smoothly.
You will still see that recordno's will be missing, but that's because the two tasks are not synchronized. Anyway you should perform this small test, because it will exactly explain to you how to tackle your situation in a fool proof manner (there is too much too explain on this).
HTH,
Peter
>Reprocess is set to automatic.
>
>I think that the duplications are probably caused by network errors but I don't know how that would happen exactly.
>
>
>Phil.
