Link to home
Start Free TrialLog in
Avatar of DRJTower
DRJTower

asked on

JDBC setAutoCommit(false) still commits if commit() not called

I am having trouble getting JDBC to rollback a transaction.
If I set autoCommit to false and run my updates without ever calling commit
it still commits, and even if I call rollback it still commits the transaction.

What am I doing wrong?

Here is my code ( It commits if I don't call commit() and even when I call rollback() )
//connect to database
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("myServer", "myUser","myPassword");
System.out.println("connected");
			
//Setup ACID transaction
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
con.setAutoCommit(false);
			
Statement stat = con.createStatement();
stat.executeUpdate("my sql script");
			
con.rollback();

Open in new window

Avatar of Andyc75
Andyc75
Flag of Canada image

With MySql, to begin your transaction use "start transaction"

Then run your SQL commands.

Then use "commit" to commit your transaction, or "rollback" to rollback the transaction.

http://dev.mysql.com/doc/refman/5.0/en/commit.html

Also make sure the table your using is a transactional table.  (Tables created with the MyISAM enigine are non-transactional).
Avatar of DRJTower
DRJTower

ASKER

That is the general process that I do with JDBC

I do not see a "start transaction" option anywhere in the Connection object,
so either it does that automatically, or I am missing something.

I don't want to use just mysql, because with JDBC I can put java code in between the queries
and updates of the transaction. (At least I think I can)




The table is a transactional table.

"start_transaction" is a sql command you run just before your sql commands.

It begins the transaction.

Then you can issue the "commit" or "rollback" to complete the transaction.
Thanks for your help, but I do understand how to do transactions in sql.

My question is with JDBC in java.
Its my understanding that the JDBC library handles all sql code generation,
so I do not need to send any sql commands to the server (except, of course, the query and update scripts)

Maby I put this question in the wrong section.  I am new to this site, is it possible to add this question to a different zone?

Thanks again for your help though
ASKER CERTIFIED SOLUTION
Avatar of Andyc75
Andyc75
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I tried using savepoints and its works perfectly now

However, using this method, is JDBC committing the updates and then undoing what it committed,
or is it working the same way sql transactions do?

I ask because I do not want any data changes from within the transaction to be seen by
any other transactions until this one commits.

I set the Isolation level with this command from my code
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

Thanks for your help!
Great, glad that worked out :)

As far as I understand from the documentation, all the transactions will be pending until the commit is issued.
I confirmed that it works the way I expected it to.  Thanks for your help!