Assigning Blocks of Unique IDs in the Island Hopper News Sample

Julie MacAller
Microsoft Corporation

January 1999

Introduction

The Island Hopper News sample is an automated classified ads system created by a fictitious company, Island Hopper Publishing, as a test project to evaluate converting the current paper-based weekly newspaper into an online newspaper. The design team consists of the Island Hopper News editor, the Classified Ads and Accounting department heads, and two developers.

This paper describes how the code for assigning blocks of unique IDs in Island Hopper fits together and how it works.

Assigning Blocks of Unique IDs Overview

Assigning blocks of unique IDs is a challenge for any application that uses such identifiers to track items in a database. In the case of Island Hopper News, customers, ads, invoices, and payments are all assigned a unique ID. The problem is how to ensure such IDs are unique, and how to generate, assign, and store them in a remote database with minimal performance impact.

Island Hopper News solved this problem by creating a separate table in the database to hold unique identifiers, grouped into blocks by item—ad, customer, and so on. This means that customer records are assigned a unique identifier from one block of IDs and ad records are assigned unique identifiers from a different block of IDs. Two middle-tier components—util_TakeANumber and util_TakeANumberUpdate—do the work of getting IDs from the database and updating the database with the next available ID. These components do not encapsulate business logic, nor are they purely data access components. Instead, they provide a service to the Island Hopper News application by assigning unique identification numbers efficiently; hence they are called utility components.

Assigning Unique IDs Components

Assigning and updating unique IDs uses the utility components util_TakeANumber and util_TakeANumberUpdate.

These components expose the following methods:

Component Methods
util_TakeANumber GetANumber
util_TakeANumberUpdate Update

The util_TakeANumber component distributes blocks of ID numbers for inserting into a SQL Server database. It uses the Microsoft® Transaction Server (MTS) Shared Property Manager to ensure that multiple clients in a distributed environment do not overwrite ID numbers.

The MTS Shared Property Manager is a resource dispenser—that is, a service that provides synchronization and management of temporary resources, such as variable values, within a process, providing for simple and efficient sharing by MTS objects. In a non-distributed environment, you can use global variables to hold information you need to share between multiple objects; the Shared Property Manager does the same thing in a distributed environment.

The util_TakeANumberUpdate component simply updates the database with the next available number.

Assigning Unique IDs Process

The utility component TakeANumber is called by the following Island Hopper business components:

Here's what happens when a business component requests a unique identifier:

  1. The business component calls the MTS CreateInstance method on the MTS ObjectContext object to create an instance of the util_TakeANumber component and then calls the GetANumber method of util_TakeANumber.

    For example, the bus_CustomerC object does it as shown in the following code:

    Set objTakeANumber = _
GetObjectContext.CreateInstance("util_TakeANumber.TakeANumber")
lngCustomerID = objTakeANumber.GetANumber("CustomerID")
  2. The GetANumber method of util_TakeANumber calls the CreatePropertyGroup method on the SharedPropertyGroupManager object, passing in a name for the group in the strPropGroupIn parameter. 
    Set spmGroup = spmMgr.CreatePropertyGroup(strPropGroupIn, _
               LockSetGet, Process, bResult)

    The CreatePropertyGroup method either creates a new shared property group, such as "ads" or "customers" (if none with the specified name exist), or returns a reference to an existing group (if a match for the name is found).

    LockSetGet is a constant that specifies the isolation mode for the shared property group. LockSetGet locks a property during calls to get or set the property, which ensures that two clients can't read or write to the same property at the same time. However, it doesn't prevent other clients from concurrently accessing other properties in the same group.

    Note   Choosing LockMethod for this value is probably safer. The LockMethod constant locks all of the properties in the shared property group for exclusive use by the caller as long as the caller's current method is executing. This is the appropriate mode to use when there are interdependencies among properties, or in cases where a client may have to update a property immediately after reading it before it can be accessed again.

    Choosing LockSetGet for this value creates a potential problem with code like the following:

    spmPropNextNum.Value = spmPropNextNum.Value + 1

    because there is nothing to prevent this sequence of events from happening:

    TakeANumber object O1 created in apartment 1

    TakeANumber object O2 created in apartment 2

    ...

    Object O1 grabs the lock, gets spmPropNextNum.Value (V1), and releases the lock

    Object O2 grabs the lock, gets spmPropNextNum.Value (V1), and releases the lock

    Object O1 grabs the lock, adds one, and sets spmPropNextNum.Value (V1+1), then releases the lock

    ...

    Process is a constant that specifies the release mode for the shared property group. Process specifies that the property group is not destroyed until the process in which it was created has terminated.

  3. The GetANumber method calls the CreateProperty method on the SharedPropertyGroup object to create two new properties (MaxNumber and NextNumber). 
    Set spmPropMaxNum = spmGroup.CreateProperty("MaxNumber", bResult)
Set spmPropNextNum = spmGroup.CreateProperty("NextNumber", bResult)

    If the properties already exist, the current values for them are returned; if they don't exist, they are created and set to an initial value of zero.

    The MaxNumber property represents the last number in a block of ID numbers; the NextNumber property represents the next available number in a block of ID numbers.

  4. The GetANumber method sets the value of the NextNumber property to one if the property did not exist and was just created. 
    If Not bResult Then
    spmPropNextNum.Value = 1
End If
  5. The GetANumber method compares the current values of the MaxNumber property and the NextNumber property. If the values of NextNumber and MaxNumber are the same or if the value of NextNumber is greater than MaxNumber, it is time to start a new block of ID numbers. The GetANumber method does this by calling the MTS CreateInstance method on the MTS ObjectContext object to create an instance of the utility component, util_TakeANumberUpdate, then calls the Update method of util_TakeANumberUpdate, passing in the increment value and the name of the shared property group. 
    If spmPropNextNum.Value >= spmPropMaxNum.Value Then
    Set objTakeUpdate = _
GetObjectContext.CreateInstance("util_TakeANumber.TakeANumberUpdate")
    spmPropNextNum.Value = objTakeUpdate.Update(incQty, _
strPropGroupIn)
  6. The Update method of the util_TakeANumberUpdate object builds a SQL command to retrieve the next available ID number and executes that command to the database using the Recordset.Open method. 
    strSQL = "Select NextNumber from TakeANumber where _
         PropertyGroupName = '" & strPropGroup & "'"
rs.Open strSQL, "FILEDSN=" & fileDSN, adOpenKeyset, _
adLockBatchOptimistic, -1

    The fileDSN variable points to a DSN file that contains the data source, user ID, and password needed for accessing the database. Using a DSN file means that system administrators can modify the data source or account access information as necessary without requiring any source code changes.

    The adOpenKeyset property specifies a keyset cursor. With a keyset cursor, you can see data changes made by other users. However, you can't see records that other users add, and you can't access records that other users delete.

    The adLockBatchOptimistic property means that the entire recordset is locked when you call the MTS UpdateBatch method on the recordset. The underlying database table isn't locked until you call the MTS UpdateBatch method. Without the adLockBatchOptimistic property, the table would be locked from the time you opened the recordset, which could impact performance.

    The -1 indicates that the type of command is unspecified. In this case, performance would improve if the constant adCmdText were passed instead, because adCmdText notifies ADO which specifies that the underlying data provider should evaluate CommandText as a textual definition of a command.

  7. The Update method of the util_TakeANumberUpdate object returns the next available number, closes the recordset, and updates the TakeANumber table with that number using the Connection.Execute method. 
    lngNextNumber = rs!NextNumber
rs.Close

strSQL = "UPDATE TakeANumber SET NextNumber = " & (lngNextNumber + _
lngInc) & " WHERE PropertyGroupName = '" & strPropGroup & "'" _
conn.Open "FILEDSN=" & fileDSN
conn.Execute strSQL
  8. The Update method of the util_TakeANumberUpdate object returns the next available number to the GetANumber method of the util_TakeANumber object and calls the MTS SetComplete method on the MTS ObjectContext object if there were no errors. 
    Update = lngNextNumber

GetObjectContext.SetComplete
  9. The GetANumber method of the util_TakeANumber object sets a new value for the MaxNumber property, increases the NextNumber property by one, and calls the MTS SetComplete method on the MTS ObjectContext object if there were no errors, returning the correct ID number to the calling object. 
    spmPropMaxNum.Value = spmPropNextNum.Value + incQty

spmPropNextNum.Value = spmPropNextNum.Value + 1

GetObjectContext.SetComplete

For More Information