Transaction handling questions

I am trying to understand how wM IS ART deals with transaction and cannot quite make sense of it. My environment is IS 6.5 / Oracle thin JDCB driver.

Assuming I have two connectors called
conLocal
conNoTrans
with the matching LOCAL_TRANSACTION and NO_TRANSACTION settings.

Then I have 3 adapter services:
readLocal (using conLocal)
updateLocal (using conLocal)
readNoTrans (using conNoTrans)

  1. If I then write a simple service without any explicit transactions just consisting of:
    readNoTrans
    updateLocal
    readLocal
    it all seems to work, that is readLocal sees the updates made by updateLocal but in the end the update is never committed to the database. However, wM doesn’t throws or logs any errors. It just completely silently rolls back the changes. According to the wM documentation it should only rollback if the service fails which is not the case here.

  2. If I write a service with explicit transactions like:
    readNoTrans
    art.startTrans
    updateLocal
    readLocal
    readNoTrans
    updateLocal

    the 2nd readNoTrans throws the “Resource used in parent transaction” error. This is also confusing as NO_TRANSACTION means autocommit and therefore there shouldn’t really be an uncommitted parent transaction using this resource.

Overall it seems even when I use an adapter service defined on a NO_TRANSACTION connector I have to use explicit transactions around each call of that adapter service if I want to mix it with calls to another adapter service on a LOCAL_TRANSACTION connection. That is the above needs to be rewritten like:

art.startTrans
readNoTrans
art.commitTrans
art.startTrans
readLocal
updateLocal
art.startTrans
readNoTrans
art.commitTrans
updateLocal

If that is the case so why bother to define a NO_TRANSACTION connection as its behaviour doesn’t really differ from a LOCAL_TRANSACTION connection, i.e. it uses up the single slot available in ART for non XA transactions.

Is that correct or do I misunderstand how this all works in wM?

Thanks

Manuel

Manuel,

If you want to commit transactions, then you must create JDBC connection with LOCAL_TRANSACTION. If you do not have insert/update/delete statements, then you may create a JDBC conn. with NO_TRANSACTION.

You need not call transaction services for select query. You should call it for
insert,update,delete statements. You need only one connection, with LOCAL_TRANSACTION.

startTransaction
insert/update/delete
commitTransaction

HTH.

Pauly

Pauly,

my examples are just that, and simplified to exhibit the point I want to make.

Also your statement that transactions are not required for selects is IMO not always true. If you require a repeatable read and / or need to see the changes you made in a transaction during the transaction or need to protect yourself against “phantoms” you certainly need transactions around your reads as well.

The other, and in this context more relevant, observation is that wM always creates a transaction for you (even if you don’t want one!). The first time you access an adapter service in a flow wM starts a transaction on the underlying connector (unless you already started an explicit transaction). This also applies if your adapter service does only a select. And, this is what I am confused about, this appears to also apply if the underlying connector is defined as NO_TRANSACTION.

Manuel

Manuel,

Why don’t you do everything with one LOCAL_TRANSACTION conn.

art.startTrans
readLocalTrans
updateLocal
updateLocal
art.commitTrans

Pauly

I can’t use a single transaction for 2 reasons:

  1. Because there is long running business logic (http / ftp file transfers) between the initial reads and the actual updates / inserts and I don’t want to have locks on database objects hanging around for anything longer than absolutely necessary.

  2. Because its a database controlled loop in which only the inner bits need to be in a transaction.

So overall the basic pseudo code looks something like:

read some initial parameters from database
Get a list of things to do from the database
   for each element in the list do
     get something from a remote server
         start trans
       for each record in the stuff from the remote server do
         read some more things from database
         insert/update records in database
       mark that element as done in the database
        end trans

This is further complicated because we have a database based activity log which is also written to within and outside the above transaction. This log we do not want to have rolled back under any circumstances.

Manuel

Why don’t you use Broker.

Package 1 (to publish)
read some initial parameters from database
Get a list of things to do from the database
for each element in the list do
get something from a remote server
map to a doc and publish to Broker.

Package 2(to subscribe)

start trans
for each record in the stuff from the remote server do
read some more things from database
insert/update records in database
mark that element as done in the database
end trans

Pauly

Pauly,

thanks - yes that would be a solution although not one I architecturally favour.

Manuel

Manuel:
I’ve struggled with NO_TRANSACTION vs. LOCAL_TRANSACTION, and implicit vs. explicit tx too. I wish the JDBC Adapter guide was more informative on these topics.

My guess is that your logging that wraps all the activity may be getting in the way. Or have you created a test scenario to exhibit the behavior without the overarching logging?

https://advantage.webmethods.com/advantage/?oid=1611541835&targChanId=knowledgebase might provide some insight. Other articles on Advantage indicate that NO_TRANSACTION does indeed auto-commit and you cannot override that with explicit transactions. Another fun part of the post was “In any case, once the problem has occurred, it leaves a thread with a corrupted transactional context in the server thread pool. This thread will remain corrupt until the server is restarted.” Eeek.

Nesting DB operations within IS is tricky. Aggressive pipeline management is required to make sure transactionName vars don’t collide.

Sorry for the random thoughts. Probably not helpful but perhaps one of them will lead down the path of resolution. Be sure to let us know your findings!

I have created simple test scenarios which demonstrate exactly the behaviours I am confused about (and exclude the logging issue).

In the end it seems to boil down to in my circumstances:

  1. Don’t use NO_TRANSACTION connectors as they “get in the way”. If you talk to a single db resource (as is the case for me) stick with one LOCAL_TRANSACTION connector.

  2. When using an explicit transaction be aware that one must avoid to start an implicit transaction in any steps preceding the startTrans call (this includes calls to nested services). In my case in which the initial service steps usually read some parameters / setup information from the db (before the real work starts) these adapter services calls must be bracketed by explicit start/commit transaction calls (this is the bit which inflates the code and makes it look really messy).

  3. Don’t nest transactions

  4. If you use nested service calls within a transaction make sure errors are propagated up such that the transaction error handler and thereby the rollback gets invoked. That is “to keep it simple” nested services should fail and not trap errors.

  5. For my logging issue I use the publish/subscribe solution suggested by Pauly. That is a service logs something by publishing a notification document. A trigger picks that up and writes it to the db. As the triggers run independent from the service publishing the document there is no nested transaction problem.

Hi Manuel,
Did you try applying IS_ART_6-5_Fix4 . Your first scenario is valid and should work fine ,if update dont throw any error.An implicit transaction starts as soon as the first transaction interaction starts (i.e., inside a flow service, when an adapter service call is made which uses transactional connection).
For more clarity on implicit transaction, let’s take on example:

Let me have 4 flow services,

NonTransFlow (child flow service called form MainFlow)
//flow begin
putNonTrans [ putNonTrans putTrans is an adapter service uses nontransactional connection]
//flow end


TrasnsFlow (child flow service called form MainFlow)
//flow begin
putTrans [ putTrans is an adapter service uses LocalTransactional connection]
//flow end

NonTransFlow2 (child flow service called form MainFlow)
//flow begin
putNonTrans [ putNonTrans putTrans is an adapter service uses nontransactional Connection]
//flow end


MainFlow
//main flow begin
NONTransFlow
TransFlow
NonTransFlow2
//main flow end


The begin Transaction is called as soon as the first transaction resource call is made i.e., during TrasnsFlow.putTrans.
However, the commit transaction (or rollback if we get an error during execution of the transactional service) is called at end of ROOT level flow service i.e., MainFlow. Much after TrasnsFlow and even after NonTransFlow2 ends.

The interesting statement you make is: An implicit transaction starts as soon as … an adapter service call is made which uses transactional connection.

Am I correct in saying that a connection defined as NO_TRANSACTION is not transactional in the sense used in the sentence above?

In that case

selectNoTrans
startTrans
selectNoTrans

should not throw the “Resource used in parent transaction” error as there wouldn’t be a parent transaction. But it does!

Manuel

The error you are poining is for explicit transaction and not implicit transaction.
I have a similar setup like this in implicit transaction
readNoTrans
updateLocal
readLocal

whcih works fine

Interesting, as that exact sequence does not work for me. The changes made by updateLocal are seen by readLocal but are never persisted in the database, that is wM performs an implicit rollback although NO error is thrown and the service does NOT fail.

As you said in explicit transaction gives error if use NOTRANSACTION service inside and outside starttransaction.
readNoTrans
art.startTrans
updateLocal
readLocal
readNoTrans
updateLocal

Modifying the flow like this will work

art.startTrans
readNoTrans
updateLocal
readLocal
readNoTrans
updateLocal
art.commitTrans

Thanks
Baiju


Hi baijubr,

your answer helped me also, fixing my problem with transactions.

I need transaction handling for inserts into database, but I got the error message, that there is a parent transaction handling in use. So I
put the starttransaction at the very beginning, before I do any selects to the database, and then it worked.

thx for the detailed discussion

joe