I have a JDBC connection which is of LOCAL_TRANSACTION. I am creating an insert adapter service(inserts 100k+ record in one run), i got some issues when i used startTranction and commitTransaction. If i dont use any explicit transaction controls, it works fine. Is it mandatory to use startTranction and commitTransaction when we use LOCAL_TRANSACTION?
No it is not. The adapter will do the start and commit/rollback for you implicitly.
It is not mandatory to use explicit control when using local transactions, but it is recommended, as it allows you to control when the data is committed or rolled back. Also, if you are not using explicit start and commit transaction statements, then you can only have one adapter connection that has a local transaction in a single wm flow. For some database types at least, when you use local_transaction, you can force the transaction to rollback without using explicit transaction control by ensuring that the service throws failure whenever it has an error. We often use local transaction with no explicit control with a success->fail/done sequence, where the last thing that happens in the done sequence is an exit flow and signal failure. This works for us.
If a step fails and the service does not report failure, then it does sometimes commit up to that point.
Thanks Rob…LOCAL_TRANSACTION works perfectly for me with out explict transaction control…I was just wondering like is there any drawbacks if we do it like that?
tamara_m,
Thanks 4 your reply…In fact i have 2 adapter service,one delete and one insert both with out explicit control inside a single flow ,both uses the same LOCAL_TRANSACTION connection. It works fine for me…
The key thing to remember when doing explicit or implicit transaction control is to do the bare minimum amount of work while in the transaction. Start the trans, do the DB work, stop the trans. Don’t do a bunch of file reads or doc publishes or web service calls. Keeping a transaction open for longer than necessary is bad DB manners.