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

In the exception case your transaction is unresolved. Eventually it will timeout, and as you say it will rollback. But until then (which may be several minutes) all the locks taken by your transaction will be held. The connection has no way to realise that you may not just about to commit(). Holding locks for an extended period like that is very back for concurrency.

Add the rollback to your exception case.

It may appear that closing your connection will also terminate the transction. When using simple JDBC, however in the presence of connection pooling as implemented in application servers closing the connection has the semantic of "return to pool" and the connection pool will retain the connection's association with your current transaction. If later in your code, still in the scope of the same transaction, you ask for a connection the pool will return you the same connection. This is very handy indeed for writing moular applications, but has the penalty that you cannot assume closing a connection resolves the transaction.

begin tran

// call a method
    get connection

    work

    close connection

// call another method

    get connection  // you get the **same** connection still associated with the tran

    work

    close connection

commit

Answer 2

If you close a connection without committing then it will transaction will be rolled back. if you are using a connection pool, it is probably doing that for you.

An explicit rollback is probably more appropriate when you encounter a condition that is not causing an exception but you still don't want to commit.

Answer 3

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!