Autocommit with JDBC connections

I hate software development all my IT life. I don’t know why but writing code (not query or simple pl/sql block) never satisfy me. For this reason i try to be far away from programming languages so i can never feel myself free about using code technics in this blog but now i have to give you an important clue about JDBC connections for the developers who works with Oracle (especially for the new ones).

Oracle never uses autocommit for transactions because of its optimistic locking mechanism which is the best point it differs from other RDBMS s which are autocommit by default. But when you use APIs ODBC and JDBC the connection is autocommit by default and this is against the  nature of Oracle.

suppose you have balance table and you are trying to transfer money from account 10 to account 20.

t1 => update balance set balance=balance-1000000 where account_id=10;

t2=> update balance set balance=balance+1000000 where account_id=20;

If you do this update with autocommit feature you take the risk of loosing 1 million dollars at a system fail between the t1 and t2.

normally in Oracle you do this two staments together and commit or rollback after all the transaction is done or fail.

To avoid this situation you must set to off the autocommit option of your JDBC connection

connection conn= DriveManager.getConnection (“jdbc:oracle:oci:database”,”hr”,”hr”);

conn.setAutoCommit (false);

References Used:

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution
(Mr Thomas Kytes Book Pg=272)

Advertisements