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,

Yes agree, this could get ugly having many users generating numbers.  Look in Google on the best approach to get this with MS-SQL.

I don't have that heavy users loading, so I simply create a method that is call in the BO.BeforeSave that will get the next available number using this query:

SELECT MAX( referenceno ) + 1 FROM dbo.[transaction]

I know it is not guarantee to exclude duplicate, but so far, it has been working fine with at lest 5 users, or at least they have not reported any problems.

Edhy Rijo

Hi Edhy. Thanks for reply.

I have the code in the Beforesave also . I was hoping that there might have been something in the framework to guard against this sort of thing, else I presume its down to putting in some Record / File  Locking Code (I did this in VFP)

I wonder if a Stored procedure would help. They are not something I use very often, but am wondering is there any 'default' locking mechanism from when I start a Stored Procedure to when it finishes.  if there was, then I would change this code to a Stored Procedure.

Well, here is my actual code which does a loop in case the number is not unique:

''' <summary>    
''' Handle the creation of the ReferenceNo.    
''' </summary>    
''' <param name="e"></param>    
''' <remarks></remarks>    
Private Sub bizTransaction_BeforeSave(ByVal e As MicroFour.StrataFrame.Data.BeforeSaveUndoEventArgs) Handles Me.BeforeSave
        If Me.Count <= 0 Then Exit Sub         
   If Me.ReferenceNo <= 0 Then
     Dim tempReferenceNo As Integer = Me.GetNextAvailableFieldValue(bizTransaction.bizTransactionFieldNames.ReferenceNo.ToString) 
       Do While True   
       If Me.ValidateNumberIsNotUnique(bizTransaction.bizTransactionFieldNames.ReferenceNo.ToString, tempReferenceNo) Then  
                  tempReferenceNo = Me.GetNextAvailableFieldValue(bizTransaction.bizTransactionFieldNames.ReferenceNo.ToString)  
       Else         
          Exit Do        
       End If     
       Loop      
       Me.ReferenceNo = tempReferenceNo     
   End If 
End Sub

The ValidateNumberIsNotUnique does a select like this:

SELECT COUNT(*) FROM pTableNameAndSchema WHERE UniqueFieldName = UniqueNumberValue AND PrimaryFieldName <> pkFieldValue

Again, this is just an idea I came up long time ago when having your same need.  I know SQL Server has some locking mechanism but I have not investigate that path, if you come out with another way to get this done, please post it here.

Edhy Rijo

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 ?

Hi Edhy. Below is my Code for Generating a Unique Key. I decided to go down the route of Locking , and with the limitedted testing i have done so far, it seems to hang together ok. Basically, I have just one table with 3 fields which will hold and maitain all Sequential Numbers:

Uni_PK   Uni_Name     Uni_PrevKey

The Uni_name field is used as a Key to Hold each Seq No. that I need, e.g. InvocieNUmber, DespatchNumber, QuoteNumber etc

I then pass the 'KeyName' as a parameter into the Method. if there is already a key there, it adds one to it, otherwise it adds a new Record and sets it to 1.

As this is my first foray into Locking in SqlServer, there may be somethings that I am missing..

 

public static int GenerateUniqueKey(string myName)
{
MicroFour.StrataFrame.Business.BusinessLayer.TransactionBegin("", IsolationLevel.Serializable);
UniBO bo = new UniBO();
bo.UniFillByTable(myName);
if (bo.Count == 0)
{
    bo.Add();
    bo.UNI_NAME = myName;
    bo.UNI_PREVKEY = 1;
}
else
{
    bo.UNI_PREVKEY = bo.UNI_PREVKEY + 1;
}
bo.Save(true);
MicroFour.StrataFrame.Business.BusinessLayer.TransactionCommit("");
return bo.UNI_PREVKEY} // GenerateUniqueKey (Cannot have more than one Identity Key in SqlServer

Hi Gerard,
Thanks for the code, appreciated!!!

Edhy Rijo