• Post Reply Bookmark Topic Watch Topic
  • New Topic

Sequence Generation with Multi database support  RSS feed

 
Sivakumar Nachimuthu
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
All,
Our J2ee application supports Oracle,MySql,MSSQL and HSSQL. We have issue in sequence generator to generate unique seq id for the all the tables , which should support all the above databases.

What is the best way to have Sequence generation to support all databases.

Thanks in advance
Siva
 
Valentin Tanase
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sivakumar,

I guess it depends upon the container you�re using. WebLogic for example provides you several ways of generation sequence ids. Some of them are database specific, like using sequences with Oracle or the identity column technique with SQL Server (they are both database specific solution). Of course they are not portable but they provide the best performances and they should be preferred when using either one or the other. Besides that WebLogic provides a database neutral approach using named sequence tables. As the name implies the container needs a table to maintain the sequence id. You only need to create a table that has a single column (named SEQUENCE) and specify it within the weblogic-cmp-rdbms-jar.xml file.



This is not the most optimal solution but it should work with any relational database.
If you�re using WebLogic, then you should seek this solution. Otherwise you should better check your container�s documentation for similar implementations. Finally if you still are not able to find a solution, let me know and we shall see from there.
Regards.
 
Sivakumar Nachimuthu
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Our application is running in jboss app server. Now, we have a singleton class with synchoronized method to generate unique id for a table. It has the following two issues.

1) Sync Lock time exceeds because every ejb object waits for this method execution to create unique id. It leads to deadlock.

2) In clustering environment , it wont create a unique id because servers are running im multiple JVM.

Thanks
Siva
[ December 14, 2005: Message edited by: Sivakumar Nachimuthu ]
 
Valentin Tanase
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sivakumar,

Although I�m no JBoss expert is my belief that JBoss should provide you a solution similar to the one that WebLogic does. I advice you to ask an appropriate question on the JBoss forum or look through JBoss documentation (which in my opinion really sucks :-)
Singletons are definitely not going to work. Although you might implement your singleton to do an in-memory caching and flush the id to the database once in a while, in order to provide fast access, this solution is not going to work in a cluster (as you�ve mentioned). The paradox is that despite our intuition and contrary to all believes the only solution to this problem is to use the database and have either an entity ejb or a SLSB for updating the sequence id in the database. This works with the cluster and has an OLTP nature, which at least in theory should be supported by J2EE. This also imposes that your database should be tuned properly and use the appropriate locking mechanism and isolation levels.
Regards.
 
Scott Selikoff
author
Bartender
Posts: 4081
21
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually the singleton solution will work just fine in a clustered environment if you use a 2-value key that has a context field and a unique id.

For example, give each machine (or JVM) a name (or better an id) so that the full key for a record is (machineId,UniqueId) such as ('server1',3401). This is guaranteed to be unique since each JVM has its own name. This solution has some additional advantages such as it now allows you to merge data in distributed systems easily. For example, you can have multiple de-synched machines that can combine their data easily for inserts without having to re-organize keys.

Remember, the only rule for keys is that they need to be unique, other than that they can be whatever you want.


And for completeness, you can use a singleton-like solution that avoid using a 2-key solution with multiple JVMs by setting up an EJB service that runs in one JVM. In fact, give it its own JVM if you plan to cluster. Some application servers, WebSphere for example, will optimize calls for all local servers so you will still get speeds similar to that of a singleton object.
 
Valentin Tanase
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Scott,


For example, give each machine (or JVM) a name (or better an id) so that the full key for a record is (machineId,UniqueId) such as ('server1',3401). This is guaranteed to be unique since each JVM has its own name. This solution has some additional advantages such as it now allows you to merge data in distributed systems easily. For example, you can have multiple de-synched machines that can combine their data easily for inserts without having to re-organize keys.


I wish it would always be that easy. Now let�s imagine that the jvm specific singleton generates keys from 1 to 1000, using the machineId approach. Further on let�s suppose that by the time it should generate the 1001 key the server crashes. The sys admin restarts the instance and the singleton is starting generating his sequence form 1 to 1000 again, leading to duplicate primary key errors. Of course this could apparently be prevented making sure that the starting sequence is always unique. I say apparently only because this is far from being trivial.


Remember, the only rule for keys is that they need to be unique, other than that they can be whatever you want.


How could one possible define an algorithm that generates unique numbers capable to respond to all situations, including instance crashes, highly multithreaded environment, time changes, etc.? Moreover why container vendors don�t have any such algorithms for generating the unique numbers? I explained you in this posting bea�s solutions that use database support like tables, sequences or identity columns. Why do you think they don�t have such a singleton in-memory base solution, because it looks very promising from a performances stand point? Maybe because is not that trivial�


And for completeness, you can use a singleton-like solution that avoid using a 2-key solution with multiple JVMs by setting up an EJB service that runs in one JVM. In fact, give it its own JVM if you plan to cluster. Some application servers, WebSphere for example, will optimize calls for all local servers so you will still get speeds similar to that of a singleton object.


Not sure if I understand you, but my question is this: why should one bother about a singleton EJB (which by the way is not trivial to implement) when s/he can use an entity cluster-aware EJB and database support without any complications?
Regards.
 
Scott Selikoff
author
Bartender
Posts: 4081
21
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Less overhead and more portability to answer your general question. I don't like solutions that are EJB server-dependent, they are contrary to the some of the principals of J2EE.

Your example about jvm crashing is a completely different issue, the jvm should use some level of pre-fetching and store this information in the database. For example, it should request ids from 1-100 (the database would store 100), then 100-200, etc so that it does not constantly have to connect to the db. At the center, the ids would still be generated in numeric order (a very common practice).

I never said this solution was perfect, I simply meant it would be possible in a clustered environment, would not depend on the particular server, and be relatively fast (if implemented correctly). Recovery and error handling are general problems unrelated to this. Singletons are non-trivial in J2EE but not extremely difficult in J2EE and there are some performance reasons for using them. I would still recommend a service layer to access the sequence generator, but one that bases its data off of context and a uses a singleton for caching.
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Going to the database for every key is too expensive, but relying on in-memory sequences is unreliable. So ...

We use a two-part key. Each server gets the first part from the database, then builds the second part from 1 to 999. Then it goes to the database for another first part. I'm pretty sure I got this idea from a Scott Ambler article.

Another approach is to use a single part key but reserve a range. So instead of incrementing the next available key on the database by 1, a server might increment the next available key by 1000. Then it could use 1000 in-memory seqeuence numbers before going to the database again.

Both of these result in missing sequence numbers in the database. Every time we stop a server we throw away 500 keys (on average). This may or may not be an issue for you.
 
Valentin Tanase
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Stan,


Going to the database for every key is too expensive, but relying on in-memory sequences is unreliable. So ...

We use a two-part key. Each server gets the first part from the database, then builds the second part from 1 to 999. Then it goes to the database for another first part. I'm pretty sure I got this idea from a Scott Ambler article.

Another approach is to use a single part key but reserve a range. So instead of incrementing the next available key on the database by 1, a server might increment the next available key by 1000. Then it could use 1000 in-memory seqeuence numbers before going to the database again.

Both of these result in missing sequence numbers in the database. Every time we stop a server we throw away 500 keys (on average). This may or may not be an issue for you.


Thank you Stan, it makes sense now.

Hi Scott,


Your example about jvm crashing is a completely different issue, the jvm should use some level of pre-fetching and store this information in the database. For example, it should request ids from 1-100 (the database would store 100), then 100-200, etc so that it does not constantly have to connect to the db. At the center, the ids would still be generated in numeric order (a very common practice).


You should say so from the beginning :-) This is rather a combination of in-memory generation as well as database support and makes more sense. It basically reduces database calls, which is great. It�s a decent approach I guess and as Stan pointed out it really works. Only one correction though: in my opinion the server crash scenario is not an unrelated issue at all. Is something that the unique id generation pattern should rather consider than ignore.


Less overhead and more portability to answer your general question. I don't like solutions that are EJB server-dependent, they are contrary to the some of the principals of J2EE.


Well different people might think different (ultimately we are all so different though :-), but the ideal of using pure J2EE solutions and not involving container specific technologies at all is in my opinion rather silly. As a matter of fact is not only the daily practice that proves it right, but most of good practices regarding optimizations, performance tuning and capacity planning are written with respect to the container you�re using. They deliberately contradict the J2EE build one run everywhere paradigm (which in my opinion has lot of theoretical value but not much practical appeal) and provide guidelines for improving your application within the environment you�re using. There is tons of good documentation for WebLogic for example posted on bea�s site, article posted on dev2dev forums and several WebLogic good books that takes your application from something able to run to something able to scale and be confidently delivered and deploy to WebLogic. Now imagine you're a tehnical arhitect working for a large J2EE project using WebLogic. Would you simply ignore all of these practice and tehniques only because J2EE tells you otherwise?
Regards.
 
Valentin Tanase
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sivakumar,

Got intrigued by this problem and I guess my perception was wrong. I always thought that it is too complex to be solved. Well some gentlemen on this forum had just proven me wrong again :-) However I spent few minutes looking through Floyd Marinescu�s EJB Design Patterns book and he has several Primary Key Generation strategies. One was already presented to you by Scott and Stan. The other one (there is one more but uses stored procedure) doesn�t require any persistence storage and it�s a pure in-memory generation pattern using global UUID. Get a copy of this book (do a google it�s free for downloading) and look at the UUID for EJB design pattern.
Regards.
 
Sivakumar Nachimuthu
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Gentlemen. I try these options and get back to you.

Thanks
Siva
 
Scott Selikoff
author
Bartender
Posts: 4081
21
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Valentin Tanase:
Well different people might think different (ultimately we are all so different though :-), but the ideal of using pure J2EE solutions and not involving container specific technologies at all is in my opinion rather silly.

They deliberately contradict the J2EE build one run everywhere paradigm (which in my opinion has lot of theoretical value but not much practical appeal) and provide guidelines for improving your application within the environment you�re using.

Would you simply ignore all of these practice and tehniques only because J2EE tells you otherwise?


In practice, there are always trade-offs. I'm not saying avoid all non-J2EE, as there are definitely some cases where using server-specific solutions is a plus, I'm saying avoid them at all costs unless using them doesn't significantly restrict their portability. For example, many servers have optimization for messaging and routing that are excellent to use. But, if you do use them, make sure you cut out places (either using general interfaces and factory patterns, or using external properties files) such that you are not hard-coding to these solutions. In other cases, servers can be configured for performance tuning independent of the application code.

What worries me the most is the future of J2EE. As certain solutions become more and more common, such as Hibernate, Spring, JUnit, sever-specific solutions will hopefully dwindle at least in the realm application code. You've got to keep in mind, one of the primary reasons for J2EE was designed was for open transparency and moduler development. As soon as you start tying your code to specific server solutions, you are immediately agreeing that if you ever switch software, someone is going to have to go in and re-do the code. An in practice, most of the time this does happen is when there is no one left who even understands how the code is written.
 
Sivakumar Nachimuthu
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Another approach is to use a single part key but reserve a range. So instead of incrementing the next available key on the database by 1, a server might increment the next available key by 1000. Then it could use 1000 in-memory seqeuence numbers before going to the database again.


In this approach , it could not create the sequence of unique ids. So i cant use the primary key for sorting with respect to time.

Thanks
Siva
 
Sivakumar Nachimuthu
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
The above issue is solved.

Instead of creating primary key using our own Sequence Generator , the custom implementation of the entity creation command objects of jboss app server used to insert entities is used.

You can know about this implementation details in the following link

http://docs.jboss.org/jbossas/jboss4guide/r4/html/ch11.chapter.html#ch11.pkgen.sect


Now i have a problem in Multi- Threaded Environment. I have multiple threads running at the same time to collect the data from different source and store it the table using ejb create method. When i use Primary key generation through entity commands , i am getting duplicate keys generated. How to synchronize the way the keys are generated?

Thanks
Siva
 
Mark Spritzler
ranger
Sheriff
Posts: 17309
11
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just out of curiosity. What about just using Hibernate and its ID generator, that way the IDs are based on the Java Objects first, then saved to the database, so the database doesn't matter, and doesn't generate the IDs.

Mark
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!