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)
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.
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:
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?
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.
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.
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.
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.
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
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?
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:
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.
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).
Don’t nest transactions
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.
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!
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
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.