Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sequence generator

 
Iris Hoekstra
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We're writing an application that needs to be able to insert records into a database with a unique id.

The DBMS we have to use is ancient and doesn't support auto-increment or any such nifty features. So far, we query the database for the maximum value, add 1 to that and insert the new record with the new number. This approach has to change, however, because theoretically two users could get the same max value and then we're in trouble. I should also add that the application will run on two servers simultaneously in the future.

I have been reading something about sequence generators but I am not sure I get the idea. I would rather not write a separate sequence generator (i.e. one that is not part of the application). From what I understand, I don't have to do that if I create a database table that holds... some value. That's where I get lost. Could someone provide some pointers?
 
Srikanth Basa
Ranch Hand
Posts: 241
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This may give some idea for a potential implementation
-----------------------------------------------
TBL: MY_SEQ_TABLE
-----------------------------------------------
Columns : SEQUENCE_NAME , SEQUENCE_VALUE
-----------------------------------------------
Initial Val: DUMMY_SEQUENCE , 0
-----------------------------------------------

Your sequence implementation will be something like this

1. currentSequenceValue = Select SEQUENCE_VALUE from MY_SEQ_TABLE where SEQUENCE_NAME = 'DUMMY_SEQUENCE' FOR UPDATE (Note the "for update" option)

2. Update MY_SEQ_TABLE set SEQUENCE_VALUE = SEQUENCE_VALUE + increment WHERE SEQUENCE_NAME = 'DUMMY_SEQUENCE'

3. newSequenceValue = currentSequenceValue + increment

4. Commit
-----------------------------------------------
Note : You may have to consider many other performance related aspects while implementing it.
 
Iris Hoekstra
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, but unfortunately 'FOR UPDATE' is not supported either.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Iris,
What database are you using? Some database provide built in sequences or something else that may be of use to you.

"for update" is used an Oracle to show you need a write lock. In some other databases - like db2 - you get a write lock automatically. In those databases, you have to say "for fetch only" to get a read only lock.
 
Iris Hoekstra
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jeanne,

We're using CA Datacom. We are approaching the database through FireXML, a piece of middleware which enables you to speak SQL to Datacom.

But I'll tell you what: 'for fetch only' seems to be supported! That brings the solution Srikanth offered back into the realm of possibilities. Thanks, both of you!
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Iris,
Just to clarify, you don't actually want to use "for fetch only" as that would make the query read only. It's absence is equivalent to "for update."
 
Tracy Nelson
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Iris Hoekstra:
We're writing an application that needs to be able to insert records into a database with a unique id.


Is the only restriction that the ID must be unique? If so, then you can probably just use Date.getTime() to get the time in milliseconds, then prepend the user ID or some other per-user bit of data.

If the IDs have to be a monotonically increasing series, then you could write a simple sequence generator class that takes a value in its constructor and has a thread-safe getNextId() method that just increments the value and passes back the new value. Initialize it with the value returned from "SELECT MAX(id) FROM <whatever table you're using>". This only has to be done at application startup, so even if it takes a couple of seconds to get the value, it shouldn't be too bad.

If you have to have gapless sequences (e.g., check or invoice numbers), then your safest bet is to use the database sequence table already discussed. If possible, use a stored procedure to control access to the table. This way, you can prevent anyone from modifying the table directly, so nobody introduces gaps or overlaps.

For your two-database situation, I'd recommend using a stateful EJB to hand out new IDs, unless you need gapless numbers. Then I'd recommend you keep the sequence table on only one database, and make all transactions hit that database.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic