local transaction inside transaction scope
Well, i don't want to use distributed transaction everytime i use a transaction, so i have decided to use local transaction when i could. The problem is I have to use Oracle, so if i put trans inside a transactionscope it produces a distributed one. Well, what I want to know is: which is the best way to achieve this? How can I enlist a local transaction (dim tran as oracletransaction=con.begintransaction) with a distributed one? this code gives me an exception in begintransaction line(parallel transaction are not suported).
Using tsAsNew
TransactionScopeDim
dbAs
Database =New
Oracle.OracleDatabase(eDal.ObtenerCadena(Util.idAplicacion))Try
Using
conAs
OracleConnection = db.CreateConnection()con.Open()
Dim
tranAs
OracleTransaction = con.BeginTransaction()Try
TiposUO.Guardar(db, tran, ds,
False
) 'CALLS DBtran.Commit()Catch
exAs
Exceptiontran.Rollback()
EndTry
EndUsing
Catch
exAs
ExceptionThrow
exEndTry
ThrowNew
Exception("caca de vaca")EndUsing
Even if it worked, this wouldn't quite do what you intended. You'd have two independent transactions with guarantee of a consistent outcome. Right now, with this snippet, the System.Transactions transaction doesn't actualy do anything, but I am assuming that in the full code it would.
This is part of what promotable transactions is intended to solve -- by supporting the PromotableSinglePhaseEnlistment (PSPE) interfaces, the database can have a local transaction until the topology requires a full distributed transaction. However, as you pointed out, not all databases support that.
If you are sure that you have a topology that only requires a local transaction, there's a workaround that involves placing a PSPE shim in the transaction and having that coordinate the database local transaction. You can find details about this on my blog at http://pluralsight.com/blogs/jimjohn/archive/2005/09/13/14795.aspx.
Jim.
Hi Jim,
I think it's great to have the ability to use declarative (implicit) transactions with TransactionScope on SQL Server 2000 and other data sources such as Oracle (without MSDTC, I mean). So your solution is much appreciated.
Could you please provide an example of your PSPE shim usage? It appears that for me the one presented at http://pluralsight.com/blogs/jimjohn/archive/2005/09/13/14795.aspx does not work.
My question is: when should one call SqlConnection.Open() using your DatabaseTransactionAdapter? When doing this inside TransactionScope before DatabaseTransactionAdapter.Begin it throws an exception (if MSDTC network access is not configured, this is the case I'm interested in). But if SqlConnection is not opened before calling DatabaseTransactionAdapter.Begin then it throws exception when calling SqlConnection.BeginTransaction. If SqlConnection is created and intialized outside TransactionScope (this is not the right way, I know) then we get an exception when executing SqlCommand.
So how should one manage the SqlConnection objects when using your soluition? Should MSDTC be configured on server and client side for this workaround to function?
Thanks in advance,
Nickolay.
