• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

setAutoCommit(false) not working

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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



 
Ranch Hand
Posts: 1179
Mac OS X Eclipse IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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(....)"?)

 
Bartender
Posts: 4568
9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac OS X Eclipse IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ;-)
 
Marshal
Posts: 80296
434
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And welcome to the Ranch
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:
 
Space pants. Tiny ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/wiki/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic