Mysql jdbc failed rollback

Question

I have Java code that uses JDBC to connect to a MySQL database, then the code performs some read operations, and then one update using the same connection. If there is an exception, then connection.rollback() is called; if there is no exception, connection.commit() called. At this point, the connection is re-created each time I run my test (i.e., it is not from the pool). My code only ever creates one connection and is used during the test.

The connection used has connection.setAutoCommit(false) , called immediately after creating the connection instance.

For some reason, when an exception is thrown and connection.rollback() is called, it turns out that my update was committed, not a rollback.

Through debugging, I confirmed the following,

• After calling connection.setAutoCommit(false) , connection.getAutoCommit() returns false , as expected. In addition, "Select @@session.autocommit" returns a value of 0 , indicating that auto-commit is turned off as expected.

• Just before calling connection.rollback() the same checks show that auto-commit is disabled, as expected.

• connection.commit() definitely not called and connection.rollback() definitely called.

I also tried to explicitly run the "rollback;" but this did not solve my problem. I also tried to explicitly run the instruction "Set AUTOCOMMIT = 0;" after creating the connection.

All of my tables use the InnoDB storage engine. Through SQL Workbench with automatic commit, rollbacks and commit work as expected.

I am using MySQL version 5.0.91-community-nt. The jdbc driver version for MySQL is 5.1.19. I am using Java 5.

Does anyone have any suggestions as to why my update is getting perfect, although auto-commit is disabled, commit is never called, and rollback is explicitly called?

Greetings.

Answer 1

The code that I refer to in OP is not in one block, it is scattered across classes. To satisfy the requests above for code examples, I prepared one block of code so as not to affect the problem. After the code block was completed, I checked it so that I could replicate the problem. Well, I could not repeat the problem - the rollback worked fine. This made me go through the production code to work out everything he did, beyond the code I put together.

It turns out production code creates and discards temporary tables. In most cases, it deletes temporary tables after performing the update, and it leaves temporary tables regardless of whether or not an exception exists. It turns out that dropping a table in MySQL raises an implicit commit call. Therefore, between my code throwing an exception and my code calling connection.rollback (), it discards the tables that cause the implicit call to make. Therefore, my problem is with auto-commit.