Eclipselink: table sequence generator and concurrency - Part One

Why do we like OR mapping? OK, you got tons of advertising written about that, so take your pick.
For your boss most likely most interesting thing would be universal database connectivity. You have customers with various databases, so it is much easier to sell your software. You dont have to buy a licence for a new database, relax, we got them all covered... Actually, that was our case.

OK, that is cool. We had our little piece of software up and running on Eclipselink (I had quite an adventure on migrating from Hibernate, but that is another story). Since we wanted to be database vendor independent, we chose table generated ID's for our primary keys.

Little intro, just to put some light on it, but I'm sure you guru's out there don't need it ;) Here is a good place to start. There you can find details on ID generation strategies.

When you specify an entity in JPA, you have to set one of the class fields as an ID, using annotation @Id.

So the next step is to see how to assign those ID's. You could set values yourself before persisting an object, but that is not very useful in practice. There are also options for using identity or sequencing, but this is not applicable on all databases. For instance, identity columns are available in SQL Server, but Oracle uses sequences. If you want universal solution, portable on all databases, you need table generated primary keys. It is similar to Oracle sequences, only the values are stored in a user table Eclipselink is accessing. If you use schema generation, Eclipselink will, according to your annotations, generate that table. Usually table has columns for sequence name and value, allocation size (default 50). If you look into the documentation on @TableGenerator annotation, you will see that you can configure name for this table, its columns, and also values for allocation size etc.

So it all sounded nice. And on Hibernate it worked nice. But life just isn't that simple. Life is fun.

While doing some stress testing on some application modules I became aware of an unpleasant fact that unusual number of unique constraints is occuring.

What I did was that I deployed application module and set up some 5 clients on others computers to target it with a large number of subsequent requests which typically did some inserting and updating in the database. Then it turned out that I get a relatively large number of unique constraint errors - on primary keys!

So, 5 clients target EJB modules, services in modules access database using Eclipselink. New objects are generated and mapped into tables. When persisting objects, Eclipselink takes tabel generated value and assigns it as objects ID. INsert into... And same thing happens initiated from several clients. One should expect that Eclipselink gives different values for primary keys each time new object is persisted. It shoul handle concurrency. But it looks like it does not.

Of course, we have checked our test cases and they were OK. It works just fine in a single client situation. When we addes another client, only 4-5 unique constraints occured on some 10000 inserts. Adding new clients increased exoenentially number of exceptions.

I have written few words to Oracle, on official forum as well on Metalink (now Oracle Support). Well, guys told me that it it shouldn't happen. But it does. It is not anything exotic - stateless session beans, jpa persistence using eclipselink provider on Weblogic server. You look up bean, method creates and persists object. It is not clustered environment.

I would really like to know if anyone else had similar problem, so if you hear anything... :)

Then search for alternative methods for generating ID's started. After a while, it turned out that Eclipselink is flexible enough to give us opportunity to use our own ID generation mechanism.

Next post will be really interesting one, when I present our cool solution :D