Error \"The resource is already being used in a parent

Hi,

I am getting one error “The resource is already being used in a parent transaction” while using webmethods Explicit Trancation Management. The error dump is as shown below:

---------------------------------------------------------------------------------

          [ART.117.4000] Adapter Runtime (Adapter Service): Unable to invoke adapter service mew.ebs.inbo
und.data.services:AS_EBSDataLineId. Unable to establish connection to connection mew.ebs.common.connection:JDBC_EBSCommon.
[ART.117.4011] Adapter Runtime (Adapter Service): Unable to connect to resource mew.ebs.common.connection:JDBC_EBSCommon.  Th
e resource is already being used in a parent transaction.
          com.wm.pkg.art.error.DetailedServiceException
          com.wm.pkg.art.error.DetailedServiceException: [ART.117.4000] Adapter Runtime (Adapter Serv
ice): Unable to invoke adapter service mew.ebs.inbound.data.services:AS_EBSDataLineId. Unable to establish connection to conn
ection mew.ebs.common.connection:JDBC_EBSCommon.
[ART.117.4011] Adapter Runtime (Adapter Service): Unable to connect to resource mew.ebs.common.connection:JDBC_EBSCommon.  Th
e resource is already being used in a parent transaction.
-------------------------------------------------------------------------------

My flow service pattern is as shown below:

SEQUENCE1(Exit on Success)
    SEQUENCE2(Exit on Failure)
        StartTransaction
        LOOP
            InsertAdapterService1
            InsertAdapterService2
            InsertAdapterService3
            InsertAdapterService4
        END-LOOP
        CommitTransaction
    SEQUENCE3(Exit on Done)
        CatchError
        RollBackTransaction

The features of the InsertAdapterService’s are as below.

  1. All the InsertAdapterService’s are using XA transaction.
  2. All the InsertAdapterService’s are inserting into different tables in the same
    Database.

Please give me some solution.
Thanks in advance
Mahadev

Mahadev,
Try moving the start transaction and commit transaction steps into the loop,in your flow.

HTH
Uday

Mahadev,

Is it possible that a higher service is calling the service you describe, and that the higher service has already started a transaction on the adapter?

Hi All,

First of all many thanks to mlemaire and uday for trying to help me. Let me explain the problem in little bit more detail.

I have a trigger (name - TR_EBSData) that is calling the flow service
(name - FS_EBSData) I have mentioned. The flow service is having the pattern as given in my previous mail. So no other higher level service is calling the flow service.

Now, the input to FS_EBSData is a document that contains

  1. One Header Record
  2. More than one Transaction Record
  3. More that one Details Record
  4. One Trailer Record

I have to insert

  1. The Header Record to database table t_header
    ( Adapter service InsertAdapterService1 does the job)
  2. The Transaction Records to database table t_transaction
    ( Adapter service InsertAdapterService2 does the job)
  3. The Detail Records to database table t_detail
    ( Adapter service InsertAdapterService3 does the job)
  4. The Trailer Record to database table t_trailer
    ( Adapter service InsertAdapterService4 does the job)

I have to use Explicit Transaction Management services because If some error in inserting one particular record then whole set of transaction should be rolled back.
So I have to define the above mentioned set of transactions as a single unit of transaction.

I believe now you will be able to help me. Please try to help me as early as you can because at this point I am unable to do anything without this.

Thanks in advance.
Mahadev

Hi Mahadev,

From your posting i understand that you are inserting (Header, Transaction, Line and trail) records in different tables of same database. If that is the case, just give the transaction type as “LOCAL TRANSACTION” and try it.

Hope it works.
Do correct me if i am wrong.

Thanks,
Radhika

Mahadev,

Can you determine which part of the flow is causing the error? Is it happening in SEQUENCE3, at the rollback call? I have experienced situations in try-catch layouts where the pipeline values from SEQUENCE2 are not available in SEQUENCE3, and so the ROLLBACK call is passed a null value instead of a real transaction ID. My solution has been to place any values that SEQUENCE3 would need at a higher level, eg. outside of SEQUENCE1.

Alternatively, we have experienced situations where it appears that a DB connection can be returned to the pool while it still holds an active transaction. When that connection is retrieved for use by another service, we get an error similar to yours. This should be easy to provide or disprove: stop and restart the connection pool, then make sure your service is the first thing that runs, i.e before any other service call could possibly have used a connection.

HTH
Michael Lemaire

Hi Michael,

The problem is happening at SEQUENCE2. I have investigated a lot doing some trial and error. I found that even if you change the code as shown below, it is giving the same error.

        
StartTransaction
SEQUENCE1(Exit on Success)
    SEQUENCE2(Exit on Failure)        
        LOOP            
            InsertAdapterService2        
        END-LOOP
        CommitTransaction
    SEQUENCE3(Exit on Done)
        CatchError
        RollBackTransaction

One of my friend suggested that the problem may be related to java threads but he was unable to tell me the solution.

I could try your alternative solution for experiment but that would not be an acceptable solution because my requirement does not match with that. In the actual production environment, I think, I would not be given that freedom to stop and restart the connection pool.

Again many many thanks to you for showing interest about my problem. I am really stuck at this point. Please try to help me as much as possible in this regard.

Thanks and Regards,
Mahadev Mondal

Hi Mahadev,

There’s a couple of ‘gotcha’ problems when testing this.

(1) If you step through the service (F7) you may get this behaviour. It’s not safe to test explicit transaction services by stepping through them. If you do, I’ve been told in the past that this can end up in Developer losing the in-flight transaction which goes stale in the connection pool (sounds wierd to me). You can, however, trace through (right click on a later step and ‘trace to here’) to AFTER the SEQUENCE1 has finished.

(2) If you don’t make sure you have dropped the ‘startTransaction’ output before calling ‘startTransaction’ in your service then you may be inadvertantly re-using a prior transaction identifier that has made it back through a parent service.

Good luck,
Adrian

Hi All,

Many thanks to all of you for helping me. My problem has been solved. Actually, before the ‘startTransaction’ there was an adapter service call as a transformer. That adapter service started an implicit transaction. I have identified that and put that adapter service call inside another transaction boundary. Now it is working file.

Again, Thanks to all of you for your constant support and help.

Mahadev

Mahadev,

It’s good to hear tyou found the cause. These things can be hard to find.

Adrian,

We have previously experienced the exact situation you describe in (1) above. The only solution was to restart the connection pool to discard the bad connections. We now use in-house wrapper services for transaction start, commit and rollback. The wrappers check whether the service is running in trace mode – if so, they log a warning and do not try to start or end a transaction. I can post further details if anyone’s interested.

Regards,
Michael

Micheal-

Yes, I thought about doing that myself, but I wasn’t sure how to find out if the service is being traced. That would be great. Do you leave the wrapper service on even after you have put the service in production? Or do you believe that the trace check does not add significant overhead?

Thanks
Roger

Roger,

Yes we use the wrapper in development, test, and production – as the NASA Mars Mission guys said, “fly what you try”. It also means we’re not changing the code between test and production.

In terms of overhead, it’s only a tiny percentage of an overall transaction’s work, so we haven’t worried about it. Other application architecture issues, like transaction sizes and database access patterns, have a much greater impact on performance.

We use a layer of 3 services of each wrapper. From bottom up (in more detail than you probably need :), they are:

  • Service ssg.sys.context:getServiceStack

This is a Java service, based on an posting by Dave Walschot at http://advantage.webmethods.com/article/?id=1610624127.
It returns the current service stack as a string list. The core of the logic is:

try {
    Thread t = Thread.currentThread(); 
    com.wm.app.b2b.server.ServerThread st = (com.wm.app.b2b.server.ServerThread)t; 
    java.util.Stack stack = st.getState().getCallStack(); 

    serviceStack = new String[stack.size()];

    if (stack.size() > 0)
	{		
	for (int i=0;i<stack.size();i++)
	    serviceStack[i] = stack.elementAt(i).toString();
	}
    }
catch (Exception e)
    {
    throw new ServiceException(e);
    }
// return serviceStack[] on pipeline
  • Service ssg.sys.context:isTraceMode

This flow service gets the stack list from getServiceStack (above), and searches it for a service called “wm.server.flow:stepFlow”. If it finds this, it returns true – otherwise it returns false.

It is of course possible that one day WM will change the name of their tracing service, but we’ll deal with that when it happens (besides, it only really affects the development environment, since we almost never do tracing in production - and we generally wouldn’t want to, given the important of transactions in production).

We have an trivial but convenient little service “listContainsString” that we use to look for the “wm.server.flow:stepFlow”.

  • Services ssg.util.transaction:startTransaction, commitTransaction, rollbackTransaction

Each of these 3 services looks like:

	if (isTraceMode)
	   publish log event ("tracing, so didn't [start/end] transaction");
	else
	   pub.art.transaction:[start/commit/rollback]Transaction

Enjoy!
Michael

This is a great thread. I’ve just hit the same error - a service has a flow service call inside it (to do complex data validation) and then if validation succeeds, it does a manually transactioned delete. I think because the validation flow service itself has an adapter service within it, it’s causing an implicit transaction.

Thanks for helping me identify this, Roger, Michael and Mahadev.

Glad to hear it worked out for you. :smiley: