Hibernate – grails: Select Return (Random) Null, and the rows in PostgreSQL exist, and the connection pool has been opened

class Question implements Serializable {

 static hasMany = [resources: Resource]

 static constraints = {
 resources(nullable: true)
} 
 def afterInsert() {
 Resource .withNewSession {
 Resource txt = Resource.create(null)
 this.addToResources(txt)
 }
} 

 Resource retrieveResource(){< br /> return this.resources.find{ it instanceof Resource }
} 

 static Question create(Map params) throws SaveDomainException {
 //question creation
} 
}

//first we create question and save it
def questio n = Question.create(params)
question.save(flush:true, insert:true)
getThirdPartyService().doCallAsync((int)req.retrieveResource().id)

We have developed a Grails 2.0 application, which previously ran smoothly on MySQL

We have been asking administrators to switch to their favorite PostgreSQL

The application has added some new features, including the one that is causing us problems now: asynchronous third-party web service requests

So, we created a domain object, let’s call it the problem. Using the afterInsert closure, A Resource will be created to store the results of calls to external web services later.

class Question implements Serializable {

 static hasMany = [resources: Resource]

 static constraints = {
 resources(nullable: true)
} 
 def afterInsert() {
 Resource.withNewSession {
 Resource txt = Resource.create(null)
 this.addToResources(txt)
 }
} 

 Resource retrieveResource(){
 return this.resources.find{ it instanceof Resource }
} 

 static Question create(Map params) throws SaveDomainException {
 //question creation
} 
}

We create questions like this:

//first we create question and save it
def question = Question.create(params)
question.save(flush:true, insert:true)
getThirdPartyService().doCallAsync((int)req.retrieveResource ().id)

And ThirdPartyService as the method doCallAsync generates ExecutorService (obtained by the grails plugin of the executor, so this is not a terrible “Hibernate Session-Thread” problem)
It executes a simple Resource. get(res_id), its ID is obtained as shown above

The problem is that for pooled = true in PostgreSQL and DataSource.groovy, get returns sometimes null instead of resource objects.

We have tested 3 different requests: get(id), findById(id) and executeQuery with select.

What’s even stranger is that in the same method with the three lines above, we sometimes Get different results. Only one of the three returns null, or three returns null, or none (this is the expected behavior, I remember)

We open the PostgreSQL query log to see if it is a Hibernate Cache problem, but 3 requests appear in the log, so every time there is a hibernate hit in the database. We see that the insert of the resource has the correct id, then the commit, and then the three choices ( Provide the correct resource ID)

Does anyone have any hints about what we want to test further to see where this bug comes from? (We tried to change the connection pool, no luck)

The last thing, if we add a Thread.sleep(1000) before the request (this is bloody but only for testing purposes ;-)), All operations will proceed smoothly. Therefore, it seems to be a problem of visibility between the postgres processes, but we don’t know how to solve this problem

You spend time researching This question is written as a good question, but the important log is missing?

I speculate that there are overlapping transactions between your save and async get. Before the save occurs, save uncommitted or get is viewing a consistent snapshot. Make sure that the process identification is enabled in the logging , Or distinguish the connection in other ways, and check the order of the statements.

Edit: looks like transaction snapshot timing.

In PostgreSQL, all statements are in one transaction (maybe Is implicit and only lasts one statement).

The default mode is “Read Committed”, which means that you can view the commits that occurred during the transaction.

There is also a “Read Committed” “Serialization” level option, which means you (mostly) see a frozen snapshot of the database at the beginning of the transaction.

For more information, please refer to the docs.

Open two psql consoles and try to submit some variants in one and choose a different isolation level in the other. You should be able to see what is happening with your real-time system.