datasources and withTransaction

[Alan Bowsher-2]

Using the 2.0 built-in multiple datasources mechanism, can I use withTransaction in a controller?  Assuming that I am not using XA (because Atomikos fails for some reason with my attempts using MySQL 5.5), and I am only working with a single datasource at a time, how do I tell withTransaction what database to start a transaction on?  I tried MyDomain.mydatasource.withTransaction, but Grails complained about not having a transaction manager defined.

If this does not work, and you use a Service with the static datasource, how can you re-use the service for other datasources (i.e we have many datasources and use the correct one dynamically with our domain objects...)

[Dave Klein-2]

Did you ever find the correct way to call withTransaction on an alternate datasource?

Thanks,
Dave

On Wed, Feb 15, 2012 at 10:44 AM, Alan Bowsher <[hidden email]> wrote:

Using the 2.0 built-in multiple datasources mechanism, can I use withTransaction in a controller?  Assuming that I am not using XA (because Atomikos fails for some reason with my attempts using MySQL 5.5), and I am only working with a single datasource at a time, how do I tell withTransaction what database to start a transaction on?  I tried MyDomain.mydatasource.withTransaction, but Grails complained about not having a transaction manager defined.

If this does not work, and you use a Service with the static datasource, how can you re-use the service for other datasources (i.e we have many datasources and use the correct one dynamically with our domain objects...)

[Alan Bowsher-2]

No - I would really like to understand how to do this!!  I can manually grab the session factories and create a transaction, but then I'd have wrap everything in a try/catch/finally to do rollbacks, and somehow handle joining transactions... all the stuff that withTransaction does for you.  Is there a way to create a transaction manager for each datasource so I could do  MyDomain.mydatasource.withTransaction?

We've had other issues recently with Atomikos/MySQL... in certain cases, when using withNewSession, a save() creates a new connection (the Hibernate ConnectionManager's current connection is null).  Then it creates a new xa branch qualifier, so that two XA transactions are open with the same global transaction identifier.  If no other database queries happen after the withNewSession completes, both xa branches are prepared and committed.  But if another database interaction happens before the commit, we get an error from mysql:

java.sql.SQLException: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state

It's tricky to chase down and figure out the exact combination of events that cause it, but this is as far as we've gotten.

Anyway, it's killing us right now... and we don't really and truly need XA, we just did it b/c it made transaction management easier.

Any help would be appreciated... 

Thanks

On Wed, Jun 20, 2012 at 2:40 PM, Dave Klein <[hidden email]> wrote:

Did you ever find the correct way to call withTransaction on an alternate datasource?

Thanks,
Dave

On Wed, Feb 15, 2012 at 10:44 AM, Alan Bowsher <[hidden email]> wrote:

Using the 2.0 built-in multiple datasources mechanism, can I use withTransaction in a controller?  Assuming that I am not using XA (because Atomikos fails for some reason with my attempts using MySQL 5.5), and I am only working with a single datasource at a time, how do I tell withTransaction what database to start a transaction on?  I tried MyDomain.mydatasource.withTransaction, but Grails complained about not having a transaction manager defined.

If this does not work, and you use a Service with the static datasource, how can you re-use the service for other datasources (i.e we have many datasources and use the correct one dynamically with our domain objects...)

[Alan Bowsher-2]

I may have found a way to make it work.  I remembered the old Datasources plugin created txn mgrs for each datasource.  I injected on into my service, then assigned the transactionManager for my class to it (something like  MyDomain[mydatasource].transactionManager = injected var...  then withTransaction worked.

Just have to find a way to connect each class/datasource combination... ugh.  Seems like this should be part of the datasources built-in...

On Thu, Aug 16, 2012 at 5:16 PM, Alan Bowsher <[hidden email]> wrote:

No - I would really like to understand how to do this!!  I can manually grab the session factories and create a transaction, but then I'd have wrap everything in a try/catch/finally to do rollbacks, and somehow handle joining transactions... all the stuff that withTransaction does for you.  Is there a way to create a transaction manager for each datasource so I could do  MyDomain.mydatasource.withTransaction?

We've had other issues recently with Atomikos/MySQL... in certain cases, when using withNewSession, a save() creates a new connection (the Hibernate ConnectionManager's current connection is null).  Then it creates a new xa branch qualifier, so that two XA transactions are open with the same global transaction identifier.  If no other database queries happen after the withNewSession completes, both xa branches are prepared and committed.  But if another database interaction happens before the commit, we get an error from mysql:

java.sql.SQLException: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state

It's tricky to chase down and figure out the exact combination of events that cause it, but this is as far as we've gotten.

Anyway, it's killing us right now... and we don't really and truly need XA, we just did it b/c it made transaction management easier.

Any help would be appreciated... 

Thanks

On Wed, Jun 20, 2012 at 2:40 PM, Dave Klein <[hidden email]> wrote:

Did you ever find the correct way to call withTransaction on an alternate datasource?

Thanks,
Dave

On Wed, Feb 15, 2012 at 10:44 AM, Alan Bowsher <[hidden email]> wrote:

Using the 2.0 built-in multiple datasources mechanism, can I use withTransaction in a controller?  Assuming that I am not using XA (because Atomikos fails for some reason with my attempts using MySQL 5.5), and I am only working with a single datasource at a time, how do I tell withTransaction what database to start a transaction on?  I tried MyDomain.mydatasource.withTransaction, but Grails complained about not having a transaction manager defined.

If this does not work, and you use a Service with the static datasource, how can you re-use the service for other datasources (i.e we have many datasources and use the correct one dynamically with our domain objects...)

[Alan Bowsher-2]

Well, found a way to do it... just pick a class that exists in all the datasources, and set the transaction managers on each one during BootStrap:

        myDbs.each {

            def transactionManager = grailsApplication.mainContext.getBean("transactionManager_$it")

            MyDomain["${it}"].transactionManager = transactionManager

        }

On Thu, Aug 16, 2012 at 5:57 PM, Alan Bowsher <[hidden email]> wrote:

I may have found a way to make it work.  I remembered the old Datasources plugin created txn mgrs for each datasource.  I injected on into my service, then assigned the transactionManager for my class to it (something like  MyDomain[mydatasource].transactionManager = injected var...  then withTransaction worked.

Just have to find a way to connect each class/datasource combination... ugh.  Seems like this should be part of the datasources built-in...

On Thu, Aug 16, 2012 at 5:16 PM, Alan Bowsher <[hidden email]> wrote:

No - I would really like to understand how to do this!!  I can manually grab the session factories and create a transaction, but then I'd have wrap everything in a try/catch/finally to do rollbacks, and somehow handle joining transactions... all the stuff that withTransaction does for you.  Is there a way to create a transaction manager for each datasource so I could do  MyDomain.mydatasource.withTransaction?

We've had other issues recently with Atomikos/MySQL... in certain cases, when using withNewSession, a save() creates a new connection (the Hibernate ConnectionManager's current connection is null).  Then it creates a new xa branch qualifier, so that two XA transactions are open with the same global transaction identifier.  If no other database queries happen after the withNewSession completes, both xa branches are prepared and committed.  But if another database interaction happens before the commit, we get an error from mysql:

java.sql.SQLException: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state

It's tricky to chase down and figure out the exact combination of events that cause it, but this is as far as we've gotten.

Anyway, it's killing us right now... and we don't really and truly need XA, we just did it b/c it made transaction management easier.

Any help would be appreciated... 

Thanks

On Wed, Jun 20, 2012 at 2:40 PM, Dave Klein <[hidden email]> wrote:

Did you ever find the correct way to call withTransaction on an alternate datasource?

Thanks,
Dave

On Wed, Feb 15, 2012 at 10:44 AM, Alan Bowsher <[hidden email]> wrote:

Using the 2.0 built-in multiple datasources mechanism, can I use withTransaction in a controller?  Assuming that I am not using XA (because Atomikos fails for some reason with my attempts using MySQL 5.5), and I am only working with a single datasource at a time, how do I tell withTransaction what database to start a transaction on?  I tried MyDomain.mydatasource.withTransaction, but Grails complained about not having a transaction manager defined.

If this does not work, and you use a Service with the static datasource, how can you re-use the service for other datasources (i.e we have many datasources and use the correct one dynamically with our domain objects...)