Grails. Cannot get a connection, pool exhausted

Question

After reworking one service method in order to use multithreading I found out that if more than one user is trying to request page (and call service method) many times, the server begins throwing "Cannot get a connection, pool exhausted" exception. Let me provide an example of my service class.

class DocumentService {
   def convertToJSON() {
      docs.collect { doc ->
         taskExecutor.submit({
            Document.withNewSession {
                def json = convertDocumentToJSON(doc)
            }
         } as Callable)
      }.collect { it.get() }
   }

   def convertDocumentToJSON(doc){
      def json = [:]
      // ... fill json with data using doc and GORM requests
      evaluateStatus(json)
      return json
   }

   def evaluateStatus(json){
      //... some work using database through GORM
   }
}

I've been struggling with this issue for more than week and I can't find a solution. I don't understand well how Grails works with sessions, connections and transactions. My guess is following. When convertDocumentToJSON is called, it takes connection from pool (4 users, 25 threads per user = 100 connections) but then they need to call evaluateStatus method, which also tries to obtain connection from pool for its own needs. But pool is exhausted, and no one thread can release connection because all of them are waiting for evaluateStatus. So, there is deadlock. I tried to place SUPPORT propagation type for evaluateStatus, but I get "pooled connection has been closed" exception. Then I thought it might work if I move evaluateStatus call from convertDocumentToJSON and wrote such code

def convertToJSON(){
  docs.collect { doc ->
    taskExecutor.submit({
        Document.withNewSession {
            def json = convertDocumentToJSON(doc)
            evaluateStatus(json) // move call from convertDocumentToJSON
        }
      } as Callable)
  }.collect { it.get() }
}

but in this case I also faced the same error (exhausted pool). Please, give me advice what should I fix in my code

Answer 1

You need something to throttle the use of connections, try using GPars e.g.

import groovyx.gpars.GParsPool

GParsPool.withPool() {
    docs.collect {
        // do your stuff...
    }
}

There are XXXXParallel versions of the usual groovy collection methods e.g. collectParallel which you can play around with to assist with performance.