Grails 2 app ported to Grails 3 - PooledConnection has already been closed, solved but not sure why

Question

I was getting a "PooledConnection has already been closed" error when executing a controller with a method that performs a groovy sql query against the dataSource like this

class MyController {
    def sessionFactory
    def viewWeek (ThingCommand cmd) {
        def summary = summaryService()
        def db = new Sql(sessionFactory.currentSession.connection())
        def sqlStrFind = "SELECT * FROM mytable"
        def weekList = db.rows(sqlStrFind)
    }
}

In Grails 2 I had seen runtime issues when ordering a Service.method call after inline use of a sessionFactory connection, but not the order above.

This led me to try refactoring the inline code into a new Service. This made the code work, but I noticed that @Transactional was annotating the new Service class, so I guessed that annotating class MyController with @Transactional might do the same for the Controller - and I was correct.

What I don't understand is why @Transactional makes a difference when I thought that Controllers and Services were transactional by default - or did something change in Grails 3?

BTW Burt Beckwith promoted the use of sessionFactory connection as this gave you a pooled connection

only in services not in controllers and use .withTransaction in other places outside of servies - in a controller - really should be in a service — V H

Gordon Rehling Gordon Rehling · Accepted Answer · 2017-05-17T08:36:22

@virtualdogbert on slack grails-community provided this answer, which answers my question:

Some where in the Grails 3 line they removed the automatic transaction proxies by default, but you could bring them back with a config change [see application.yml - grails: spring: transactioManagement: proxies: false ]

The @Transaction(Grails version) was added because it used an AST transform rather than a spring proxy, which in my mind makes it more reliable. Using the AST transform, was promoted over using the proxies for this reason. The @Transactional AST transform also gives you greater control over how your transactions work.

Even though it's shown in examples I wouldn't use @Transactional in controllers, because it's considered bad practice.

Your business logic should be in services - controllers should just be use for routing, and rendering.

Grails 2 app ported to Grails 3 - PooledConnection has already been closed, solved but not sure why

1 Answers