CODEWITHSUNDEEP
javamysqljdbcrollbackautocommit
user1806029
user1806029

Reputation: 101

mysql jdbc unsuccessful rollback

I've got some Java code uses JDBC to connect to a MySQL database, then the code does some read operations then a single update, all using the same connection. If there is an exception, then connection.rollback() is called; if there's no exception, connection.commit() is called. At this stage, the connection is newly created each time I run my test (i.e. it does not come from a pool). My code only ever creates one connection, and it is used throughout the test.

The connection being used has connection.setAutoCommit(false) called immediately after the connection instance is created.

For some reason, when there is an exception and connection.rollback() is called, it turns out that my update has been committed rather than rolled back.

Via debugging, I have confirmed the following,

I have also tried explicitly running the statement "rollback;" but it does not solve my issue. I have also tried explicitly running the statement "Set AUTOCOMMIT = 0;" after creating the connection.

All of my tables use a storage engine of InnoDB. Via SQL Workbench, with auto commit off, rollbacks and commits work as expected.

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

Has anyone got any suggestions as to why my update is getting committed even though auto-commit is off, commit is never called, and rollback is explicitly called?

Cheers.

Upvotes: 4

Views: 1848

Answers (1)

user1806029
user1806029

Reputation: 101

The code I refer to in the OP is not in a single block, it is dispersed across classes. To satisfy the queries above for examples of the code, I prepared a single block of code to illistrate the issue. Once the block of code was completed, I tested it to ensure I could replicate the issue. Well, I couldn't replicate the issue - the rollback worked just fine. This caused me to go through the production code to work out all the things it was doing beyond the block of code I had put together.

It turns out that the production code both creates and drops temporary tables. Most relevant, it drops the temporary tables after performing the update, and it drops the temporary tables whether or not there is an exception. It turns out that dropping a table in MySQL issues an implicit commit call. Hence, in between my code throwing an exception and my code calling connection.rollback(), it drops tables which causes an implicit call to commit. Hence my 'auto commit' issue.

Upvotes: 2

Related Questions