dataSource injection in a Grails service

I have a service with application scope, not transactional.

I have a service method which:

1. uses the injected dataSource to create a stored procedure call [using Sql.call{...}]. Executes and traverse the resultset.

2. Based on the resultset, I subdivide the resultsets into equal sizes chunks and process them in multiple threads.

3. Each thread tries to do Sql sql = new Sql(dataSource)开发者_如何学运维

4. Here a deadlock occurs.

Why is that? Does dataSource not return a possibly new or an idle connection?

Try to look into Gpars : It's a groovy parallalization framework.

I run into exactly the same issue. After hours of searching i've found the solution.

In your Datasource.groovy configfile you are able to set the parameters for the connection pooling to the database.

I've changed the minIdle, maxIdle and maxActive settings of the http://commons.apache.org/proper/commons-dbcp/apidocs/org/apache/commons/dbcp/BasicDataSource.html so that my configfile looks something like this:

dataSource {
  url = "jdbc:mysql://127.0.0.1/sipsy_dev?autoReconnect=true&zeroDateTimeBehavior=convertToNull"
  driverClassName = "com.mysql.jdbc.Driver"
  username = "sipsy_dev"
  password = "sipsy_dev"
  pooled = true
  properties {
    minEvictableIdleTimeMillis=1800000
    timeBetweenEvictionRunsMillis=1800000
    numTestsPerEvictionRun=3
    testOnBorrow=true
    testWhileIdle=true
    testOnReturn=true
    minIdle=100
    maxIdle=250
    maxActive=500
    validationQuery="SELECT 1"
  }
  dialect = 'org.hibernate.dialect.MySQL5InnoDBDialect'
}

When you are not in a transaction, you have to release the connection that GroovySQL picks up from the datasource. The pool runs out of connections and that's why it locks up.

Inside a transaction TransactionAwareDataSourceProxy will take care of sharing the connection and therefore releasing the connection from GroovySQL isn't required in that case. See http://jira.grails.org/browse/GRAILS-5454 for details.

This is a better way to use GroovySQL in Grails since the OpenSessionInView (OSIV) interceptor will take care of closing the connection and it will share the same database connection as Hibernate. This method works in both cases: inside transactions and outside transactions.

Sql sql = new Sql(sessionFactory.currentSession.connection())