Grails Transaction Issues

Question

I have a Grails Service that is marked as transactional and it does a lot of stuff.

I am adding code to this method and not getting results that I expect when I step through it:

1. I have code that calls .save() that cannot be seen in the MySQL backend until the whole method finishes. This is what I would expect given that the service method is transactional.
2. I have other code that calls .save() that CAN be seen in MySQL before the service method finishes. I don't understand this and I don't understand the disparity between this and 1.
3. I have yet more code that uses the groovy.sql.Sql to insert into the database. I am guessing this is outside of Grails transaction processing, so the fact that this commits before the method ends makes sense. Can I get Grails to manage this inside of the transaction?

Please disabuse me of any errors in my assumptions. Here is some relevant code:

Main Service Method

public void updateDb(Date date) {
        // Create the results
        if (createResults() > 0) {
            createA()
            createB()
        }
}

createA

A a = new a()
a.user = user
a.week = week
a.save()

createB

userWeek = new UserWeek(user: user)
userWeek.number = 1
userWeek.save(flush: true)

createResults

String insert = "insert into ..."
Sql sql = new Sql(dataSource)
sql.execute(insert)

I added flush:true to make it flush, but I now understand that to just flush hibernate but not actually commit the transaction since it is transactional. What am I doing wrong?

Answer 1

You can get groovy.sql.Sql running in the transaction that your service method is using by using the Sql constructor that takes a connection argument instead:

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

This should resolve the problem of data getting committed at different times in the same service method.