The resource is already being used in a parent transaction

chandra_sekhar2 · March 3, 2008, 6:47pm

Hi

i have used explicit transaction like this

-startTransaction(t1)
–sequence(success)
—sequence(failure)
insert (local trasaction, connect to db1)
update (no transaction, connect to db2)
commit(t1)
—sequence(done)
rollback(t1)

i am getting the specified exception
Error ART.117.4011 Unable to connect to resource .The resource is already being used in a parent transaction.

As per my understaing we can not use two local transactions in one explicit transaction.

if i create a seperate flow and use explicit transaction for insert service and called up into the current explict transaction, if insert is success then it will commits the transaction even though update fails.

I want to roll back insert service if update fails.

can you please help me out.

Regards
Chandrasekhar

chandra_sekhar2 · March 6, 2008, 12:10am

Hi

we have two adapter services

insert [ connected to oracle][local transaction]
update [connected to dataware house][local transaction]

both are different connections

we are inserting data into oracle form DW.After inserting we need to update Oracle db.

my requirement is, i want to roll back the insert step if at all update fails

i have written my flow like this

startTransaction
sequence(success)
sequence(failure)
insert
update
commit
sequence(done)
rollback

i am getting error with this flow step.can you please help me out the steps to achive this.

Thnks&Rgds,
Chandrasekhar

system · March 6, 2008, 12:58am

You can’t enlist two local_transactions that way in webMethods IS they have to be wrapped each in an explicit transaction. What you are trying to do is normally what is considered a distributed transaction. You can try using an XA_Transaction type and then try coordinating it across the two db’s. That gets complicated. You could also just do a compensating transaction back to the insert table.

However why not just run the update first and if it fails just don’t do the insert?

chandra_sekhar2 · March 18, 2008, 6:51pm

Hi

Thnks for your reply.we have manually updating the data.we are not using both insert and update in a one expicit transaction.

if any one of the service will fail we receive the e-mail. based upon this we need to take the steps further.

Thnks&Regds,
Chandrasekhar

saurabhm · March 19, 2008, 12:06am

Best approach for your requirement is to use XA_TRANSACTION (that is their purpose)

LC1 · June 30, 2009, 3:35pm

I am having a very similar situation.

I have a JDBC (com.ibm.as400.access.AS400JDBCDataSource) with LOCAL_TRANSACTION.

My InsertService starts by creating a transaction name by using pub.date.getCurrentDateString with pattern= yyyyMMddHHmmssSSS

This provides unique Transaction names such as: 20090630113533638

1 - MAP (create transactionName)
2 - Start Transaction
3 - Sequence (main)
3.1 - Sequence (try)
3.1.1 - Loop
3.1.1.1 - Insert
3.1.2 - Commit
3.2 - Sequence (catch)
3.2.1 - rollback

The ideia was to either insert all or roll back and insert none.

It is exaclty as stated in Adapter documentation, but it returns the error:
[ART.117.4011] Adapter Runtime (Adapter Service): Unable to connect to resource XXXXXXXXXXXXXX:YYYYYYY. The resource is already being used in a parent transaction.

Any ideias?

system · June 30, 2009, 8:26pm

Is your commit inside your loop? If so take it out. The other thing is you don’t need to generated a transaction id for that service, IS will handle that for you.

LC1 · June 30, 2009, 10:59pm

Hi.

Commit is outside the loop. I will only comit if the insert loop works fine.

Just realised that the main service also calls adapterservices before.

Hence when I get to the startTransaction it already is started.

I think I will need two connections, one NO_TRANSACTION and one LOCAL_TRANSACTION so that I can make this work…

system · July 1, 2009, 6:00am

you can nest your transactions, don’t need to separate by local_transaction or none, will not make any difference. as long as your parent and inner transactions are nested correctly it will work. transactions are fun aren’t they?:eek:

Fiorald_Aliaj · June 6, 2014, 5:16pm

Hi,
i have the same problem,
can you help me ?

Mahesh_K_Sreenivas · June 6, 2014, 6:21pm

I have seen your code sample. .Can you explain your problem in brief? When do you receive this error? Which adapter service are you invoking in your code…

What is your transaction type? Are you passing the same Txn name to start, commit and rollback transaction services.

rmg · June 6, 2014, 8:22pm

Forald,

Are you mapping explicitly the same transactionName value given in the start and commit/rollback and even in the catch block section for rollback with EXIT signal on failure?

HTH,
RMG

Fiorald_Aliaj · June 9, 2014, 9:28am

Hi guys,
@M@he$h

Hello guys,
@M@he$h
on the first step i create an transaction name based on the current timestamp.
When i start the transaction i use as transaction Name the generated one.

On the try sequence i loop through a document list and for every document i call a new flow service when i first do controls, and only if the controls return an OK sms i call the jdbc adapter (A Dynamic SQL adapter)

I’m using LOCAL TRANSACTION as transaction type.

@M@he$h, rmg

Yes i think i’m using the same transaction name on commit or rollback. The transaction name is the one generated on the first step of the flow service

Regards,
Fiorald

Mahesh_K_Sreenivas · June 9, 2014, 9:41am

Can you share the information from server log after the service being called/invoked…?

Fiorald_Aliaj · June 9, 2014, 9:47am

The rror returned by server is :

Adapter Runtime: Error Logged. See Error log for details. Error: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service AgosEccezioneCoperto.adapters.database.loadVariazioneAnagrafica:insertIntoVariazioneAnagrafica.
[ART.117.4011] Adapter Runtime (Adapter Service): Unable to connect to resource connection:DB2Connection. The resource is already being used in a parent transaction.
2013-01-08 17:40:13 CET [ISP.0090.0004E] AgosEccezioneCoperto.adapters.database.loadVariazioneAnagrafica:insertIntoVariazioneAnagrafica – [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service AgosEccezioneCoperto.adapters.database.loadVariazioneAnagrafica:insertIntoVariazioneAnagrafica.
[ART.117.4011] Adapter Runtime (Adapter Service): Unable to connect to resource connection:DB2Connection. The resource is already being used in a parent transaction.
2013-01-08 17:40:13 CET [ISP.0090.0004E] AgosEccezioneCoperto.services.loadVariazioneAnagrafica:insertLoadVariazioneAnagrafica – [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service AgosEccezioneCoperto.adapters.database.loadVariazioneAnagrafica:insertIntoVariazioneAnagrafica.
[ART.117.4011] Adapter Runtime (Adapter Service): Unable to connect to resource connection:DB2Connection. The resource is already being used in a parent transaction.
2013-01-08 17:40:13 CET [ISP.0090.0004E] AgosEccezioneCoperto.services.Caricamento:ParsificaRecordAnagrafica – [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service AgosEccezioneCoperto.adapters.database.loadVariazioneAnagrafica:insertIntoVariazioneAnagrafica.
[ART.117.4011] Adapter Runtime (Adapter Service): Unable to connect to resource connection:DB2Connection. The resource is already being used in a parent transaction.
2013-01-08 17:40:13 CET [ISP.0090.0003C] [AgosEccezioneCoperto.services.Caricamento:CaricamentoFlussoAnagrafica]- Start
2013-01-08 17:40:13 CET [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.117.4018] Adapter Runtime (Adapter Service): Error while closing transactions at service completion Error:[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections…
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.
2013-01-08 17:40:13 CET [ISS.0015.9998E] Exception → com.wm.pkg.art.error.DetailedServiceException: [ART.117.4018] Adapter Runtime (Adapter Service): Error while closing transactions at service completion Error:[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections…
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.
2013-01-08 17:40:13 CET [SCC.0121.0116W] Active transaction detected while completing service AgosEccezioneCoperto.services.Caricamento:CaricamentoFlussiAnagrafica. Rolling back pending operations.
Caught throwable: java.lang.NullPointerException
at com.webMethods.portal.portlet.wm_xt_ldapdirsvc.service.connection.cookie.LdapPagingCookie.generateQueryHolder(LdapPagingCookie.java:150)
at com.webMethods.portal.portlet.wm_xt_ldapdirsvc.service.connection.cookie.LdapPagingCookie.prepareQuery(LdapPagingCookie.java:75)
at com.webMethods.portal.portlet.wm_xt_ldapdirsvc.service.LdapDirQueryProvider.search(LdapDirQueryProvider.java:360)
at com.webMethods.portal.portlet.wm_xt_ldapdirsvc.service.LdapDirQueryProvider.lookupByID(LdapDirQueryProvider.java:185)
at com.webMethods.portal.mech.dir.impl.DirSystemMechanics$1.visit(DirSystemMechanics.java:224)
at com.webMethods.portal.mech.dir.impl.DirSystemMechanics.visitDirServices(DirSystemMechanics.java:451)
at com.webMethods.portal.mech.dir.impl.DirSystemMechanics.lookupPrincipalByID(DirSystemMechanics.java:222)
at com.webMethods.portal.bizPolicy.command.dir.LookupPrincipalByID._lookupPrincipalByID(LookupPrincipalByID.java:119)
at com.webMethods.portal.bizPolicy.command.dir.LookupPrincipalByID.lookupPrincipalByID(LookupPrincipalByID.java:101)
at com.webMethods.portal.bizPolicy.biz.dir.impl.DirSystemBizPolicy.lookupPrincipalByID(DirSystemBizPolicy.java:112)
at com.webMethods.sc.directory.impl.DirectorySession.lookupPrincipalByName(DirectorySession.java:520)
at com.wm.app.b2b.server.cds.CDSUserManager.getUser(CDSUserManager.java:116)
at com.wm.app.b2b.server.UserManager.getUser(UserManager.java:115)
at com.wm.app.b2b.server.ACLManager.allow(ACLManager.java:400)
at com.wm.app.b2b.server.ACLManager.allow(ACLManager.java:390)
at com.wm.app.b2b.server.ACLManager.permitInvoke(ACLManager.java:1924)
at com.wm.app.b2b.server.ACLManager.process(ACLManager.java:208)
at com.wm.app.b2b.server.invoke.DispatchProcessor.process(DispatchProcessor.java:30)
at com.wm.app.b2b.server.AuditLogManager.process(AuditLogManager.java:363)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:547)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:386)
at com.wm.app.b2b.server.ServiceManager.invoke(ServiceManager.java:234)
at com.wm.app.b2b.server.comm.DefaultServerRequestHandler.handleMessage(DefaultServerRequestHandler.java:119)
at com.wm.app.b2b.server.HTTPMessageHandler.process(HTTPMessageHandler.java:160)
at com.wm.app.b2b.server.HTTPDispatch.handleRequest(HTTPDispatch.java:181)
at com.wm.app.b2b.server.Dispatch.run(Dispatch.java:355)
at com.wm.util.pool.PooledThread.run(PooledThread.java:131)
at java.lang.Thread.run(Thread.java:662)

Mahesh_K_Sreenivas · June 9, 2014, 10:56am

@ Fiorald,

Just try the below steps:
1> Disable and enable the connection on JDBC and then try to run the service
2> Reload WmART package
3> What is Minimum Pool Size=? in JDBC connection.
4> As a best practice it is always good to use the WmPublic/pub.flow:getLastError as the first step in catch block and then WmART/pub.art.transaction:rollbackTransaction

If not working see below:

Explanation: WmART attempted to create a new connection to the specified resource, but detected that
the resource is currently enrolled in an ongoing transaction.

Action: This is an internal error. Contact webMethods Customer Care. Please have the following
information available: system and setup specifics, journal logs, and error logs with the
stack trace exposed.

system · June 9, 2014, 12:01pm

Hi,

Please call your Insert and update adapter’s service in different transaction boundary (wrapped with two different transaction boundary) as you are trying to insert and update operation and both db are different.
You can achieve this in XA transaction but for LOCAL_Transaction please use different transaction boundary.
Try the above and let me know if you have any question.

HTH,
Vicky

Mahesh_K_Sreenivas · June 9, 2014, 12:26pm

@Fiorald,

Are you using two different databases? or just one? I see you are using LOCAL TXN

Fiorald_Aliaj · June 10, 2014, 9:36am

Hello guys,
I’m using 10 as the maximum size of the pool to the JDBC adapter configuration settings.
I’m using a single database and I’m trying to loop over an array of input records (list of documents)
For all records in the input array I have to do some custom validation controls. Only if the record pass validation checks I have to insert on the DB, otherwise I have to do the rollback, returning to its previous state by canceling all changes.
I think I solved the problem with some sort of solution.
Initially loop the input array and run the validation controls.
Only if controoli validation successfully complete call a new service in which I open the transaction, do the loop dell’aaray input and insert the records in the DB. If something goes wrong in the sequence Catch

Thanks for your help,
Regards
Fiorald

rmg · June 10, 2014, 5:42pm

OK it make sense from your new logic/split the service…as long the rollback scenario works for your scenario!

HTH,
RMG