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

DRJTowerAsked:
Who is Participating?
 
Andyc75Connect With a Mentor Commented:
Not sure if this makes a difference but do you need the JDBC_ prefix when setting the transaction isolation level ?

These are the list of JDBC transaction Isolation levels..
JDBC_TRANSACTION_NONE       
JDBC_TRANSACTION_READ_UNCOMMITTED
JDBC_TRANSACTION_READ_COMMITTED       
JDBC_TRANSACTION_REPEATABLE_READ
JDBC_TRANSACTION_SERIALIZABLE

It might not be recognizing  the mode without the prefix.  Is there a way to check it its being set properly ? The documentation says, it wont work if your in JDBC_TRANSACTION_NONE mode and auto commit is false.


Also have you tried using save points ?

Thats all I can think of....


Example: Set and roll back to savepoints

Statement s = Connection.createStatement();
s.executeUpdate("insert into table1 values ('FIRST')");
Savepoint pt1 = connection.setSavepoint("FIRST SAVEPOINT");
s.executeUpdate("insert into table1 values ('SECOND')";);
connection.rollback(pt1);        // Undoes most recent insert.
s.executeUpdate("insert into table1 values ('THIRD')");
connection.commit();

Open in new window

0
 
Andyc75Commented:
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).
0
 
DRJTowerAuthor Commented:
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.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Andyc75Commented:

"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.
0
 
DRJTowerAuthor Commented:
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
0
 
DRJTowerAuthor Commented:
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!
0
 
Andyc75Commented:
Great, glad that worked out :)

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

From novice to tech pro — start learning today.