env:
IS 6.0.1
JDBC adapter 6.0.1
Oracle 8.1.7
Oracle JDBC thin driver
JDBC Connection = LOCAl_TRANSACTION
JDK 1.3.1_07

I tried to use explicit transaction in my flow for data insertion. My flow looks like -

…
pub.art.transaction:startTransaction

pub.art.transaction:commitTransaction
…

However, I got the following error in “pub.art.transaction:startTransaction”

2003-05-19 10:17:07 EDT [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.117.4036] Adapter Runtime (Adapter Service): Unable to rollback transaction. Transaction state:Transaction is active .
2003-05-19 10:17:07 EDT [ISS.0015.9998E] Exception → com.wm.pkg.art.error.DetailedServiceException: [ART.117.4036] Adapter Runtime (Adapter Service): Unable to rollback transaction. Transaction state:Transaction is active .

Please help!

Bin

I’m also getting the following ART.117.4036 errors:
com.wm.pkg.art.error.DetailedServiceException: [ART.117.4036] Adapter Runtime (Adapter Service): Unable to rollback transaction. Transaction state:Transaction is rolled back .
[ART.117.4036] Adapter Runtime (Adapter Service): Unable to rollback transaction. Transaction state:Transaction is rolled back .
[SCC.0121.0026E] delisted transaction commit failed: javax.transaction.RollbackException: Transaction is rolled back xid = odtweaid/1060178478117

It happens when I run the service versus tracing it in Developer.

Did you ever resolve what was happening in your error?

Hi,

I’m also getting the same problem with the JDBC adapter service when I
invoke. Have you resolved this problem at u’r end.
Please let me know the resolution…

Thanks

Are you stepping through the flow when you get this? Have you tried running it without stepping through?

I am getting the following errors:

[ART.117.4036] Adapter Runtime (Adapter Service): Unable to rollback transaction. Transaction state:Transaction is rolled back .
[SCC.0121.0026E] delisted transaction commit failed: javax.transaction.RollbackException: Transaction is rolled back xid = [SCC.0121.0034E] commit failed: more than 1 local trans enlisted. xid =

I am using an adapted version of WebMUnit to test services for a system I am currently working on. The problem occurs when a service tries to invoke a select on the error message database (SQLServer) and retrieve data located within an Oracle 8i database. The error only occurs when both these database queries are invoked within the same top level service. I assume there is some kind of clash but am fairly new to webMethods and am not sure where to progress from here.

Does anyone have any ideas for a resolution or even just what the cause may be.
Any help would be greatly appreciated.

Thanks

Mark Cecchetto

I’m also getting the same problem.
It works when service with the transaction block (startTransaction-commitTransaction-rallbackTransaction) invoked directly from Broker, but does not work, when invoked from another sevice.

Need help.

Thanks,

Mark Shklover

the good practice as we noticed is to have a start transaction and a commit or rollback around every database call(that uses a LOCAL_TRANSACTION), you can gather multiple calls using the same connection in the same transaction (do not use nested transactions!)

If you have two connections in the same flow that are of the local_transaction type you will definitely encounter problems if the transaction handling is not used

use a unique id for every transaction name!!

Be ware that if you put a rollback in your catch block sequence that the pipeline content of the try block sequence is not passed! meaning that the identifier of the transaction must be generated before the try sequence. If you use a rollback in the try block it works if the identifier was still known but it displays however an error in the logs, something like transaction with id … already marked for rollback. We ignore the error as the service executed OK.

We also noticed that that the transaction gets commited automatically when it goes to the catch block sequence even if there were no commit or rollback transactions specified.

Watch out with transactions that are not commited or roll backed as they mess up the connection pool.

the debug mode currently does not support transactions and this is a real problem!!!

hope this was of any help

feel free to contact me for more info on this topic as we had the same problems, currently everything seems to be working OK

Following is our Environment:
IS 6.0.1
JDBC adapter 6.0.1
Oracle 9i
Oracle JDBC thin driver (ojdbc14.jar)
JDBC Connection = NO_TRANSACTION
JDK 1.4.2

As mentioned, we use NO_TRANSACTION, On some of our IS services we use two JDBC calls to same database (using same JDBC Connection)- a select and update in loop. We get lots of errors :-
“[ART.117.4036] Adapter Runtime (Adapter Service): Unable to rollback transaction. Transaction state:Transaction is active.”.
These services are Scheduled services & invoked every 30 seconds.
We are struggling to resolve these and can’t get anywhere.
Some Urgent help and inputs will be really appreciated as soon we want to go to production.

Thanks

strange NO_TRANSACTION type connections didn’t give problems in our case (not the answer you were hoping for)

we started having big problems when having two LOCAL_TRANSACTION connections in the same execution flow

are you using any other JDBC connections in your services, e.g. custom logging or error handling services that might be called during your service executions?

If during your excution flow there is no other JDBC connection involved I cannot think of a cause right now.

Currently you are not using transaction handling at all I guess which is normal as it should only be done for LOCAL_TRANSACTIONS.

I hoped I could have helped you better but…

Hi,

Referring to your message:

"If you use a rollback in the try block it works if the identifier was still known but it displays however an error in the logs, something like transaction with id … already marked for rollback. We ignore the error as the service executed OK. "

Did you get any reason/resolution for this error ? I am running into the same error (delisted transaction commit failed: javax.transaction.RollbackException: Already marked for rollback xid = cdcdev2/1077232517944) in my flow but the rollback works fine.

My flow goes like this :

----> Generate a unique transaction id
----> Start transaction using generatedtransaction id (using pub.art.transaction:startTransaction)

----> TRY CATCH Block
---->TRY Block
----> Insert in Table 1
----> Insert in Table 2
----> Commit Transaction using generated transaction id (
pub.art.transaction:commitTransaction)
---->CATCH Block
---->Rollback Transaction using generated transaction id
(pub.art.transaction:rollbackTransaction)
---->Raise Exception

Any updates on this? I’m using version 6.1 and still get this behaviour.

Thanks

Hi Jorge

I spent one day with JDBCadapter ver.6.0.3 on IS6.1 build132 struggeling with simmilar problems in Developer until I found help in bart.goethals remark:
Quote:
“the debug mode currently does not support transactions and this is a real problem!!!”

Thanks Bart!

Be careful to test your flowService with the Run-button, not Trace or Step.
Hope this will help you out.

Regards

simen

Post a copy of your Flow and perhaps we can spot a resolution

startTransaction: If you are doing any selects before inserts etc then ensure that the whole segment is within the transaction. You may need to move the startTransaction invoke above all of the db service invokes. This way you prevent any dirty database reads.

debugLog: The debug trace from the developer tool is currently not supported by JDBC adapter and the alternative to overcome this is to use the debugLog messages.

Another Tip: From what we have seen, BatchInsert and BatchUpdate from JDBC Adapter 6.0.3 are at least three times faster than the Single Insert / Update while working with Bulk documents insert or update. You can also try using them if needed.

Good luck,
Muthu.

some other usefull info:

In january we noticed that the rollback transaction is depleting the connections of the pool one by one. The pool will at the end remain using one connection until a rollback depletes the last connection. Then it will reconnect the pool (this may take some time).

We noticed this behaviour for JDBC adapter 6.0.2 on Oracle databases.

This problem is currently being fixed by webMethods.

The error on the already rollbacked transaction is claimed to be “not an error” by webMethods. There is a patch to hide the errormessage.

the version 6.1 still has the same problem, I still used the JDBC adapter 6.0.2 and not the JDBC adapter 6.0.3 for my test

pub.art.connection:getConnectionStatistics is a nice service to check out what happens to the connection pool on doing the rollback

I would advise to do a test and see the results ;-).

Does anyone see the same problem on doing the test?