Wednesday, November 25, 2009

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

Eclipselink: Null primary key encountered in unit of work clone

Remember that XKCD strip about the most boring guy in the world and his blog
where he only apologizes for not writing more? :D Here it is ;)

Well, it's been a while. To be honest, civilian military service is not actually best environment for us developers, but what can you do, from June next year, I'm back on track.

Meanwhile, a blast from the past. I received a notification about a bug on Eclipselink you can find here. Of course, I encountered it last summer. Suddenly a weird error Null primary key encountered in unit of work clone, as the title of this post (I hope it helps with google).

So, this is one of those things that just frustrates you. You develop application, write queries by the book, OK, push the limits probably on few fronts, but it's all according to specification, should work, wright? Yeah, should. But bug like this just kills you. It occurs seemingly randomly, fires up nad causes some null pointer exceptions in upper layers of your application, so you dig in and find thing like this. I can tell you, I've been lucky here. In most cases I just run (actually Google does) into two or three guys who had the same problem (usually our Corean brotheren, hats off ! ), and no solution...

So, it had something to do with assigning sequence numbers. I went so far to dig into the source code of Eclipselink, but honestly, I couldn't get my way around it, even debugging (I'm not proud) didn't help. Guys who discussed the problem on the thread about the bug suggested that the problems are negative ID's which could not occur in our application. At the end, we've given up. And now the blessed news:

Chris Delahunt 2009-11-12 16:13:34 EST
Peter checked in a fix 02 November 2009

I'll check it with the guys at work to see if they still have the same problem
as before and if new patch will help.

This reminds me of a very interesting problem tha led to very
interesting solution,regarding sequencing, concurency and
multiplatform implementation of JPA.
Interested? Well, OK, you will have it in the next blog post which,
I hope will not be seven months from now . :)