When should we call connection.rollback() method?

Question

Please let me know when do we require to call the method connection.rollback();

try{
  connection = getConnection();
  connection.setAutoCommit(false);
  pstmt1 = connection.preparedstatement ( ... );
  ...
  pstt1.executeUpdate();
  pstmt2 = connection.preparedstatement ( ... );
  ...
  pstt2.executeUpdate();
  connection.commit();
}catch ( Exception sqe ) {  sqe.printStacktrace();
}finally {
  closeQuitely ( pstmt1 );
  closeQuitely ( pstmt2 );
  closeQuitely ( connection );
}

In above code we are not using connection.rollback(), but if some exception occurs, even then everything will work fine [ i guess ], cos connection is already set in autoCommit = false mode.

So what could be the possible situation when we need to use this method. Please post the example as well.

Answer 1

When you close your connection, your transaction will be terminated. Most DBMS's will rollback your transaction because they don't know under what circumstances the connection was terminated (maybe your program was killed?). So if you've already committed, the rollback will do nothing.

On the other hand, if you're using Connection-Pooling, when you close the connection, the Pool Manager intercepts it and will probably (hopefully) rollback the connection and leave the connection open.

It's good practice to rollback inside the catch clause, or even in the finally clause. It generally doesn't hurt to do an unnecessary rollback after a commit.

As an aside, if you're using Postgres, it's a good idea to rollback before you start to ensure that your transaction start-time is reset. That's because Postgres holds the current_timestamp value to the time the transaction started and if you're using pooled Connections, this could have been a long time ago!