<Transactional(TransactionalTypes.TransactionScope)> Question

SouthSpawn posted on Friday, May 09, 2008

I have the following piece of code.

<Transactional(TransactionalTypes.TransactionScope)> _

Protected Overrides Sub DataPortal_Insert()

Using DataLinq = ContextManager(Of SystemDataLinqDataContext).GetManager("ConnectionString")
      DataLinq.DataContext.spAddFinishType( _
                     Description, _
                     Active, _
                     _Id)
End Using

End Sub

I heard using the <Transactional(TransactionalTypes.TransactionScope)> attribute is a performance hog if you are hitting a "single" database. Pretty much overkill.

Is there a way I can create a manual transaction with the code above?

Or am I thinking this all wrong.

Thanks,
Mark

RockfordLhotka replied on Saturday, May 10, 2008

No, TransactionScope is designed for a single database. The challenge is that it is only fast if you only open exactly one database connection and reuse that connection.

This is the purpose of Csla.Data.ContextManager - to make it very easy to reuse a single connection - thus making TransactionScope the right option.

ContextManager opens a connection if it isn't open, or reuses a connection if it is already open. The trick is to nest all your Using blocks.

In other words, the root object (where your DP_XYZ method is called by the data portal) should have a Using block for the context, and all related data access should be in that Using block.

<Transactional(TransactionScope)> _
Protected Overrides Sub DataPortal_Update()
Using ctx = ContextManager(Of MyDbContext).GetManager("MyDbName")
    ' update this object
    ' update child objects
    ' interact with any other root or command objects
End Using
End Sub

As long as everything happens within that Using block, all the Using blocks in those other objects will automatically reuse the same L2S data context object, and thus will reuse the same database connection.

As long as every root object's DP_XYZ methods follow this pattern, you can freely invoke one root object from another one's DP_XYZ methods and they'll all automatically reuse the data context.

The result is very clean and simple. No muss, no fuss Smile [:)]

smiley riley replied on Monday, May 12, 2008

If the set of data updates/inserts etc includes at any point retrieving data then use the flag to exclude these from the transaction otherwise you may lock records within these selects as they may become part of the transaction. I may be wrong but best be aware.

Not that you should need to select in an update delete etc, but people do.

DCottle replied on Monday, February 16, 2009

smiley riley:

If the set of data updates/inserts etc includes at any point retrieving data then use the flag to exclude these from the transaction otherwise you may lock records within these selects as they may become part of the transaction. I may be wrong but best be aware.

Not that you should need to select in an update delete etc, but people do.

what flag exactly are you talking about? we have some stored procedures that we are not in control of that do exactly that. 'Select' records as the last action of the 'Insert/Update'. Is there a flag we can include on TransactionScope that would prevent the select from getting included in the lock?

RockfordLhotka replied on Monday, February 16, 2009

Not that I’m aware of, no.

If the database connection is enlisted in a transaction when the stored procedure is invoked, all operations in that stored procedure are enlisted in the transaction.

If you have one stored procedure that does database updates and reads, I think you are stuck having them either in a transaction together, or not in a transaction at all.