Transactions (App Db & WF Db)

Mar 21, 2008 at 8:01 PM
I am using WFTools' persistence & tracking services. and my application database is different from workflow database.
I am planning to use WCF Service which will make calls to save in to these two databases. so I am using manual workflow shceduler service to run on a single thread.
But I am not successful. How can I use same transaction context between my DAL call and WF call?
Any ideas is greatly appreciated.
Apr 4, 2008 at 5:15 PM
Edited Apr 4, 2008 at 5:17 PM
Hope I understood your question... we're using iBatis for DAL and running our WF under ASP.NET in the same way and face the same problem. Finally in iBatis there is a property for the IsqlMapper class so it can recover the context (in our case the webContext through HybridWebThreadSessionStore). On the other hand, the only real problem was the DAL connection, not the persistence service; it worked without problem using the Manual WF Scheduler service.

Not sure if this can be a solution for you... in our case the persistence service worked fine but our DAL throws an exception.

Greetings and sorry for my poor english!
Apr 18, 2008 at 10:50 AM
Hi vasu,

Apologies for the slow reply, I've recently started a new job so things have been somewhat hectic!

If they're different databases you have to use MSDTC - it's the only way you can co-ordinate things across multiple connections. Otherwise there's no way that you can guarantee atomicity of the transaction.

If you're running both databases on the same Oracle instance you can probably give grants to the account used to log on to your application database to the WF database and use those details for your WF database. You'd need to create your own IAdoPersistenceNameResolver in order to stick the fully-qualified schema on the front of the procedure name (e.g. WFTOOLS.WORKFLOWPERSISTENCEPKG.InsertInstanceState and make sure that you use the same connection in your DAL. The second you open another connection then TransactionScope will attempt to escalate the transaction to MSDTC.

I'm not sure how else I can help to be honest, you have to use the same workflow / application database, MSDTC across two separate databases or use kludges like the above to make things work under TransactionScope!