"ORA-02049: timeout : distributed transaction waiting for lock."
Steps to reproduce error:
Service 1 (DatabaseAdapter 1)-->inserting into table1
Service 1-->calls service 2 through wsdl
Service 2 (DatabaseAdapter 2)--> updating table 2 (Important: table 2 has foreign key to table1, If foreign key dropped then error resolved)
Current weblogic connection pool configurations:
Both DatabaseAdapters connections are "oracle.jdbc.xa.client.OracleXADataSource" and connecting to RAC
It looks like each DatabaseAdapter is opening its own session which is causing the Lock error.
How to resolve this and have both adapters use same session?
We're in a very similar situation right now and so far it seems to be either a misconfiguration on the RAC itself or utterly a bug on it related to several nodes involved in a distributed transaction.
The only difference is we are using JBOSS AS but before you jump to say I'm in the wrong forum, know that our datasource is configured with "oracle.jdbc.xa.client.OracleXADataSource" and we are using 3-nodes RAC.
Our failing scenario works OK:
1. on basic DB Cluster, all App nodes hitting the same DB node.
2. on the RAC while we disabled 2 of the 3 nodes.
3. on RAC with all nodes enabled if it's lucky enough to reach the same node as the initial app update.
So it seems the distributed Tx is coordinated correctly but the RAC is either mis-configured or buggy.
Kumar12, you say:
Try to increase the Distributed Parameter value in DB.
distributed transaction timeout? we started with 3 minutes, upped to 5 minutes and of course, still failing.
Also, tune your JTA and XA paramter on weblogic console.
Have you had any luck with the timeouts? We're still searching ourselves.
I found a somewhat similar situation described in this thread: Re: Problems with RAC and XA: Fallback Initiator seems to have solved his problems with DTP services. Oracle documentation is really confusing regarding DTP since they say DTP are not needed for Oracle 11R1 and above but somehow you still need them :D Anyway, if you're using a DB version below 11R1 you may want to consider DTP services, maybe they solve your problem.
@Arik, those Metalink notes are not helpful in my situation.
@kumar12, I've tried increasing the timeouts till 10 minutes and still failing. Anyway, they should be executed in milliseconds like it happens against a standalone DB. Those are legitimate distributed transaction statements and they shouldn't take minutes to complete.
One thing I've noticed, the DB sessions create TX enqueues in GV$LOCK while the Oracle documentation states that distributed transaction branches create always create DX enqueues. Also record in V$GLOBAL_TRANSACTION indicates a global transaction with one branch, one refcount and tightly coupled distributed transaction branches.
All we got from Oracle support was an indication that our situation is similar to the one described in bug 13361419 and it's not a bug. Your situation is for sure identical to that one so don't expect any help from them.
1. Oracle Metalink: Consideration should be taken to constrain those parts of an application to use DTP services. Load balancing can be used for other parts of the application if desired.
2. Oracle documentation: For a long time they clearly stated that 'split branches' of a distributed transaction (working on several nodes at once) would not work: just google for 'best practices for rac and xa' and access the first PDF. So you may want to try suggesting the distributed transaction to open sessions towards a single node, making use of the distributed transaction context. I expected this to be a feature of the RAC and be transparent client side but it seems I've set my expectations too high. One thing that may help may be UCP from Oracle that should allow you to define 'transaction affinity', see some piece of info here but feel free to search for more: google for 'Getting Database Connections in UCP' and access the first page. We're considering this one too, although I'm not sure it pairs up too nicely with JBOSS...