Using Transactions in Data Access, formerly OpenAccess ORM Development (API, general questions)

Michael Kellogg

8 posts

Member since:
Oct 2009

Posted 30 Oct 2009 Link to this post

My app has a BL that owns and calls a DAL multiple times to insert about 6 different places in the datastore. Now, each BL method has a corresponding DAL method, and each DAL method calls a stored proc method that OpenAccess built for me to represent a stored procedure. Whenever those stored proc methods get called, they're using Transactions by default, and I can see this when I run a profile. That works great.

The problem is that if one of these methods fail, I want to fail the whole batch. So I have my BL call one of three DAL methods: BeginTransaction(), CommitTransaction(), or RollbackTransaction(). Those methods in the DAL all work on a private member variable IObjectScope _scope. It looks like so (only relevant code is included here):

    
                 public class DataStoreController

                {

                    private IObjectScope _scope;

                    #region Transaction Control 
        
                    /// <summary> 
        
                    /// Begins a new data store transaction 
        
                    /// </summary> 
        
                    public void BeginTransaction() 
        
                    { 
        
                        if (_scope == null) 
        
                            _scope = Database.Get("connFfops").GetObjectScope(); 
        
                        if (_scope.Transaction.IsActive) 
        
                            throw new DataStoreException("This datastore does not support nested transactions and one is already active.", null); 
        
                        _scope.Transaction.Begin(); 
        
                    } 
        
                    /// <summary> 
        
                    /// Executes a COMMIT TRANSACTION on the data store. If there is no current transaction, nothing is done. 
        
                    /// </summary> 
        
                    public void CommitTransaction() 
        
                    { 
        
                        if (_scope.Transaction.IsActive) 
        
                            _scope.Transaction.Commit();             
        
                    } 
        
                    /// <summary> 
        
                    /// Rolls back the current data store transaction, if there is one. 
        
                    /// </summary> 
        
                    public void RollbackTransaction() 
        
                    { 
        
                        if (_scope.Transaction.IsActive) 
        
                            _scope.Transaction.Rollback(); 
        
                    } 
        
                    #endregion

                }

            }

I call the Begin at the top of all the individual method calls (in the BL); then I call the Commit after they run. I have the Rollback inside the Catch block for the main method that runs through all the calls.

Something I'm doing is wrong, because I don't ever see any Transaction statements in the Profiler when it runs (and I'm single-stepping through the lines that are calling these transactions), and the end result is that when I throw an exception in the middle of my batch, I wind up with half a batch in the database. Frustrating.

Any idea what I'm doing wrong here?

Jan Blessenohl

Admin

707 posts

Posted 30 Oct 2009 Link to this post

Hello Michael Kellogg,
Your code looks good, I am just wondering how you get the object that you change. You have to use the same scope instanstance to execute the queries.

Greetings,
Jan Blessenohl
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

Michael Kellogg

8 posts

Member since:
Oct 2009

Posted 30 Oct 2009 Link to this post

That is a great point, and should have been obvious to me. They were all using locally-scoped variables. I changed them to use the member _scope variable and now I can see the ROLLBACK happen in the Profiler. However, the inserted records still exist.

Here is some code from the DAL controller:

    
                    public void ReceiveTrxHdr(EdiShipment shipment) 
        
                    { 
        
                        EdiReceiveTrxHdr trxHdr = shipment.TransactionHeader; 
        
                        if (_scope == null) 
        
                            _scope = Database.Get("connFfops").GetObjectScope(); 
        
                        if (trxHdr.ImportIdentifier == null) 
        
                            trxHdr.ImportIdentifier = 0; 
        
                        IQueryResult retval = StoredProcedures.ReceiveTrxHdr(_scope, 
        
                                                                       trxHdr.TrxType, trxHdr.Origin, trxHdr.Destination) 
        
                    }

And here is what it calls in StoredProcedures.cs (I shortened this, hopefully didn't accidentally remove something important):

    
                   public static IQueryResult ReceiveTrxHdr(IObjectScope scope, 
        
                        Char? edireceivepopulateedireceivetrxhdrsptrxtype, 
        
                        string edireceivepopulateedireceivetrxhdrsporigin, 
        
                        string edireceivepopulateedireceivetrxhdrspdestination) 
        
                   { 
        
                        IQuery query =  
        
                            scope.GetSqlQuery("receive_trx_hdr_sp ?,?,?,?,", 
        
                            null, 
        
                            "VARCHAR trx_type,VARCHAR origin,VARCHAR destination"); 
        
                        IQueryResult res = query.Execute(new object[]  
        
                            {trxtype, origin, destination}); 
        
                        int a = res.Count;//Actually executes the query 
        
                        return res; 
        
                    }

When I throw an exception, my RollbackTransaction() method gets called and the value of _scope at that point is that "IsActive" is true and "Level" is 1, which sounds right to me. However, "IsDirty" is false and nothing ever shows up inside "DirtyObjects", which seems wrong to me. And of course, the end result is that the INSERTs never get removed.

Your thoughts?

IT-Als

381 posts

Member since:
Sep 2008

Posted 02 Nov 2009 Link to this post

Hi Michael,

One potential problem I can see is the line:

if (_scope == null)

_scope = Database.Get("connFfops").GetObjectScope();

because this should really never happen if I understand you correctly. Right?

You already call scope.BeginTransaction in the BL method thus the scope is (or at least it should be) initialized.

Basically the code should look something like this (pseudo code):

BL:
obtain a scope
try
{
scope.BeginTransaction();

// Call DAL methods
myDal.PerformSomething();

scope.CommitTransaction();
}
except
{
scope.RollbackTransaction();
}

The point is to keep the scope alive during the business layer transaction. For that purpose you can create an external (to the BL and DAL) that will provide you the scope upon request. The thing here is that this mechanism must provide you the same scope as long as you're in the same business transaction.
We created a ContextManager for that purpose, but there's also a ScopeFactory class in one of the Telerik examples. Still, the same principle applies: During the business transaction the same scope must be used all the way down the lowers layers (in your case the DAL).. also during multiple calls from BLL to DAL.

Regards

Henrik

Michael Kellogg

8 posts

Member since:
Oct 2009

Posted 02 Nov 2009 Link to this post

I agree the scope needs to be the same throughout. And yes, technically that line could generate a new scope. But it's not in this case. The line is there in case I do not call BeginTransaction() prior to that method. In this case, I always am calling it first and so the initalizing line there is not being called. Indeed, as far as I can see during single-step, my scope is correctly staying the same throughout all my work. As I mentioned, I see the "Level" property increment & decrement at the right times, but it looks to me like the scope's transaction does not realize that objects are "dirty" because calling the stored proc's has inserted records. No BEGIN TRAN or ROLLBACK ever seems to appear in the Profiler (I have occasionally seen a ROLLBACK after the app quits, but it doesn't seem like my lines are what's causing it).

My thought process right now is to explicitly call "BEGIN TRANS," perhaps through something like "GetSqlQuery("BEGIN TRAN", "")" or something like that. Clearly I need SQL Server to start a transaction so that I can roll back all these Inserts if need be. Does this make sense?

Michael Kellogg

8 posts

Member since:
Oct 2009

Posted 02 Nov 2009 Link to this post

Realizing OpenAccess does not support direct SQL such as "BEGIN TRAN," I tried to do a Scope.Add() on each changed object in their respective stored proc call methods. This increased the "Level" property and set "IsDirty" to TRUE, but upon executing Rollback, there still was no effect: The inserted records remain and there was no "Rollback" SQL command found in the profile.

I have now also read the whole documentation on System.Transactions and have tried to implement an implicit transaction that way using a TransactionScope object, this is not working either. Same exact results.

This is really getting frustrating, and I'm into this several days, now, without resolution. Honestly, what I'm attempting to do seems very routine to me: Load up several objects with values. Call stored proc's to insert records in the objects' underlying tables, filling in the parameters manually with the objects' values. Perform a rollback in case of trouble along the way. If I could just execute transaction statements manually, I would have finished this last week. If I can't get this resolved my whole DAL is shot. As it is I'm eating billable hours here.

IT-Als

381 posts

Member since:
Sep 2008

Posted 03 Nov 2009 Link to this post

Hi Michael,

Sorry the late answer.. and that the previous answers wasn't of much help.

Anyway, I think you're and the right path regarding the System.Transaction usage, albeit the enlisting must be explicit before your stored procedure call I guess. You're not seeing dirty objects..Why? I think it is because you never change the instance... instead you call the provided stored procedure static method and passes each of the values needed as parameters.. but the instance is unchanged.

From what you describe it actually looks like the stored procedure doesn't participate in the transaction. Maybe someone from Telerik can confirm that this is actually a bug or behaviour as designed.

Another thing came to my mind (I know it is kind of a work-around, but still..):
Do you have an emerging need to use stored procedures (I guess you ticked the "Use stored procedures" in the mapping dialog) for your application?... If not, try to switch off this usage and load (create) and manipulate the persistent instances the "natural" way..all inside your business transaction.

Regards

Henrik

Michael Kellogg

8 posts

Member since:
Oct 2009

Posted 03 Nov 2009 Link to this post

Hi Henrik,

No need for an apology since you're not from Telerik; I appreciate your help.

I think you are right-on with your analysis. Because I'm not really "dirtying" the object itself, OA doesn't think there's any need for a BEGIN TRAN. It seems that OA is just a little too smart. I planned on trying "explicit" transactions next, but not sure whether that will do anything for me. When I used implicit ones, and also when I tried letting OA manage the transactions and I called Scope.Add() on each of my objects, I could see dirtying happening, but still no transaction ever took place on the SQL Server. Again, I think OA is basically trying to manage all that itself, in memory, rather than on the server. I'm betting it's by design for some reason.

The reason I'm doing it in this unorthodox sort of way is that my client had all these Insert stored proc's already built when I walked in, and they wanted me to utilize them. When I analyzed them I learned that they were mostly (but not totally) just simple Inserts, taking a boatload of parameters that essentially mapped the table they were inserting to. In some cases there were multiple-table inserts happening inside the proc. So what I did was do a reverse-mapping in OA against all the tables I was going to need to Insert to so I'd have objects in my code that represented each table. Then I reverse-mapped each of the stored proc's they wanted me to use. With that in place, I just carried around the various objects I needed, populating them in another section of my app, and passed the objects into my DAL's method for that particular table. The DAL method calls the reverse-mapped stored proc method directly. So you see, because of this the objects are technically "persistent," but they never retrieve from the database (this app is all about pushing into) and they use a custom stored proc to do the Inserts.

I'm open to the idea that I'm not using the ORM exactly as intended, but everything works. Except for this transaction problem. I don't want to have to scrap the DAL just for this one small problem (though I'm getting close to conceding I may have to).

IT-Als

381 posts

Member since:
Sep 2008

Posted 04 Nov 2009 Link to this post

Hi Michael,

After reading your post I am almost sure, that the analysis I provided is correct. Also, what I don't understand is this:

If you use System.Transactions namespace and the your BLL starts, commits and rollbacks (in case of exceptions) the transaction. Then, if your DAL enlist (explicit) to the running System.Transaction and an exception occurs somewhere, the System.Transaction should rollback everything that was enlisted up to that point. This is exactly what System.Transactions is designed for.

Can anyone from Telerik elaborate on this?

Regards

Henrik

Jan Blessenohl

Admin

707 posts

Posted 16 Nov 2009 Link to this post

Hello Michael,
Herik is completely right, we try to handle the SYS.TXN in a smart way but the problem comes from a different fact.
The problem is that we do not expect that the SP is changing something in the database. Because of that the SP gets it's own connection and is just executed without participation in the SYS.TXN. At the moment their is no way to avoid that. You can only executed the SP via a parallel ADO connection that is also enlisted. Not nice but I have no better idea.

Best wishes,
Jan Blessenohl
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

IT-Als

381 posts

Member since:
Sep 2008

Posted 17 Nov 2009 Link to this post

Hi Jan,

Thanks for elaborating on/confirming this issue.

/Henrik

David Thompson

29 posts

Member since:
Feb 2010

Posted 09 Mar 2011 Link to this post

Hmm,

Is this still the case? Is there still no way to execute a stored procedure in the same scope as the current open transaction? I need to execute a stored procedure prior to doing any other DML statements as the database needs to know who the current user is. I am at a stand still on my project because of this issue.

David

Jan Blessenohl

Admin

707 posts

Posted 10 Mar 2011 Link to this post

Hello David Thompson,
We are using a connection pool underneath, each operation can potentially get a different connection. This allows to perfectly scale in web and application server scenarios. There are some tricks where the connection is bound to the scope or context but that can only be done by additional C/S calls.
You can call scope.transaction.flush after you have changed or inserted data, the connection has to be bound here to be able to rollback the flush later. But it is not a recommended way. And you might keep the connection open for a too long time.

Best wishes,
Jan Blessenohl
the Telerik team

Registration for Q1 2011 What’s New Webinar Week is now open. Mark your calendar for the week starting March 21st and book your seat for a walk through all the exciting stuff we ship with the new release!

David Thompson

29 posts

Member since:
Feb 2010

Posted 10 Mar 2011 Link to this post

I'm confused. I understand the use of connection pooling but how can you use different connections and still guarentee the ability to rollback all changes under the same scope. I KNOW this is not possible with Oracle and that is the database I am using.

Are you saying the following CODE will not rollback because ot this issue?

Using (var db = new ORM())
{
try
{
db.Scope.Transaction.Begin();
//DAL and Stored Procedure calls using the same (db) rlinq file

var itemRepository = new ItemRepository(db);

itemRepository.IdentifyUserSession(

ASPSessionBridge.getValue("SessionID"), "NTE.NTEx.Web.ItemTabItem.btnSave_Click()");

//Create, uppdate and destroy object all the time adding and updating them using the same db
db.Scope.Transaction.Commit();
    }
except
    {
    db.Scope.Transaction.Rollback();
    }
}

Jan Blessenohl

Admin

707 posts

Posted 11 Mar 2011 Link to this post

Hi David Thompson,
OpenAccess works in two phases, before you call commit or rollback all operations are stored in memory. During commit all changes are executed against the database. Which means the rollback is mainly a memory cleanup operation. Only if we have to get information from the server, like autoinc values or you call flush, we have to keep the same connection until the database transaction can really be committed or rollbacked. Usually all database operation outside commit or rollback are read operations.

Regards,
Jan Blessenohl
the Telerik team

Registration for Q1 2011 What’s New Webinar Week is now open. Mark your calendar for the week starting March 21st and book your seat for a walk through all the exciting stuff we ship with the new release!

David Thompson

29 posts

Member since:
Feb 2010

Posted 11 Mar 2011 Link to this post

Ok, that makes alot more sense. So, I could implement this if I flush the transaction after the first update statement and I do NOT use stored procedures. I can work around that for the moment.

David

Jan Blessenohl

Admin

707 posts

Posted 11 Mar 2011 Link to this post

Hello David Thompson,
After the flush you can use SPs as well, we are using the same connection.

Greetings,
Jan Blessenohl
the Telerik team

Registration for Q1 2011 What’s New Webinar Week is now open. Mark your calendar for the week starting March 21st and book your seat for a walk through all the exciting stuff we ship with the new release!