Unique Sequential Key


Author
Message
Ger Cannoll
Ger Cannoll
Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)
Group: StrataFrame Users
Posts: 430, Visits: 507
I have a table which has a number of Sequential Unique fields (e.g. Invoice Number, Despatch Number, Order Numer etc) whcih all need to be just one higher than the previous one, but can all be different. My first idea was to use Identity keys, but Sql Server does not suport more than one 'Identity' keys in the one table.

 I've written a method whcih updates a 'Uniques' table which just has 3 fields: PK,UniqueName,NextKey. I then use this to give me a unique key, depending on the UniqueName...all working fine. I dish out these by using a SFrame BO and code is as follows (Pseudo Code):

Select out from the table based on the UniqueName

If BO.Count = 0
{ Add a new record to BO and set Key to 1}
Else
{   Increase Existing Key by 1}
BO.Save

Return Thekey



Above working fine... but I am concerned that if there are say 60 different users getting a New Number, there is the possibility that 2 users may get the same number. I know I can put a Unique Index on the Field... this will give an error for duplicates but was looking for a more Fail-Safe way.

Is there any 'sure' way of preventing this, or another more secure way of dishing out numbers  (want to keep all numbers in the one table)
Replies
Michel Levy
Michel Levy
Advanced StrataFrame User (607 reputation)Advanced StrataFrame User (607 reputation)Advanced StrataFrame User (607 reputation)Advanced StrataFrame User (607 reputation)Advanced StrataFrame User (607 reputation)Advanced StrataFrame User (607 reputation)Advanced StrataFrame User (607 reputation)Advanced StrataFrame User (607 reputation)Advanced StrataFrame User (607 reputation)
Group: StrataFrame Users
Posts: 193, Visits: 9K
Hi Gerard,

Ivan talks about "repeatable read" level for the transaction.

be aware that if you begin a serialized transaction, it is as a FLOCK in VFP, nobody may read any row of any table involved in the transaction!
Starting such a transaction before the fill may cause some unexpected waiting for other users. I use serialized transaction only for the writing processes (i.e. update or insert) on a very complex BOM.
Ger Cannoll
Ger Cannoll
Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)Advanced StrataFrame User (866 reputation)
Group: StrataFrame Users
Posts: 430, Visits: 507
Hi Michel. Many thanks for replying.

I had noticed Ivans 'Repeatbale read' setting, but on investigation, it appears that you can get Phantom reads with this, and the only difference between 'RepatableRead' and 'Serializable' seems to be that 'Inserts' are also locked out (whereas they are not for RepeatableRead)

This is a very specific instance where I intend to use Serializable, for dishing out Sequential Numbers, where there is a slight chance that there may be an insert (if the table requiring the Key is starting off) . I plan to use and Lock ONE TABLE ONLY. As you mentioned re FLOCK, this is exactly how i did it in VFP and it always seemd to work well. Also this Table that will be used, will not be used for any other purpose.

I realise that for other tables, 'Serializable' may not be the way to go.

Are there any other 'Gotchas' that perhaps I cannot see ?
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...





Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search