Grails session handling in waiting thread with Hibernate and MySQL InnoDB

In order to realize client-side notifications in an AJAX-driven application that I am developing with Grails (and GWT), I have implemented a service method that will block until it is being signaled. I am using a monitor object to wait for a signal. Once signaled, the thread will query the database for new objects and then return the entities to the browser.

It is working perfectly fine with the memory database but not as I expect when I use the My开发者_如何学CSQL database connector. What happens: whenever I do a findAllBy... call it will only find objects that were created before the request started.

The lifecycle of my service method

• request from client
• Hibernate session is being created by Grails
• service querying database for new objects
• if there are none: wait
• incoming signal: query database for new objects (DOES NOT GET NEW OBJECTS when using MySQL, works fine with memory db)

The mysql query log shows all the queries as expected but the result of findAllBy... is just an empty array.

I disabled query and second level cache. Behaviour is the same no matter if data connection is pooled or not.

What am I doing wrong? Should I close the Hibernate session? Flush it? Use a transaction for my queries? Or somehow enforce the findAllBy... method to query the database?

Just a guess, but this sounds like a transaction isolation level problem where you are experiencing a phantom read. Does your service need need to be transactional? If not set transactional=false in the service.

I think that you need to flush the session on the save calls for the new objects that you are looking for, e.g.

DomainOfFrequentlyAddedStuff.save(flush:true) 

Then they should be persisted to the db quickly so they will show up in your findAll() query.