my dog learned polymorphism*
The moose likes Object Relational Mapping and the fly likes Oracle order of insertion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Oracle order of insertion" Watch "Oracle order of insertion" New topic
Author

Oracle order of insertion

Alex Armenteros
Ranch Hand

Joined: May 05, 2010
Posts: 69
I'm working with Hibernate 3 and Oracle 11g. and I have this problem.

I save the objects to the tables with a function like this.



Being T the class of the objects I'm storing in the DB.


But the problem comes as the application is multithread and is reading at the same time from the same table. But I've encountered the case that Oracle is inserting into the DB in an order different from the list. Let's say an example


In the list there are 5 objects in order {1,2,3,4,5}, when they are going to be inserted Hibernate looks for the sequence number to create their identificator and they recieve the identificators in order

1 1
2 2
3 3
4 4
5 5

But sometimes when I read (using the IDs as the order) in the other thread I got something like this.

Ids

1
2
5

This means that 3 and 4 weren't inserted but 5 was inserted.

Is there some way to prevent this?
Arun Kumarr
Ranch Hand

Joined: May 16, 2005
Posts: 513

Google for "Isolation Levels"


If you are not laughing at yourself, then you just didn't get the joke.
Alex Armenteros
Ranch Hand

Joined: May 05, 2010
Posts: 69
As you noted I'm no Oracle expert but in this case Oracle is working in "Cluster mode" and that can cause that behaviour but with serializing it should not happen, I think...
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Do the missing instances eventually appear? Can you identify them? Are they out of order, or just the assigned IDs have gaps?

I don't know Hibernate at all, but if it uses database sequences to generate IDs, you should note that sequences are not gap-free, and there is no way to make them so. Moreover, the numbers generated by sequence might not be in order, if your database is a RAC (cluster), which I understood is the case.

The only thing to expect from an Oracle sequence is that it will generate unique numbers. Don't try to force it to be ordered, or gap-free. It's just wasted effort.

Note that this issue is probably unrelated to isolation level. Oracle will never let you read uncommited records, you can only have read commited and serializable levels.
Alex Armenteros
Ranch Hand

Joined: May 05, 2010
Posts: 69
Oh sorry about that detail, yeah those entities finally appear in the database, because when the reader thread (remember it's a multithread process) reads the registries from the table it deletes them.

and in that example in the database the registries 3 and 4 remain in the DB as I've read 5 and I suppose I ' ve read them already, so I think I must change my read algorithm to battle the Oracle write "unorder".
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

So the third and fourth entries finally appear in the database, do have IDs 3 and 4 respectively, but appear there after the fifth entry?

That would be strange. From your description it appears that there is a commit after storing every entry (otherwise you could not ever see only entries 1,2,5 without seeing 3 and 4, if they were parts of the same transaction - the numbers are meant to denote the order in which the entries were stored).

Based on the previous information I assume that Hibernate grabs a new connection from a pool for every entry being stored and commits after every entry. This way individual entries could be written using different nodes in the RAC, hence the IDs would become unordered. However, I've always assumed that committed transactions are immediately visible on all nodes in a cluster (they must be, otherwise it would be impossible to honour referential integrity and other constraints), so neither way it should be possible to see records from later transaction before records from earlier transaction. Can you confirm this is happening? What Oracle version are you using?

And lastly, isn't Hibernate by chance persisting the entries in background threads? (As I've mentioned before, I don't know Hibernate, so please be forbearing if this question is silly.)
Alex Armenteros
Ranch Hand

Joined: May 05, 2010
Posts: 69
Thanks to you Mr Vajsar, an idea came to me.

I'll try something like this...



I think this way all inserts will be commited at the same time... dunno if this will solve anything, I'll keep you informed to satiate your curiosity.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Yes, that seems promising. However, this modifies the original premises and it is possible that you'll run into the same situation later on, since the cause of the original problem is not understood (at least to me).
Alex Armenteros wrote:I'll keep you informed to satiate your curiosity.

Of course you're not obliged to keep me informed . I keep asking those questions because the whole process is rather complex and we still don't have all necessary information in this thread, and because it seems that either there is a bug in Oracle (entirely possible, I've run into bugs in Oracle DB myself), or you're doing something you probably don't entirely understand. Both of these possibilities deserve to be resolved, in your own interest: otherwise they'll come back and bite you, probably in circumstances even less suitable than now, eg. after deploying the app into production.

Simply answering my questions might help more (and faster) than trying to repair the problem by accident. I had a reason to ask them. Of course, it is all up to you.
Arun Kumarr
Ranch Hand

Joined: May 16, 2005
Posts: 513

Martin Vajsar wrote:
The only thing to expect from an Oracle sequence is that it will generate unique numbers. Don't try to force it to be ordered, or gap-free. It's just wasted effort..


Are you using the NoCache option? May be hibernate has not yet commited the cached 3 and 4 and commits it later?
Check this Sequence-Caching.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Arun Kumarr wrote:Are you using the NoCache option? May be hibernate has not yet commited the cached 3 and 4 and commits it later?
Check this Sequence-Caching.


I would discourage to use the NOCACHE option. NOCACHE is bad for performance, especially on RAC. May be viable if the sequence is accessed, say, a few times in a hour, but I wouldn't use that anyway.

Moreover, the referenced article is mistaken. NOCACHE does not quarantee a gap-free sequence. Gaps will be easily generated by a rollback. Applications depending on rollbacks not occurring are a bit too much fragile.

The sole purpose of sequences in Oracle is to generate a unique surrogate key. Expecting more or trying to do more with them is not wise.
Arun Kumarr
Ranch Hand

Joined: May 16, 2005
Posts: 513

Arun Kumarr wrote: Are you using the NoCache option?

My Bad. I meant to ask the OP if he is using cache option and not NoCACHE OPTION?

Martin Vajsar wrote:NOCACHE does not quarantee a gap-free sequence. Gaps will be easily generated by a rollback.

You're right. It doesn't guarantee gap-free sequence. But the reference article also says the same.
Alex Armenteros
Ranch Hand

Joined: May 05, 2010
Posts: 69
Thanks for all the answers, but chief told me that I can't afford to use this (this process must update some tables in real time and we cannot take the risk of one node of the cluster failing) and I'll use another method to fill the table.

I close the thread, and thanks again for your answers.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle order of insertion
 
Similar Threads
database access pattern
Hibernate with strange behavior
Errors using Oracle XA datasource
Inserting Blob data into Oracle DB through JDBC
Batch update in EJB