Auto Commit option fails to work

[Renjith Mohan]

I have a class like this:

Class DemoClass{
Connection conn1; // connection variable to DB 1
Connection conn2; // connection variable to DB 2

public void getConnection(){
// connects to database and set the connection variable to conn1 and conn2

// after getting conn1 and conn2, i disabled the auto-commit mode as:
conn1.setAutoCommit(false);
conn2.setAutoCommit(false);
}

public boolean someProcess(){
// here I am doing some sql updates and insert
}

public boolean someOtherProcess(){
// a different sql queries to go
}

public void call(){

try{
someProcess();
someOtherProcess();
conn1.commit();
conn2.commit();
}
catch(Exception e){
//am rolling back the db operations
conn1.rollback();
conn2.rollback();
}
}
}

If any sql exceptions are raised, the rollback is not working.....some of the data are updated in the database.
What could the possible chance ???
Thanks in advance.

[Balu Sadhasivam]

If any sql exceptions are raised, the rollback is not working.....some of the data are updated in the database.
What could the possible chance ???

Where is the sql exception raised in this case , which method ? someProcess(); someOtherProcess();

Usually data will be committed if you close the connection even when exceptions happened ( before rollback is called). Can you provide where you close the connection ?

[Renjith Mohan]

Actually am testing the application...so Iam making exceptions in both someProcess() and someOtherProcess()

try{
//sql code
}
catch(Exception e){
//here am rolling back the db operations
}
finally{
//am closing the Statement object
}

And connections are closed in the call method ()?

So, in case of exceptions, first it enters catch block. there it roll back the process. then it enters finally block..close the statement block..then return to call() method, where the connection is closed.

[Balu Sadhasivam]

Are you able to ascertain that the code reaches the catch block and calls conn1.rollback() methods.. also i would recommend to close the connection close directly in fianlly block.

There are 2 ways a typical update to table happens
1) calling commit() statement
2) closing the connection ( without rollback)

so check if any of the above conditions are satisfied in your code. Without anlaysing your complete code cant predict anything more.

[Campbell Ritchie]

"Renjith r" please read the important administrative private message which I am just sending you.

[Renjith Mohan]

class DatabaseConnect
{
Connection conn1, conn2;
//PreparedStatement pstmt1, pstmt2;
public static void main(String[] args) throws Exception
{
new DatabaseConnect().init();
}

public void init(){
try{
handle();
}
catch(Exception e){
e.printStackTrace();
}
}

public void handle() throws Exception{
try{
System.out.println("***Calling connect()***");
connect();
System.out.println("***Calling doQuery()***");
doQuery();
System.out.println("***Calling doAnotherQuery()***");
doAnotherQuery();
conn1.commit();
}
catch(Exception e){
if(null != conn1) conn1.rollback();
if(null != conn2) conn2.rollback();
throw e;
}
finally{
if(null != conn1) conn1.close();
if(null != conn2) conn2.close();
}
}

public void connect() throws ClassNotFoundException, SQLException{
System.out.println("Connecting to database server ");
Class.forName("com.mysql.jdbc.Driver");
conn1 = DriverManager.getConnection("jdbc:mysql://ip:port/dbName",
"user", "****");
System.out.println("Connection object for DB = "+conn1);
conn1.setAutoCommit(false);
}

public void doQuery() throws SQLException{
PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO A VALUES(?, ?, ?, ?, ?)");
PreparedStatement pstmt2 = conn1.prepareStatement("INSERT INTO A VALUES(?, ?, ?, ?, ?)");

pstmt1.setInt(1, 8);
pstmt1.setString(2, "value1");
pstmt1.setString(3, "value1");
pstmt1.setString(4, "value1");
pstmt1.setInt(5, 1);

pstmt2.setInt(1, 9);
pstmt2.setString(2, "value2");
pstmt2.setString(3, "value2");
pstmt2.setString(4, "value2");
pstmt2.setInt(5, 1);
try{
pstmt1.executeUpdate();
pstmt2.executeUpdate();

}
catch(SQLException e){
throw e;
}
finally{
//if(null != pstmt1) pstmt1.close();
//if(null != pstmt2) pstmt2.close();
}
}

public void doAnotherQuery() throws SQLException{
PreparedStatement pstmt1 = conn1.prepareStatement("UPDATE A SET field2 = 'updatevalue' WHERE field = ?");
pstmt1.setString(2, "value");

try{
pstmt1.executeUpdate();
}
catch(SQLException e){
throw e;
}
finally{
//if(null != pstmt1) pstmt1.close();
}
}
}

This is my code. Here even if I comment the prepared statement closing at finnally block, the commit is not working.
What could be the flaw ???

[Renjith Mohan]

I found one more clue. If I write both the contents of doQuery() and doAnotherQuery() in a single method, the transaction rollback is working...Then what is the problem in writing in two different methods?