• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle order of insertion

 
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 662
Eclipse IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Google for "Isolation Levels"
 
Alex Armenteros
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 662
Eclipse IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 662
Eclipse IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
They worship nothing. They say it's because nothing is worth fighting for. Like this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic