The Phantom data trail

For those who have come in late ..
We were happy to use the connection pool for database access . just get the connection from the pool use it and release it back... life's cool until one day when we decided to execute a transaction . we followed the following steps ...

1. get a connection from the free pool
2. set the connection's auto commit property to false (needed for a transaction
3. put the db execute/update statements within a try catch block
4. In the catch block log the exception and,close the statement release the connection and throw the exception back
5. end of the method con.commit()
6. release the connection back to the pool

This worked pretty much fine for all the happy flows ... whenever an error happened we used to have some data appearing in the database long time after the operation was over .The mystery further deepened when we found out this data which was appearing was due to failed transaction.

Reality dawned a li'l later when I found out that in the catch block one crucial step was missing conn.rollback() w/o which we used to release it to the pool and when someone else gets this same connection and performs certain other operations along with those operations whatever statements were successful in the previous operations were being updated

Moral of the story

pool or no pool ... to keep life a li'l simpler and easier follow the best practices