When to use [Transactional(TransactionalTypes.TransactionScope)] ?

jspurlin posted on Monday, December 18, 2006

I am working with a DB Architect and we are using stored procedures with their own transactions.  But with my objects I use

[Transactional(TransactionalTypes.TransactionScope)]

and find it works well.

My question is when using  [Transactional(TransactionalTypes.TransactionScope)] in CSLA, does the use of transactions in SqlServer 2005 stored procedures create an unnecessary burden on system resources.  Is using  [Transactional(TransactionalTypes.TransactionScope)] in conjunction with transactions scripted in stored procedures compatible.  If not, when should one approach be opted for as opposed to the other?

Thank you
jspurlin

ajj3085 replied on Monday, December 18, 2006

Well, you'll probably end up with nested transactions, which takes some more resources, but nothing I'd probably worry about. 

I think Rocky explains why the BO's usually are in charge of the transactions in the book, and I agree with his reasonings.  If you can, you may want to suggest removing the transactions from the procs, using the book as the argument.  But that's up to you, I don't think you'll hit any problems with having transactions in both places.

Keep in mind that it may be possible to have two transactions nested within a larger transaction with your setup; one from the attribute, and then you may end up calling two procs that also start their own transactions.. now it might be getting more expensive in terms of resources.

Brian Criswell replied on Monday, December 18, 2006

One thing you might want to check is whether a rolled back transaction in a sproc will also roll back the .Net transaction.  In SQL server, each nested transaction must be committed independently but a rollback will roll back all transactions.