Problem combining locking and paging on Oracle

Description

I'm trying to make a select for update that returns only 1 row and in the query there is an order by:

<query name="msg_m_to_b.select.head.by.userId.appId">
        from MessageMtoB msg
        where msg.userId = ? and msg.applicationId = ? and msg.blockId is null
        order by msg.id asc
</query>


Query query = session.getNamedQuery("msg_m_to_b.select.head.by.userId.appId");
query.setMaxResults(1);
query.setLockMode("msg", LockMode.UPGRADE);
query.setLong(0, userId);
query.setLong(1, appId);
message = (IMessage) query.uniqueResult();

The query that generates is incorrect:

select * from ( 
	select messagemto0_.MOAPVKMSGMT_ID as MOAPVKMS1_, messagemto0_.MOAPVUMSGMT_MESSAGE_ID as MOAPVUMS2_6_, messagemto0_.MOAPNFMSGMT_USER_ID as MOAPNFMS3_6_, messagemto0_.MOAPNKMSGMT_LICENSE_ID as MOAPNKMS4_6_, messagemto0_.MOAPVFMSGMT_IN_RESPONSE_OF as MOAPVFMS5_6_, messagemto0_.MOAPNFMSGMT_APPLICATION_ID as MOAPNFMS6_6_, messagemto0_.MOAPDGMSGMT_CREATION_DATE as MOAPDGMS7_6_, messagemto0_.MOAPCVMSGMT_MESSAGE_BODY as MOAPCVMS8_6_, messagemto0_.MOAPVGMSGMT_BLOCK_ID as MOAPVGMS9_6_, messagemto0_.MOAPDGMSGMT_ENQUEUE_DATE as MOAPDGM10_6_ 
	from MOAPTBMSG_M_TO_T messagemto0_ 
	where messagemto0_.MOAPNFMSGMT_USER_ID=? and messagemto0_.MOAPNFMSGMT_APPLICATION_ID=? and (messagemto0_.MOAPVGMSGMT_BLOCK_ID is null) 
	order by messagemto0_.MOAPVKMSGMT_ID asc ) 
where rownum <= ? for update of messagemto0_.MOAPVKMSGMT_ID

---> ORA-00904: messagemto0_.MOAPVKMSGMT_ID not a valid identifier

The field in the "for update" should be MOAPVKMS1_:

select * from ( 
	select messagemto0_.MOAPVKMSGMT_ID as MOAPVKMS1_, messagemto0_.MOAPVUMSGMT_MESSAGE_ID as MOAPVUMS2_6_, messagemto0_.MOAPNFMSGMT_USER_ID as MOAPNFMS3_6_, messagemto0_.MOAPNKMSGMT_LICENSE_ID as MOAPNKMS4_6_, messagemto0_.MOAPVFMSGMT_IN_RESPONSE_OF as MOAPVFMS5_6_, messagemto0_.MOAPNFMSGMT_APPLICATION_ID as MOAPNFMS6_6_, messagemto0_.MOAPDGMSGMT_CREATION_DATE as MOAPDGMS7_6_, messagemto0_.MOAPCVMSGMT_MESSAGE_BODY as MOAPCVMS8_6_, messagemto0_.MOAPVGMSGMT_BLOCK_ID as MOAPVGMS9_6_, messagemto0_.MOAPDGMSGMT_ENQUEUE_DATE as MOAPDGM10_6_ 
	from MOAPTBMSG_M_TO_T messagemto0_ 
	where messagemto0_.MOAPNFMSGMT_USER_ID=? and messagemto0_.MOAPNFMSGMT_APPLICATION_ID=? and (messagemto0_.MOAPVGMSGMT_BLOCK_ID is null) 
	order by messagemto0_.MOAPVKMSGMT_ID asc ) 
where rownum <= ? for update of MOAPVKMS1_

Now, this query gives the following error:
ORA-02014:, cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

I think that this is because you can't put an order by in the inner query and a for update in the external query.

I re-write the query, adding the rownum condition to the inner query and eliminating the extenal query:

select messagemto0_.MOAPVKMSGMT_ID as MOAPVKMS1_, messagemto0_.MOAPVUMSGMT_MESSAGE_ID as MOAPVUMS2_6_, messagemto0_.MOAPNFMSGMT_USER_ID as MOAPNFMS3_6_, messagemto0_.MOAPNKMSGMT_LICENSE_ID as MOAPNKMS4_6_, messagemto0_.MOAPVFMSGMT_IN_RESPONSE_OF as MOAPVFMS5_6_, messagemto0_.MOAPNFMSGMT_APPLICATION_ID as MOAPNFMS6_6_, messagemto0_.MOAPDGMSGMT_CREATION_DATE as MOAPDGMS7_6_, messagemto0_.MOAPCVMSGMT_MESSAGE_BODY as MOAPCVMS8_6_, messagemto0_.MOAPVGMSGMT_BLOCK_ID as MOAPVGMS9_6_, messagemto0_.MOAPDGMSGMT_ENQUEUE_DATE as MOAPDGM10_6_ 
from MOAPTBMSG_M_TO_T messagemto0_ 
where messagemto0_.MOAPNFMSGMT_USER_ID=? and messagemto0_.MOAPNFMSGMT_APPLICATION_ID=? and (messagemto0_.MOAPVGMSGMT_BLOCK_ID is null) and rownum <= ? 
order by messagemto0_.MOAPVKMSGMT_ID asc
for update of messagemto0_.MOAPVKMSGMT_ID

this query works

Attachments

23 Aug 2012, 08:56 PM

Linked issues

is a fix for

Failed to load remote issue because remote site is not linked

Activity

Show:

Piotr FindeisenNovember 18, 2014 at 1:08 PM

Now I realized Scott Van Wart's expressed exactly same concerns. Sorry for doubled comments!

Piotr FindeisenNovember 18, 2014 at 1:06 PM

I poked into org.hibernate.loader.Loader.shouldUseFollowOnLocking in Hibernate 4.2.15 / 4.2.16 and found that:

protected boolean shouldUseFollowOnLocking(QueryParameters parameters, Dialect dialect, List<AfterLoadAction> afterLoadActions) {
    if ( dialect.useFollowOnLocking() ) {
        LOG.usingFollowOnLocking();
        .....

Why there is no check over parameters.getRowSelection()?
Issue title says "Problem combining locking and paging", but what is there any problem if there is no paging?

I guess the "no paging query with locking" is the case in HHH-9486.

Scott Van WartJanuary 17, 2013 at 6:52 PM

You know what? I just realized rownum is determined before results are ordered, so my query above wouldn't work. Take out pagination though, and it would still be nice to see pessimistic locking at least enabled for non-paginated queries.

Scott Van WartJanuary 17, 2013 at 6:50 PM

Steve, do you anticipate being able to find a middle ground with the locking issue? I just updated my hibernate installation and noticed follow-on locking is used exclusively. I rely on pessimistic locking in the database for a number of projects so I've temporarily overridden useFollowOnLocking() to return false in my own Oracle dialect class. While FOR UPDATE can't be specified for any statement that has a lower-bound result limit and an order by, it should function in all other existing cases. So an upper-bound result limit (firstResult == 0 with maxResults set) with an ORDER BY works just fine:

SELECT ... FROM ... WHERE rownum <= :maxResults ORDER BY ... FOR UPDATE;

Obviously Hibernate now wraps the entire query once either firstResult or maxResults is set, but it's not strictly necessary. Adding this special case for firstResult == 0 would allow immediate pessimistic locking on any use case that said, "give me the earliest/latest/lowest/greatest X and lock them immediately", which happens to match my case perfectly (a task queue with many nodes pulling the "next available task" from the task table--causes optimistic lock failures with follow-on locking).

Steve EbersoleNovember 30, 2012 at 4:09 PM

Adding another warning here in the case of users requesting alias-specific lock modes and dialects that request follow-on locking. Currently that does not work. The alias-specific lock modes are ignored.

Fixed

Details
Assignee
Steve Ebersole
Reporter
Miquel Barba
Time tracking
22.1h logged
Components
hibernate-core
Fix versions
4.1.9
4.3.0.Beta1
Affects versions
3.0.5
3.1 rc2
Priority
Major

Created November 16, 2005 at 1:42 PM

Updated November 18, 2014 at 1:08 PM

Resolved November 30, 2012 at 8:09 PM

Problem combining locking and paging on Oracle

Description

Attachments

Linked issues

is a fix for

is duplicated by

is followed up by

relates to

Activity

Piotr FindeisenNovember 18, 2014 at 1:08 PM

Piotr FindeisenNovember 18, 2014 at 1:06 PM

Scott Van WartJanuary 17, 2013 at 6:52 PM

Scott Van WartJanuary 17, 2013 at 6:50 PM

Steve EbersoleNovember 30, 2012 at 4:09 PM

Details
Assignee
Steve Ebersole
Reporter
Miquel Barba
Time tracking
22.1h logged
Components
hibernate-core
Fix versions
4.1.9
4.3.0.Beta1
Affects versions
3.0.5
3.1 rc2
Priority
Major

Details

Assignee

Reporter

Time tracking

Components

Fix versions

Affects versions

Priority

Flag notifications

Something's gone wrong

Problem combining locking and paging on Oracle

Description

Attachments

Linked issues

is a fix for

is duplicated by

is followed up by

relates to

Activity

Piotr FindeisenNovember 18, 2014 at 1:08 PM

Piotr FindeisenNovember 18, 2014 at 1:06 PM

Scott Van WartJanuary 17, 2013 at 6:52 PM

Scott Van WartJanuary 17, 2013 at 6:50 PM

Steve EbersoleNovember 30, 2012 at 4:09 PM

DetailsAssigneeSteve EbersoleSteve EbersoleReporterMiquel BarbaMiquel BarbaTime tracking22.1h loggedComponentshibernate-coreFix versions4.1.94.3.0.Beta1Affects versions3.0.53.1 rc2PriorityMajor

Details

Assignee

Reporter

Time tracking

Components

Fix versions

Affects versions

Priority

Flag notifications

Something's gone wrong

Details
Assignee
Steve Ebersole
Reporter
Miquel Barba
Time tracking
22.1h logged
Components
hibernate-core
Fix versions
4.1.9
4.3.0.Beta1
Affects versions
3.0.5
3.1 rc2
Priority
Major