Coskan’s Approach to Oracle

March 14, 2007

Autocommit with JDBC connections

Filed under: Uncategorized — coskan @ 3:33 pm

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)

About these ads

1 Comment »

  1. So what can this all imply?

    Comment by Renate — April 21, 2013 @ 11:03 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers

%d bloggers like this: