transaction thread active (explicit trans)

hi ,
when we use explicit transactions ,and for no reason we get exceptions and everything seems to be in context with explicit transaction management logic .
this is not even an error its just an exception.
i am here with giving the details,actually if we encounter this kind of exceptions once it leaves an open thread on database which has not been committed ,so i guess everytime we need to restart the server.

when i just run the service without explicit trans ,even at this scenario implicit trans are not getting committed.

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.

the structure of flow is
start trans
insert(local_trans )
insert(local_trans)
commit trans
both inserts are to same database.

so i think there are uncommitted transactions exixiting on the database ,or there is a problem with the database .

so anybody have any thoughts on why we get this error when we are just using local trans.

Your flow structure doesn’t show a rollback in the case of an error. You need to use a try/catch sequence structure to guarantee that either commit or rollback is called. Otherwise, your pool connections will be in an unusable state and throw errors at seemingly odd times (like you’ve described).

rob,
thanks for reply.actually as i havent showed my try catch block previously ,hence i did not write about rollback ,but the basic structure does exist in the flow .i think its the database which is not allowing to commit any transactions .

main
Try
start trans
insert (local_trans)
insert (local_trans)
commit trans
catch
rollback trans
so i dont see any issue with the webmethods ,or flow logic ,or adapter problem,
becouse the same flow was working great a week back,hence what could be the database issue which is cousing this error.

sri

Hmm. Did a search for ART.117.4015 on Advantage turn up anything?

Hi Sri,

Was it really required to use expliciit transaction in this case. I went thru the JDBC adapter’s user guide. On page 165 it says -

“A single transaction context can contain any number of XA_TRANSACTION connections but no more than one LOCAL_TRANSACTION connection. If your flow contains adapter services that use more than one LOCAL_TRANSACTION connection, you must use explicit transactions,”.

Can’t we use a single LOCAL_TRANSACTION connection more than once inside a flow service with implicit transaction.

Rob/Sri-

I also have a similar scenario where –

 A Parent flow service "parent_x" calls a subservice "sub_y" which intern calls 4-5 subservices in a Sequence .These 4-5 sub services are infact wrapper to the Adapter services. All the adapater services uses the same JDBC Adapter Conncetion named - xyz. Adapter Connection xyz is configured with LOCAL_TRANSACTION. 

Though i have used this Start, Commit and Rollback (Explicit Transaction), in my Parent flow service “parent_x” , I want to know whether it was really required to do that.

parent_x is like this -

start Transaction
Sequence(try)
sub_y
commit Transaction
Sequence(catch)
getlasterror
rollback Transaction

sub_y -->
Sequence
getOrderHeader
getPrice
insertRate
getQuantity
getOrderLine
insertCase

All above are flow services, wrapper to adapter service.

Please tell me …

Thanks

-Ratnesh

hi ratnesh,
i guess ,actually when u are using same adapter to insert data into different tables ,then u have to use explicit transactions ,so that it will rollback every insertion in this flow ,if an error or exception happens.

But if u have two different adapter connections with Local transaction ,then u have to make one connection into XA_trans.so that u can use explicit transaction.

HTH
sri