Hello,
I'm developing a server application exposing an API with the following requirement:
1) select the "next document in status to_be_processed" (that is, select the oldest record having a certain status)
2) change the status of the selected document to in_process
3) (make some more work on the selected data, and then) return the document

The application will have many clients in a highly concurrent environment, so it's crucial that 2 concurrent calls to this API select different records.
At the moment, there will be only one instance of the server application, but this may change in the future.

I'm using Oracle 11, Hibernate 3.5.2.


To me, the only bullet-proof approach seems to be "select for update"-based. So I wrote something like

Query query = hibernateSession.createQuery("from Document where status=1 order by insertDate);
query.setLockOptions(LockOptions.UPGRADE);
query.setMaxResults(1);
Document nextDocumentToBeCreated = (Document)(query.uniqueResult());

...only to discover that the generated SQL causes an Oracle error:

select * from ( select document0_.id as id6_, document0_.abi as abi6_, document0_.type as type6_, document0_.productNumber as productN4_6_, document0_.numPages as numPages6_, document0_.fileSize as fileSize6_, document0_.archiveId as archiveId6_, document0_.insertDate as insertDate6_, document0_.creationStartDate as creation9_6_, document0_.creationEndDate as creatio10_6_, document0_.testCase as testCase6_, document0_.status as status6_, document0_.creationErrorMsg as creatio13_6_ from MPS_Document document0_ where document0_.status=1 order by document0_.insertDate ) where rownum <= ? for update

==> java.sql.SQLSyntaxErrorException: ORA-02014: FOR UPDATE kann nicht auf View mit DISTINCT, GROUP BY usw. augewandt werden

(see also https://hibernate.onjira.com/browse/HHH ... ntId=35839).


The question: is there some solid workaround for this? Of course, I'm aware that -as long as I have only one instance of my server- I could just synchronize the piece of code around the database transaction... But I'd really really like to let the database handle the semaphore.
Another possibility would be to write a "native SQL" query, basically hand-writing the correct query (with "and rownum <= 1" in the where clause, and no nested select), but I'm never keen on writing Oracle-specific code...
Dropping the rownum condition is not an option, as the cardinality of the table will be large.
Oh, and I have also considered the "optimistic locking" approach... that would mean that the API keeps trying to select another record until it can update the status successfully... not very attractive...

This seems to be a fairly common application problem... Any suggestion welcome.
Regards,
Alberto