This week's book giveaway is in the Design forum.
We're giving away four copies of Design for the Mind and have Victor S. Yocco on-line!
See this thread for details.
Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

setAutoCommit(false) not working

 
marco dres
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1179
Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
marco dres
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4566
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1179
Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The SET command is just a normal MySql syntax - so you should just call it via JDBC.
 
marco dres
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And welcome to the Ranch
 
Vinay Srivastav Kasarla
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic