aspose file tools*
The moose likes JDBC and the fly likes setAutoCommit(false) not working Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "setAutoCommit(false) not working" Watch "setAutoCommit(false) not working" New topic
Author

setAutoCommit(false) not working

marco dres
Greenhorn

Joined: Sep 16, 2010
Posts: 3
Hi,

I'm new to Java and I wrote a little code yesterday which executes some INSERT-Statements on a MySQL-Database via JDBC.

While testing the code I found out the rollback I had in the catch-block didn't work.

To analyze this issue, I made things simple and wrote the following code:



First, I get the highest value of the "num"-column and increase it by 1 for the INSERT.

Then the INSERT-Statement is executed and the content of the table is displayed. Although no commit was made, the SELECT-Command returns the line I've just inserted, so it seems that "setAutoCommit(false)" doesn't work.

The following rollback doesn't change anything, but I assume that's not the problem, because somehow the INSERT-Statement was yet commited.

Does anybody has an idea what's wrong here?

The transaction isolation level in the database is set to "REPEATABLE READ", do I have to change this to be able to set off AutoCommit?

Thanks for your help!
Marco



Rene Larsen
Ranch Hand

Joined: Oct 12, 2001
Posts: 1179

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. To disable autocommit mode, use the following statement:

SET autocommit=0;


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


Regards, Rene Larsen
Dropbox Invite
marco dres
Greenhorn

Joined: Sep 16, 2010
Posts: 3
Rene Larsen wrote:
By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. To disable autocommit mode, use the following statement:

SET autocommit=0;


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


Thanks for your reply.

But I thought "SET autocommit=0" is what "con.setAutoCommit(false)" would do in the database?

If not so, how should I call this statement in my java code? (perhaps "stmt.executeUpdate(....)"?)

Matthew Brown
Bartender

Joined: Apr 06, 2010
Posts: 4465
    
    8

What type of tables are you using? Not all MySQL tables support transactions. E.g. I don't think the default (MyISAM) type does.
Rene Larsen
Ranch Hand

Joined: Oct 12, 2001
Posts: 1179

The SET command is just a normal MySql syntax - so you should just call it via JDBC.
marco dres
Greenhorn

Joined: Sep 16, 2010
Posts: 3
Matthew Brown wrote:What type of tables are you using? Not all MySQL tables support transactions. E.g. I don't think the default (MyISAM) type does.


Thanks a lot!

That was simple....


Changed the table engine to INNODB, now it works ;-)
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39834
    
  28
And welcome to the Ranch
Vinay Srivastav Kasarla
Greenhorn

Joined: Jul 19, 2012
Posts: 2
Hi,

Very good post.

I am facing the same problem. But this time, I have 7 tables, of Engine type InnoDB. As per my requirement, on single button click all the tables shud get loaded from there respective csv files.
I have set connect.setAutoCommit(false) in the beginning of the method and then process all the tables ( insert queries) and then if every things goes well, I am setting connect.commit() else connect.rollback();

Example:

public static void main(){

Connection connect = getConnection();
int insertedValues = 0;
connect.setAutocommit(false);

insertedValues = insert into 1 table
insertedValues = insert into 2 table
insertedValues = insert into 3 table
insertedValues = insert into 4 table
insertedValues = insert into 5 table
insertedValues = insert into 6 table
insertedValues = insert into 7 table

if(insertedValues > 0){
connect.commit();
}
else{
connect.rollback();
}

}


now the problem is when follow the above process, all the other tables are getting rollbacked but data still exists in table 1.
i.e., if there is some issues in inserting table 7
then table 2 table 3 table 4 table 5 table 6 are rollbacked.
but table 1 data still exists.


please help me in this criteria .. thanks in advance.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

There is slight problem with your pseudocode. As it stands, the insertedValues variable will keep just the number of rows inserted to the seventh table. This might be part of the problem.

Further, your pseudocode does not show try/catch clauses you'd need to handle exceptions properly. Commiting/rollbacking based on number of inserted rows is almost certainly wrong.

You should code something like this:
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: setAutoCommit(false) not working