0

How would I accomplish the following query with GORM? 

select * 
from T where id in 
(
    SELECT id
    FROM (
        SELECT *
        FROM T
        WHERE X is NULL
        ORDER BY Y DESC
        )
    WHERE ROWNUM <= 1
)
FOR UPDATE;

The method call I'm trying looks like this:

T.findByXIsNull(sort: "Y", order:"desc", lock: true)

However, I got the following error (Oracle 11gR2): ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

The reason I believe it's failing is because Hibernate is converting it to the following query:

SELECT *
FROM (
    SELECT *
    FROM T
    WHERE X is NULL
    ORDER BY Y DESC
    )
WHERE ROWNUM <= 1 FOR UPDATE;

This query is trying to apply the FOR UPDATE directly on the clause limiting rownum. A wrapper select statement is needed to apply the FOR UPDATE, as in my example at the top (and discussed here: How to solve ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY). How can this be accomplished?

Update1

Looks like this is a bug in GORM/Hibernate in generating the query when both order by and rownnum <= ? is specified. Both of the following work individually:

T.findByXIsNull(sort: "Y", order:"desc")
T.findByXIsNull(lock: true)

But together T.findByXIsNull(sort: "Y", order:"desc", lock:true) they fail with the ORA-02014 error. The fix would be for Hibernate to generate the SQL as I described at the top of this post, which wraps the lock into another outer select statement. However, there may be a workaround that I'm not aware of.

Community
  • 1
  • 1
Alex
  • 9,250
  • 11
  • 70
  • 81

1 Answers1

1

AFAIK findBy* does not support pagination and order parameters because it will always return the first matching result.

If you want to use the sorting then you have to use findAllBy* and then pick up the first row for locking.

Using findBy I would try

//To avoid the infinitesimal chance of dirtiness between fetching and locking.
def t = T.findByXIsNull([lock: true])
//Round-about an unliked way 
def t = T.lock(T.findByXIsNull()?.id) 

//Or easier
def t = T.findByXIsNull()
t.lock()

Using findAllBy with pagination params:

def t = T.findAllByXIsNull(sort: "Y", order:"desc", max: 1, lock: true)

Untested with Oracle db

dmahapatro
  • 49,365
  • 7
  • 88
  • 117
  • Yes, you're right that I don't need the max:1 since the Oracle query is still `rownum <= 1` due to findBy. I removed max:1 from my code. However, I must have the lock be part of the same select statement that picks the row. Splitting them up into two separate select statements is not allowed due to possible race condition. – Alex Jul 17 '13 at 22:26
  • @Alex Have you tried either of the approach now? `(findBy*(lock: true) and/or findAllBy*(...))` – dmahapatro Jul 17 '13 at 22:27
  • I've tested with lock by itself and sort options by themselves and they both work. Looks like the issue is with sort and lock options combined together. I added this information in detail as an update at the bottom of the question. – Alex Jul 18 '13 at 13:49