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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Andyc75Software ArchitectCommented:
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
Andyc75Software ArchitectCommented:

"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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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
Andyc75Software ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Andyc75Software ArchitectCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.