Question Transaction Scope?

[raysefo]

Hi,

I have a method that calls other method which make insert / update to DB like below:

Protected Sub methodA()

...

generalFunctions.insertCustomer(ByVal abc As Customer)

....
generalFunctions.updateSales()


End Sub

I tried to use Transaction Scope but i all got was MSDTC disabled or something like that. The thing is, it seems much more easier to use Transaction Scope rather than regular transactions.How can i manage transactions in a method like above?

Best Regards

 

[jmcilhinney]

You have to enable MSDTC, which is the Microsoft Distributed Transaction Coordinator. The way the TransactionScope class works, it uses a lightweight transaction initially and then promotes that to a distributed transaction if necessary. In order to enable a distributed transaction you must have the Distributed Transaction Coordinator service running. Open the Services MMC snap-in from the Control Panel -> Administrative Tools and then enable and start the service. You'll need to ensure that service is running on all systems you deploy to as well.

 

[raysefo]

thanks for your reply but i know i should enable MSDTC and i googled and found out some security setting i should do. I did those settings also but it gave me error like "communication with the underlying transaction manager has failed". Any ideas?

 

[jmcilhinney]

I'm afraid not. I've never seen that error message so I'd have to search for information just like you.

 

[cjard]

I always found TransactionScope a headache, so instead I wrote a helper class that would enroll TableAdapters in a transaction using reflection..I still have it lying around somewhere if you want it

 

[raysefo]

Thanks so much, it would be nice if i can see them. Bytheway i got rid of TransactionScope errors, now its working but i m not sure if there are performance issues or drawbacks?

 

[cjard]

TransactionScope is indeed horrendously slow and heavy compared to using connection based transactions

 

[jmcilhinney]

The major advantage of TransactionScope is support for distributed transactions and automatic promotion from lightweight to distributed on demand. Most applications don't really need that support though.

 

[raysefo]

In my case, which is lightweight, i guess i need to use TransactionScope. What are your opinions?

method A{

insert to DB

method B ---------------> method C ------------> update DB

method D ---------------> insert to DB


}

 

[jmcilhinney]

One of the main differences between using a SqlTransaction or the like and a TransactionScope is that multiple connections can automatically enrol in a TransactionScope. A SqlTransaction is created from a SqlConnection, so only commands executed over that connection are enrolled in the transaction. If executed inside a TransactionScope, commands executed over multiple connections can all enrol in the same transaction. TransactionScope even supports connections of different types, even non-database connections. It's up to each particular provider whether they support distributed transactions or not.

If you're using one connection then it's easy enough to call BeginTransaction. That's not easy or best in some cases. For instance, cjard likes to use typed DataSets, but each TableAdapter creates its own connection object. For that reason he would have to replace each of those with the same single connection in order to use a SqlTransaction or equivalent.

Also, note that, for a transaction to be able to be rolled back, the connection must remain open. This means that if multiple connections enrol in the same transaction, they must all remain open until you either commit or rollback to make it effective.