How to handle the deadlock/hang up in jdbc with oracle database?

I wrote a application wh开发者_如何学运维ich uses jdbc to insert records into table A. Now I found whenever I insert a record into table from other client and didn't commit. the my application will hang at the insert sql until I commit the change from other client.

How can I avoid this problem ? I don't want my application to wait until other client commit their changes.

INSERTs should not normally wait on each other.

One exception to that is when you try to insert the same (primary) key from two concurrent transactions:

• the first one to reach the INSERT will continue normally,
• but the second one will stall at its INSERT until the first one either commits (causing a key violation in the second one) or rolls back (allowing the second one to continue normally).

Interactions between UPDATE and INSERT or even DELETE and INSERT may also cause similar kind of stall.

You could always artificially trigger this kind of behavior from generic tools such as SQL Developer and I would not be too worried about that.

If, however, you have a bunch of client applications stalling each other for prolonged periods of time, you should try to either shorten the length of your transactions or redesign your database (e.g. by using SEQUENCE to guarantee automatic assignment of unique PK) and/or client logic so these kinds of "conflicts" are minimized.

What Branko mentioned is 1 of the reasons. This's the mistake I did in debugging apps using Transactional query where I simply quit debugging after a insert query is executed without commit/rollback using oracle.jdbc.OracleDriver thin client.

However, what's not mentioned here is, if you happen to run a SQL Client Browser i.e SQL Developer to check your table or to verify your insert statement at the same time, they're also the culprit contributing to the freeze. In debugging mode, while stepping through the code (i.e statement.executeUpdate(); ) you will notice that your code seems waiting for server response indefinitely ... very long ... without giving exception. This happens if you perform previous uncommitted query again. And U'll find your SQL Browser connection is also affected.

It also depends on how you construct the query

i.e: 
insert into TB1(ID, Name) values (1, 'User1') x 2 times w/o commit --> freeze on 2nd time 
insert into TB1(ID, Name) values (SEQ.nextval, 'User1') x 2 times w/o commit --> OK, won freeze on 2nd time 
*where ID = auto number* 

Solution:

Stop debugging/runtime server that manage the connection. Close / disconnect all your SQL browsers. Easiest way is close SQL Browser, restart eclipse/netbean IDE if U're unsure.

There're times you may need DBA rights to release(rollback/commit) those uncommitted transactions backlog. The RDBMS server is waiting for actions on those query upon the same col bfr moving to your next query.

Don use transactional query if not necessary by setting the Connection object:

<connection>.setAutoCommmit(true)