Java – How to resolve java.sql.SQLException distributed transaction waiting for lock

javaoracleoracle11gtransactions

We are using Oracle 11G and JDK1.8 combination.
In our application we are using XAConnection, XAResource for DB transaction.
ie) distributed transactions.

On few occasions we need to kill our Java process to stop the application.
After killing, if we restart our application then we are getting the below exception while doing DB transaction.

java.sql.SQLException: ORA-02049: timeout: distributed transaction
waiting for lock

After this for few hours we are unable to use our application till the lock releases.

Can someone provide me some solution so that we can continue working instead of waiting for the lock to release.

I have tried the below option:

a) Fetched the SID and killed the session using alter command.After this also table lock is not released.

I am dealing with very small amount of data.

Best Answer

I followed one topic similar with that with tips about what to do with distributed connections.

Oracle connections remains open until you end your local session or until the number of database links for your session exceeds the value of OPEN_LINKS. To reduce the network overhead associated with keeping a database link open, then use this clause to close the link explicitly if you do not plan to use it again in your session.

I believe that, by closing your connections and sessions after DDL execution, this issue should not happens.

Other possibility is given on this question:

One possible way might be to increase the INIT.ORA parameter for distributed_lock_timeout to a larger value. This would then give you a longer time to observe the v$lock table as the locks would last for longer. To achieve automation of this, you can either - Run an SQL job every 5-10 seconds that logs the values of v$lock or the query that sandos has given above into a table and then analyze it to see which session was causing the lock. - Run a STATSPACK or an AWR Report. The sessions that got locked should show up with high elapsed time and hence can be identified. v$session has 3 more columns blocking_instance, blocking_session, blocking_session_statusthat can be added to the query above to give a picture of what is getting locked.

I hope I helped you, my friend.

Related Solutions

Java – How does the Java ‘for each’ loop work

for (Iterator<String> i = someIterable.iterator(); i.hasNext();) {
    String item = i.next();
    System.out.println(item);
}

Note that if you need to use i.remove(); in your loop, or access the actual iterator in some way, you cannot use the for ( : ) idiom, since the actual iterator is merely inferred.

As was noted by Denis Bueno, this code works for any object that implements the Iterable interface.

Also, if the right-hand side of the for (:) idiom is an array rather than an Iterable object, the internal code uses an int index counter and checks against array.length instead. See the Java Language Specification.

R – TransactionScope and Error: ORA-02049

So it sounds like you must have two transactions competing for a row lock.

Just brainstorming here, but if dbValueLookup.Count = 0, then you will call addData (which sounds like it does an INSERT?), but you will not call scope.Complete() to commit your transaction.

I'm not sure if the End Using will always commit the transaction or not.

Do you really need to create the TransactionScope on every iteration of the loop? Why not create one transaction, do all your updates/inserts, then commit once?

Best Answer

Related Solutions

Java – How does the Java ‘for each’ loop work

R – TransactionScope and Error: ORA-02049

Related Topic