-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 11 posts ] 
Author Message
 Post subject: Select entities by RAND to avoid concurrent modifications
PostPosted: Tue Aug 02, 2016 1:15 am 
Newbie

Joined: Tue Aug 02, 2016 1:09 am
Posts: 6
I am using hibernate 5.0 with Oracle DB and have the following issue.I have to mention that I saw the issue HB-958 it is a really old issue, but I need such feature.

I want to produce

select dbms_random.value l ,e.* from my_table e order by l

but hibernate generates the following for me

select dbms_random.value formula2_4_ ,e.* from evoucher_scm.evoucher e order by dbms_random.value

The second query is totally different from first one.
I resolved this problem by NativeSQLOrder and hard-coding "formula2_4_".
But it's not the good or even correct solution.

Any help is appreciated.
Thanks in advance.
Pedram


Top
 Profile  
 
 Post subject: Re: Ordering by @Formula using its alias
PostPosted: Tue Aug 02, 2016 1:31 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
What exactly is your use case? I don't understand what is your requirement. Do you want a randomly assigned value for your entity upon persisting it, or do you want a random value every time you fetch it from DB?


Top
 Profile  
 
 Post subject: Re: Ordering by @Formula using its alias
PostPosted: Tue Aug 02, 2016 1:42 am 
Newbie

Joined: Tue Aug 02, 2016 1:09 am
Posts: 6
mihalcea_vlad wrote:
What exactly is your use case? I don't understand what is your requirement. Do you want a randomly assigned value for your entity upon persisting it, or do you want a random value every time you fetch it from DB?


I want randomly generate a value for each row and then ordering them with this dummy field and then pick the first one to reduce
concurrent access to the same row. By ordering on random values, I want to make chance of selecting each row uniform. I am using Optimistic lock and dont want to use pessimistic locking mechanism of db.


Top
 Profile  
 
 Post subject: Re: Ordering by @Formula using its alias
PostPosted: Tue Aug 02, 2016 2:33 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
If you're trying to implement a Job queue, you might be interested in SKIP_LOCK support that Hibernate offers for Oracle and PostgreSQL 9.5.

Otherwise, you can still implement your design using insert-generated values instead of @Formula. @Formula is useful when you want to fetch a value from the database, while @ValueGenerationType is what you want to insert random values. Then you can ORDER BY tht specific property that you only generate a value for during entity persisting.


Top
 Profile  
 
 Post subject: Re: Ordering by @Formula using its alias
PostPosted: Wed Aug 03, 2016 4:05 am 
Newbie

Joined: Tue Aug 02, 2016 1:09 am
Posts: 6
Thank you mihalcea_vlad for prompt answers and suggesting these interesting features.

But my requirements is different.
Suppose two "select * from my_table where ..." queries come to DB simultaneously. Each query have to fetch one record (outer select with "where rownum=<1" is omitted) with different ID, so I need some type of random sort to fetch different rows with these simultaneous queries. I want a dummy column with an alias like "select dbms_random.value as rand_val ... order by rand_val" to distribute chance of selection by each simultaneous query among all rows.

Thanks
Pedram


Top
 Profile  
 
 Post subject: Re: Ordering by @Formula using its alias
PostPosted: Wed Aug 03, 2016 4:48 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
And what if by chance two selects will fetch the same row because both used the same random value?

I understand what you are trying to do. I don;t understand what is the actual reason for doing such a hack. Is it related to concurrency control? Do you plan on modifying thos eentities and you don't want a conflict? There are better ways to solve such a requirement.


Top
 Profile  
 
 Post subject: Re: Ordering by @Formula using its alias
PostPosted: Wed Aug 03, 2016 5:30 am 
Newbie

Joined: Tue Aug 02, 2016 1:09 am
Posts: 6
mihalcea_vlad wrote:
And what if by chance two selects will fetch the same row because both used the same random value?


I am trying to prevent such conflicts on best-efforts basis, also in our case we have lots of rows which satisfy our select query(typically about 10000 rows) so I believe that the probability of such situations are very low.

mihalcea_vlad wrote:
I understand what you are trying to do. I don;t understand what is the actual reason for doing such a hack. Is it related to concurrency control? Do you plan on modifying thos eentities and you don't want a conflict? There are better ways to solve such a requirement


Yes exactly, the fetched rows are going to be modified and an update query will be sent to DB. We are using Optimistic Lock by hibernate to prevent conflicts.

Note that I am forced not to use pessimistic lock.
What is your suggestion?

Thanks
Pedram


Top
 Profile  
 
 Post subject: Re: Ordering by @Formula using its alias
PostPosted: Wed Aug 03, 2016 6:45 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
There are better ways to do that. Since you are "forced" not to use pessimistic locking, you can still workaround conflicts by using a versionless optimistic locking approach.

The versionless optimistic locking minimizes the chance of conflict while still ensuring that lost updates are prevented.

But that's not all. Depending on your use case, you could also try an automated retry mechanism. However, the automated retry mechanism is double edged sword. If you are simply overwriting a database record whose state has been changed since you first read it, you are going to lose updates. But you are the only one to tell if that's ok or not for your use case.


Top
 Profile  
 
 Post subject: Re: Select entities by RAND to avoid concurrent modifications
PostPosted: Sun Aug 07, 2016 5:33 am 
Newbie

Joined: Tue Aug 02, 2016 1:09 am
Posts: 6
hi vlad,

Sorry for my late reply.
After these days we eventually decided to use "for update of [column] skip locked".
Now I`m trying to fill [column] by using

Code:
criteria.setLockMode(crit.getAlias(), LockMode.UPGRADE_SKIPLOCKED);

getAlias returns "this" and if i change the value,it seems hibernate does not generate for update.
How can I handle this problem?

Thanks
Pedram


Top
 Profile  
 
 Post subject: Re: Select entities by RAND to avoid concurrent modifications
PostPosted: Mon Aug 08, 2016 12:22 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
The legacy Criteria is deprecated. Try using Criteria API from JPA or JPQL/HQL queries instead. Even if it's a bug in legacy Criteria, it won't be addressed by the Hibernate team, we'll only integrate a fix that you provide as a Pull Request.


Top
 Profile  
 
 Post subject: Re: Select entities by RAND to avoid concurrent modifications
PostPosted: Mon Aug 08, 2016 3:39 am 
Newbie

Joined: Tue Aug 02, 2016 1:09 am
Posts: 6
Thanks for your informative posts.
I will try JPA api.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 11 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.