JDBC Local Transaction

Hi All,

We are having JDBC Local Transaction connection and there is a insert adapter service which calls a store procedure on DB.

Even though the transaction type is local we havent used the start, commit and rollback transaction to manage the transactions.

My question is if we are not manage the transaction using art services does the IS will open a connection when there is a request and close it after there is sucessfull or unscuessfull insert.

This is because on the IS we are seeing Available Thread reached 0% and systems gets hanged.

We believe that when we use Local JDBC transaction we have to manage the transaction because the IS Resource Manger will try to roll back in case if there is any error and returns the connection to the pool but in our case it is not happening because we dont have implicit commit or roll back.

Does my statement is true or any other things on IS is causing us this problem.

Apperciate your quick response.

Thanks
KSV

I suspect something else is consuming the IS threads.

JDBC connections are managed by the connection pool. Connections are created as needed to fulfill service requests. After the service is complete and is finished using the connection, it is returned to the pool. The connection is not closed until the expiration time.

You do not need to explicitly manage transactions (though I recommend always doing so). IS will implicitly manage them, starting the transaction on the first JDBC service call and then committing/rolling back when the service exits.

Review the Service Usage page to see if there are services that are hung and consuming your system threads. That may lead you to the source of the issue.

Hi All - We too are using a single LOCAL_TRANSACTION JDBC adapter connection for both implicit and explicit db services. I [b] think [/b] that should be ok, but let me know your thoughts on whether we should should use a second NO_TRANSACTION connection for the implicit db calls.

The reason I’m asking is because we occassionally see this informational message in our server log:

Active transaction detected while completing service . Rolling back pending operations.

According to wM Advantage (https://advantage.webmethods.com/advantage?targChanId=kb_home&oid=1614333377), this server log entry is only informational and doesn’t signify anything serious. However, I somehow get the sense that it shouldn’t be ignored. Specifically, if there is indeed a rollback happening in the service, why doesn’t that specific db call fail and send control to the CATCH block (we know this isn’t happening since we don’t see notifications being sent out from our CATCH block).

Does anyone know what to make of the “Active transaction detected …” server log entries?