According to http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#close():
"It is strongly recommended that an application explicitly commits or
rolls back an active transaction prior to calling the close method. If
the close method is called and there is an active transaction, the
results are implementation-defined."
This test, using Mysql rather than Oracle confirms this fact:
import static org.junit.Assert.assertEquals;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
public class DBTest {
public Connection openConnection() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "password");
c.setAutoCommit(false);
return c;
}
@Test
public void testSO25886466() throws SQLException, ClassNotFoundException {
{
Connection c = openConnection();
PreparedStatement delete = c.prepareStatement("delete from temp");
delete.executeUpdate();
c.commit();
c.close();
}
{
Connection c = openConnection();
PreparedStatement insert = c.prepareStatement("insert into temp values ('a', 'b')");
insert.execute();
c.close();
}
{
Connection c = openConnection();
PreparedStatement select = c.prepareStatement("select count(*) from temp");
select.execute();
ResultSet rs = select.getResultSet();
while(rs.next()){
assertEquals(0, rs.getInt(1));
}
rs.close();
select.close();
c.close();
}
}
}
According to http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html:
(boolean) Flag to remove abandoned connections if they exceed the
removeAbandonedTimeout. If set to true a connection is considered
abandoned and eligible for removal if it has been in use longer than
the removeAbandonedTimeout Setting this to true can recover db
connections from applications that fail to close a connection. See
also logAbandoned The default value is false.
I would recommend not setting removeAbandoned
so that Oracle closes the connection after a timeout on the server side, rather than Tomcat closing it. Oracle will probably not commit the transaction in that case, but you would need to test this.
Alternatively, could you increase the removeAbandonedTimeout
setting, so that your program can finish, and no connections get abandoned?
Another problem you have is that your application has become tied to Oracle because you are relying on the driver implementation where the spec has a hole in it. If you can, program against specs, so that you are free to migrate your application to a different database, although I know that is hard in practice.
A completely different solution would be to take an open source connection pool, and extend it with an AOP interceptor which can intercept calls to close
and work out if the transaction has been committed, and if not, call rollback
on the connection. That's quite a complex solution though... :-)
defaultAutoCommit=false
that also did not solved your problem....... – dbw