After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

marekdominiak
Hi,

We are using:
- Bitronix 2.1.3
- Mysql 5.5.30
- ActiveMQ 5.5.1
- Spring 3.1.0.RELEASE
- Hibernate 4.1.9.Final

The problem I'm desribing here is a behaviour which has appeared after we started to use global transactions - after we started to use MysqlXA and Bitronix.

We are having some big problems with our application related to what happens after we get a deadlock in Mysql. If we wouldn't have any problems this should work that after we get a deadlock Mysql will detect it and rollback the transaction, afterwords other transactions can use this connection to open transactions and perform queries.

In our case however, after getting a deadlock the connection seems to be in corrupted state: every query issued from this connection (in new transactions) is giving us an exception.


Deadlock exception:
---------------------------------------------------------------------------------------------
ERROR SqlExceptionHelper - Deadlock found when trying to get lock; try restarting transaction
ERROR BatchingBatch - HHH000315: Exception executing batch [Deadlock found when trying to get lock; try restarting transaction]
WARN  BitronixTransaction - error delisting resource, assuming unilateral rollback: an XAResourceHolderState with uniqueName=mysqlDatasourceDB XAResource=com.mysql.jdbc.jdbc2.optional.JDBC4SuspendableXAConnection@6075f9d3 (ended) with XID a Bitronix XID [74657374732D62746D0000000007ED70E000000004 : 74657374732D62746D0000000007ED70E300000005]
bitronix.tm.internal.BitronixSystemException: cannot delist an XAResourceHolderState with uniqueName=mysqlDatasourceDB XAResource=com.mysql.jdbc.jdbc2.optional.JDBC4SuspendableXAConnection@6075f9d3 (ended) with XID a Bitronix XID [74657374732D62746D0000000007ED70E000000004 : 74657374732D62746D0000000007ED70E300000005], error=!invalid error code (0)!
        at bitronix.tm.BitronixTransaction.delistResource(BitronixTransaction.java:203)
        at bitronix.tm.BitronixTransaction.delistUnclosedResources(BitronixTransaction.java:456)
        at bitronix.tm.BitronixTransaction.rollback(BitronixTransaction.java:311)
        at bitronix.tm.BitronixTransaction.commit(BitronixTransaction.java:240)
        at bitronix.tm.BitronixTransactionManager.commit(BitronixTransactionManager.java:143)
        at org.springframework.transaction.jta.JtaTransactionManager.doCommit(JtaTransactionManager.java:1010)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:147)
        at no.mintra.common.shared.testframework.integrationtest.AbstractJpaIntegrationTest.doInTransaction(AbstractJpaIntegrationTest.java:398)
        at no.mintra.trainingportal.application.competence.user.CompetenceUserRelationDeadlockTest.access$16(CompetenceUserRelationDeadlockTest.java:1)
        at no.mintra.trainingportal.application.competence.user.CompetenceUserRelationDeadlockTest$3.run(CompetenceUserRelationDeadlockTest.java:170)
Caused by: com.mysql.jdbc.jdbc2.optional.MysqlXAException: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
        at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:607)
        at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:586)
        at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.end(MysqlXAConnection.java:481)
        at com.mysql.jdbc.jdbc2.optional.SuspendableXAConnection.end(SuspendableXAConnection.java:117)
        at bitronix.tm.internal.XAResourceHolderState.end(XAResourceHolderState.java:172)
        at bitronix.tm.internal.XAResourceManager.delist(XAResourceManager.java:133)
        at bitronix.tm.BitronixTransaction.delistResource(BitronixTransaction.java:186)
        ... 11 more
org.springframework.transaction.UnexpectedRollbackException: JTA transaction unexpectedly rolled back (maybe due to a timeout); nested exception is bitronix.tm.internal.BitronixRollbackException: RuntimeException thrown during beforeCompletion cycle caused transaction rollback
        at org.springframework.transaction.jta.JtaTransactionManager.doCommit(JtaTransactionManager.java:1013)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:147)
        at no.mintra.common.shared.testframework.integrationtest.AbstractJpaIntegrationTest.doInTransaction(AbstractJpaIntegrationTest.java:398)
        at no.mintra.trainingportal.application.competence.user.CompetenceUserRelationDeadlockTest.access$16(CompetenceUserRelationDeadlockTest.java:1)
        at no.mintra.trainingportal.application.competence.user.CompetenceUserRelationDeadlockTest$3.run(CompetenceUserRelationDeadlockTest.java:170)
Caused by: bitronix.tm.internal.BitronixRollbackException: RuntimeException thrown during beforeCompletion cycle caused transaction rollback
        at bitronix.tm.BitronixTransaction.commit(BitronixTransaction.java:241)
        at bitronix.tm.BitronixTransactionManager.commit(BitronixTransactionManager.java:143)
        at org.springframework.transaction.jta.JtaTransactionManager.doCommit(JtaTransactionManager.java:1010)
        ... 6 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.LockAcquisitionException: Deadlock found when trying to get lock; try restarting transaction
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1377)
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300)
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1306)
        at org.hibernate.ejb.AbstractEntityManagerImpl$CallbackExceptionMapperImpl.mapManagedFlushFailure(AbstractEntityManagerImpl.java:1500)
        at org.hibernate.engine.transaction.synchronization.internal.SynchronizationCallbackCoordinatorImpl.beforeCompletion(SynchronizationCallbackCoordinatorImpl.java:109)
        at org.hibernate.engine.transaction.synchronization.internal.RegisteredSynchronization.beforeCompletion(RegisteredSynchronization.java:53)
        at bitronix.tm.BitronixTransaction.fireBeforeCompletionEvent(BitronixTransaction.java:532)
        at bitronix.tm.BitronixTransaction.commit(BitronixTransaction.java:235)
        ... 8 more
Caused by: org.hibernate.exception.LockAcquisitionException: Deadlock found when trying to get lock; try restarting transaction
        // REMOVED FOR CLARITY
        at $Proxy282.executeBatch(Unknown Source)
        // REMOVED FOR CLARITY
        at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:402)
        at org.hibernate.engine.transaction.synchronization.internal.SynchronizationCallbackCoordinatorImpl.beforeCompletion(SynchronizationCallbackCoordinatorImpl.java:104)
        ... 11 more
Caused by: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2009)
        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1454)
        at com.mysql.jdbc.jdbc2.optional.StatementWrapper.executeBatch(StatementWrapper.java:722)
        // REMOVED FOR CLARITY
        at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
        ... 24 more
---------------------------------------------------------------------------------------------


Exceptions received afterwords:
---------------------------------------------------------------------------------------------
WARN  SqlExceptionHelper - SQL Error: 0, SQLState: null
ERROR SqlExceptionHelper - error enlisting a JdbcConnectionHandle of a JdbcPooledConnection from datasource mysqlDatasourceDB in state ACCESSIBLE with usage count 1 wrapping com.mysql.jdbc.jdbc2.optional.JDBC4SuspendableXAConnection@5372194e on com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@48f0aab8
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: error enlisting a JdbcConnectionHandle of a JdbcPooledConnection from datasource mysqlDatasourceDB in state ACCESSIBLE with usage count 1 wrapping com.mysql.jdbc.jdbc2.optional.JDBC4SuspendableXAConnection@5372194e on com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@48f0aab8
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1377)
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300)
        at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1387)
        at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:108)
        // REMOVED FOR CLARITY
Caused by: org.hibernate.exception.GenericJDBCException: error enlisting a JdbcConnectionHandle of a JdbcPooledConnection from datasource mysqlDatasourceDB in state ACCESSIBLE with usage count 1 wrapping com.mysql.jdbc.jdbc2.optional.JDBC4SuspendableXAConnection@5372194e on com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@48f0aab8
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
        at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:146)
        at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
        at $Proxy280.prepareStatement(Unknown Source)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:84)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:77)
        at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:196)
        at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1289)
        at org.hibernate.internal.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:401)
        at org.hibernate.ejb.QueryImpl.internalExecuteUpdate(QueryImpl.java:194)
        at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:99)
        ... 34 more
Caused by: java.sql.SQLException: error enlisting a JdbcConnectionHandle of a JdbcPooledConnection from datasource mysqlDatasourceDB in state ACCESSIBLE with usage count 1 wrapping com.mysql.jdbc.jdbc2.optional.JDBC4SuspendableXAConnection@5372194e on com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@48f0aab8
        at bitronix.tm.resource.jdbc.JdbcConnectionHandle.enlistResource(JdbcConnectionHandle.java:87)
        at bitronix.tm.resource.jdbc.JdbcConnectionHandle.prepareStatement(JdbcConnectionHandle.java:242)
        at sun.reflect.GeneratedMethodAccessor82.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at bitronix.tm.resource.jdbc.BaseProxyHandlerClass.invoke(BaseProxyHandlerClass.java:64)
        at $Proxy115.prepareStatement(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor81.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138)
        ... 44 more
Caused by: bitronix.tm.internal.BitronixSystemException: cannot enlist an XAResourceHolderState with uniqueName=mysqlDatasourceDB XAResource=com.mysql.jdbc.jdbc2.optional.JDBC4SuspendableXAConnection@5372194e with XID a Bitronix XID [74657374732D62746D000000000803389400000009 : 74657374732D62746D00000000080338BC0000000A], error=XAER_RMFAIL
        at bitronix.tm.BitronixTransaction.enlistResource(BitronixTransaction.java:139)
        at bitronix.tm.resource.common.TransactionContextHelper.enlistInCurrentTransaction(TransactionContextHelper.java:69)
        at bitronix.tm.resource.jdbc.JdbcConnectionHandle.enlistResource(JdbcConnectionHandle.java:85)
        ... 54 more
Caused by: com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ROLLBACK ONLY state
        at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:603)
        at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:586)
        at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.start(MysqlXAConnection.java:526)
        at com.mysql.jdbc.jdbc2.optional.SuspendableXAConnection.start(SuspendableXAConnection.java:158)
        at bitronix.tm.internal.XAResourceHolderState.start(XAResourceHolderState.java:220)
        at bitronix.tm.internal.XAResourceManager.enlist(XAResourceManager.java:111)
        at bitronix.tm.BitronixTransaction.enlistResource(BitronixTransaction.java:130)
        ... 56 more
---------------------------------------------------------------------------------------------


This is a big problem for us because we need to restart the whole application server (Tomcat), and this shouldn't be necessary.

One thing is that this is probably the problem with underlying MysqlXA driver, but because nothing big happens in this matter we need to try to recover application after this exception by ourselves. I could think of two hacks:
1. After getting a deadlock try to remove the corrupted connection from the pool.
2. After getting a deadlock reset the connection pool.

Of course 1. would be preferable but I'm not sure if it is possible to achieve. How do I get a handle to the connection associated to transaction in which we got a deadlocK? Is is possible? If yes, how to remove this connection from the pool? I couldn't find any methods in API of bitronix.tm.resource.jdbc.PoolingDataSource to remove a single connection (nice encapsulation!).

Second hack I have tried and it did work but this is risky because if there were many clients at this very momenent they will all get an exception (even if they have used not corrupted connection).


Is there a better solution?
Maybe our configuration is wrong?
Should I bother Mysql guys?

Regards
Marek Dominiak
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

Ludovic Orban-2
Hi,

This is a complex problem. I don't have everything I need to give a definitive answer but I'm tempted to blame the problem onto MySQL's driver. By the way, what version of MySQL and MySQL JDBC driver are you using?

Apparently during delistment, MySQL detected a deadlock and throws a XAException which exactly is what it should do. The exception's message even specifies that: "...MysqlXAException: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected". First problem: the XAException's error code was not set to XA_RBDEADLOCK but left to zero: error=!invalid error code(0)!". This forces BTM to throw a SystemException and to openly report this hazardous case instead of silenly handling it. But we're just talking about extra warnings here, nothing too serious.

The second problem is the real pain. My understanding is that MySQL has put its connection in some kind of ROLLBACK_ONLY state just before the MysqlXAException: XA_RBDEADLOCK was thrown. This is incorrect: XA_RBDEADLOCK means that the transaction has been completely rolled back and no trace of it should exist anymore, the connection should have been reset to a clean state.


Since I don't have debug logs and I cannot reproduce this problem myself, it could be that BTM did something wrong too which caused MySQL to get confused. Or there could be some kind of extra cleanup it could do, I don't know.

Regarding cleaning up the broken connection, I assume that if you disable JDBC 4 connection testing and use a sensible test query (ie: one that would make MySQL throw its XAER_RMFAIL exception) that hopefully should do the trick. If that falls short, there's little alternative to resetting the whole pool.


If you can reproduce this problem easily, I'd be happy if you could collect the debug logs or send a simple program that I could use myself to reproduce the problem.

--
Ludovic


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

marekdominiak
This post was updated on .
Hi Ludovic,

Thank you for your prompt reponse.

We use
- Mysql in version 5.5.30 (I use Ubuntu as OS).
- mysql-connector is in version: 5.1.12 but I got the same exception with 5.1.25.

I have an integration test in which I am able to reproduce the problem quite easily. This was much harder though with logging with DEBUG level, but I got the logs from one execution.

I have tried to play a little bit with testQuery and enabling JDBC 4 connection testing (it was disabled in our config) but the problem remained. I was mistaken when I said that the exception occurs after sql issuing - the exception is thrown during commit phase. Each transaction commit fails for this connection after getting deadlock on this connection.

Reset of the whole pool is working but I can't say this is a good solution (I would prefer not add this hacky hack to our app:))

I have attached the logs for this test execution.
- bm.log.debug contains logs only from bitronix.tm package
- app.log.debug contains stack traces which we get in application layer

Logs from execution after-deadlock-problem.zip.zip

I hope you will be able to see something there.


Regards
Marek Dominiak
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

marekdominiak
Hi Ludovic,


I hope you will find some time to look at these logs which I've uploaded.

I'm aware of the fact that to fix this issue could be impossible, so I started to look at some alternatives. Unfortunately the testQuery doesn't find anything wrong with the broken connection, so I started to create a simple AOP aspect which after getting exception (during the transaction) would make it possible to get the handle the the broken connection. With help of "shareTransactionConnections" set to true I'm able to achieve just that.

But here's a little problem, how to force this connection to be removed from the pool?
I'm able to unwrap the handle I've got and then I have a handle to JDBC4ConnectionWrapper.
Invoking close method on this connection closes the physical connection, and the pool can't use it anymore. I have run some tests with producing the deadlock (with Jmeter to stress this solution) and it seems that it's working fine.

But anyway I'm not sure if this "solution" is safe to use.

What do you think about that?

Regards
Marek Dominiak
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

Brett Wooldridge-2
Hi Marek,

What you're doing should work, without any ill-effects.  It is unfortunately that you
have to result to something like this.  I did have one suggestion, without your AOP
aspect ... can you try setting the test query to the statement 'ROLLBACK'?

When the test query runs, the connection is basically in local transaction mode,
and because the connection is coming fresh from the pool, there should actually
be nothing to rollback.  You can try 'COMMIT' as the test query for that matter.

I would be curious whether either of these causes the exception to throw ... at the
timing in which it should, which is before being handed back to the user.

I'm investigating whether there is a way for us to detect this mysql exception as a
special case for handling in the pool, I'll let you know what I find.

Regards,
Brett



On Wed, May 29, 2013 at 6:28 PM, marekdominiak <[hidden email]> wrote:
Hi Ludovic,


I hope you will find some time to look at these logs which I've uploaded.

I'm aware of the fact that to fix this issue could be impossible, so I
started to look at some alternatives. Unfortunately the testQuery doesn't
find anything wrong with the broken connection, so I started to create a
simple AOP aspect which after getting exception (during the transaction)
would make it possible to get the handle the the broken connection. With
help of "shareTransactionConnections" set to true I'm able to achieve just
that.

But here's a little problem, how to force this connection to be removed from
the pool?
I'm able to unwrap the handle I've got and then I have a handle to
JDBC4ConnectionWrapper.
Invoking close method on this connection closes the physical connection, and
the pool can't use it anymore. I have run some tests with producing the
deadlock (with Jmeter to stress this solution) and it seems that it's
working fine.

But anyway I'm not sure if this "solution" is safe to use.

What do you think about that?

Regards
Marek Dominiak




--
View this message in context: http://bitronix-transaction-manager.10986.n7.nabble.com/After-deadlock-we-get-XAER-RMFAIL-exception-on-all-subsequent-db-access-Corrupted-connection-is-not--tp1470p1475.html
Sent from the Bitronix Transaction Manager mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

marekdominiak
Hi Brett,

Thank you for your help!

You've saved me from using this hacky "solution". I wasn't aware of the fact that during the testing connection phase the connection is in local mode (I've assumed that I can't use any of ROLLBACK or COMMIT). Of course this makes sense now.

I've tested both ROLLBACK and COMMIT as testQuery and they're both working. The exception is thrown and it's removed from the pool.

Thanks again!

ps. I think we could try to add this information for the time being to the Bitronix FAQ for users which are using Mysql as a XaResuorce.


Regards
Marek Dominiak

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

Brett Wooldridge-2
Marek,

Wow!  That was just a wild guess, but I'm glad it worked.  In general, I would feel
safer using ROLLBACK rather than COMMIT in this case. :-)  Even though both
should basically do nothing.

Regards,
Brett



On Wed, May 29, 2013 at 9:24 PM, marekdominiak <[hidden email]> wrote:
Hi Brett,

Thank you for your help!

You've saved me from using this hacky "solution". I wasn't aware of the fact
that during the testing connection phase the connection is in local mode
(I've assumed that I can't use any of ROLLBACK or COMMIT). Of course this
makes sense now.

I've tested both ROLLBACK and COMMIT as testQuery and they're both working.
The exception is thrown and it's removed from the pool.

Thanks again!

ps. I think we could try to add this information for the time being to the
Bitronix FAQ for users which are using Mysql as a XaResuorce.


Regards
Marek Dominiak





--
View this message in context: http://bitronix-transaction-manager.10986.n7.nabble.com/After-deadlock-we-get-XAER-RMFAIL-exception-on-all-subsequent-db-access-Corrupted-connection-is-not--tp1470p1477.html
Sent from the Bitronix Transaction Manager mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

Brett Wooldridge-2
In reply to this post by marekdominiak
Marek,

Make sure you test it well.  I'm glad it works, but I feel a little unsure how MySQL
might deal with a mix of local and global transactions on the same connection.  I
think it *should* be OK, but just test it well.  If you can watch transactions on the
MySQL-side (through the log somehow?), you can verify that in-flight global
transactions are not being committed or rolled-back by the local transactions.
Some DB/drivers allow interleaving and some do not.

Brett



On Wed, May 29, 2013 at 9:24 PM, marekdominiak <[hidden email]> wrote:
Hi Brett,

Thank you for your help!

You've saved me from using this hacky "solution". I wasn't aware of the fact
that during the testing connection phase the connection is in local mode
(I've assumed that I can't use any of ROLLBACK or COMMIT). Of course this
makes sense now.

I've tested both ROLLBACK and COMMIT as testQuery and they're both working.
The exception is thrown and it's removed from the pool.

Thanks again!

ps. I think we could try to add this information for the time being to the
Bitronix FAQ for users which are using Mysql as a XaResuorce.


Regards
Marek Dominiak





--
View this message in context: http://bitronix-transaction-manager.10986.n7.nabble.com/After-deadlock-we-get-XAER-RMFAIL-exception-on-all-subsequent-db-access-Corrupted-connection-is-not--tp1470p1477.html
Sent from the Bitronix Transaction Manager mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

marekdominiak
Hi Brett,

Thank you again Brett.

You're right, I shouldn't get so excited about this and carefully test the suggested solution.


Unfortunately I haven't found any log tool for doing this trace on Mysql server side.

However I have tested some scenarios and I don't see anything strange. Rollbacks and commits for global transactions are working as they should. I have set the connection pool to be small and debug the connection on the way, so I am sure that the same connection was used many times for global transactions and for local transactions (to perform test query both for COMMIT and ROLLBACK), rollbacks and commits are working fine.
(after commits the records are stored as they should and jms messages are delivered, after rollbacks there are no changes in DB and the elements weren't added to the jms queue).

Do you think that these tests are enough to say that these testQueries are quite safe to use?

Best regards
Marek Dominiak
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

Brett Wooldridge-2
Hi Marek,

It sounds like it's working fine.  I assume you are using ROLLBACK as the
test query.  Luckily you're using a database that seems to support interleaved
transactions.  If it were PostgreSQL, you'd be out of luck as it doesn't support
interleaving; but then again, it probably doesn't have this strange deadlock
semantic either.

I assume you've tested it under some amount of load, correct?  If so, I'd go 
with it until you encounter an issue (which might be never).

Regards,
Brett



On Thu, May 30, 2013 at 10:58 PM, marekdominiak <[hidden email]> wrote:
Hi Brett,

Thank you again Brett.

You're right, I shouldn't get so excited about this and carefully test the
suggested solution.


Unfortunately I haven't found any log tool for doing this trace on Mysql
server side.

However I have tested some scenarios and I don't see anything strange.
Rollbacks and commits for global transactions are working as they should. I
have set the connection pool to be small and debug the connection on the
way, so I am sure that the same connection was used many times for global
transactions and for local transactions (to perform test query both for
COMMIT and ROLLBACK), rollbacks and commits are working fine.
(after commits the records are stored as they should and jms messages are
delivered, after rollbacks there are no changes in DB and the elements
weren't added to the jms queue).

Do you think that these tests are enough to say that these testQueries are
quite safe to use?

Best regards
Marek Dominiak



--
View this message in context: http://bitronix-transaction-manager.10986.n7.nabble.com/After-deadlock-we-get-XAER-RMFAIL-exception-on-all-subsequent-db-access-Corrupted-connection-is-not--tp1470p1481.html
Sent from the Bitronix Transaction Manager mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

Ludovic Orban-2
Quite frankly I think there is something fishy in mysql. I would report that problem to the mysql developers to get a definitive answer on this, and to make them aware of the problem.

You could also file a bug for the incorrect XAException error code while you're at it.


On Thu, May 30, 2013 at 4:08 PM, Brett Wooldridge <[hidden email]> wrote:
Hi Marek,

It sounds like it's working fine.  I assume you are using ROLLBACK as the
test query.  Luckily you're using a database that seems to support interleaved
transactions.  If it were PostgreSQL, you'd be out of luck as it doesn't support
interleaving; but then again, it probably doesn't have this strange deadlock
semantic either.

I assume you've tested it under some amount of load, correct?  If so, I'd go 
with it until you encounter an issue (which might be never).

Regards,
Brett



On Thu, May 30, 2013 at 10:58 PM, marekdominiak <[hidden email]> wrote:
Hi Brett,

Thank you again Brett.

You're right, I shouldn't get so excited about this and carefully test the
suggested solution.


Unfortunately I haven't found any log tool for doing this trace on Mysql
server side.

However I have tested some scenarios and I don't see anything strange.
Rollbacks and commits for global transactions are working as they should. I
have set the connection pool to be small and debug the connection on the
way, so I am sure that the same connection was used many times for global
transactions and for local transactions (to perform test query both for
COMMIT and ROLLBACK), rollbacks and commits are working fine.
(after commits the records are stored as they should and jms messages are
delivered, after rollbacks there are no changes in DB and the elements
weren't added to the jms queue).

Do you think that these tests are enough to say that these testQueries are
quite safe to use?

Best regards
Marek Dominiak



--
View this message in context: http://bitronix-transaction-manager.10986.n7.nabble.com/After-deadlock-we-get-XAER-RMFAIL-exception-on-all-subsequent-db-access-Corrupted-connection-is-not--tp1470p1481.html
Sent from the Bitronix Transaction Manager mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email




Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

marekdominiak
Yeah, I am able to repeat this problem almost every time so for sure I will file a bug (after holidays which has just began ...)

Thanks guys for the great work and for helping with this case.

Regards
Marek
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: After deadlock we get XAER_RMFAIL exception on all subsequent db access. Corrupted connection is not removed from the PoolingDataSource.

makeachange
Hi guys, I am hitting upon this exact problem with our app server - a big show stopper at the minute! This is with MySQL connector 5.1.26, so still not fixed :(

Anyway thanks for this info, it helped me at least diagnose the issue we've been suffering. Marek, did you get around to filing a bug report with MySQL?

Regards
Nick


marekdominiak wrote
Yeah, I am able to repeat this problem almost every time so for sure I will file a bug (after holidays which has just began ...)

Thanks guys for the great work and for helping with this case.

Regards
Marek
Loading...