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



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)

Hi Gerard.

I would deal with your "Uniques" table in a Stored Procedure, inside a Transaction. Have a look at "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ".

Hope it helps.

Hi Ivan. Thanks for your reply an suggestion. I have looked at the SF example and also done some investigation on Isolation Levels. The Exanple in the SF help (in summary form ):

      

So from within the APP, I want to start the transaction prior to the Getting the data, and ensure NO OTHER user can get the data until I have updated it and written it back.  So the only difference between here and the example in the manual is that I am starting the transaction before the Fill

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.

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 ?