Safety of approach to manually managing database transactions?

Safety of approach to manually managing database transactions?

Old forum URL: forums.lhotka.net/forums/t/4309.aspx


rsbaker0 posted on Thursday, February 07, 2008

I have done some searching of the forum to see if this OK, and I think it is, but it might be helpful to have someone with more experience comment before I do a bunch of rework.

I don't think we can use the built-in transaction scoping because we support Access as a database (the BOL code is database-independent), so we have been managing ADO.NET transactions in the code.

Rather than try to pass the transaction object around internally as a parameter, I was going to store a reference to it in the LocalContext (or server-side ClientContext), and that way each BO that was going to try to save itself in the database could grab the active transaction and save itself in that context.

The Begin/Rollback/Commit code would be very good about maintaining the reference in the LocalContext, ensuring that the transaction would be there only during the execution of the transaction and removed/disposed afterwards if either a commit, rollback, or disaster occurred.

This already seems to work well in prototype testing. My main concern is that, when using a separate application server, is that (1) proper isolation is maintained when two different clients execute a transaction, and that (2) requests from the same client don't end up in separate transactions. The latter part is really of lesser concern and maybe I don't even need to worry about it, since in theory the client would only be saving one BO at a time and all the transaction processing is on the server-side of the data portal.

Does this seem like a reasonable approach?

 

JoeFallon1 replied on Thursday, February 07, 2008

I use that exact scenario and it seems to work well.

It also solves the case of 2 different root BOs using the same tr.

I posted my solution a long time ago - try searching for it (using LocalContext and Transaction).

You  may get another idea or 2 from it.

Oh - and I left the tr as a parameter for child collections and objects inside a root BO because I did not want to modify all my template code and it was easier to leave it alone.

Joe

 

rsbaker0 replied on Thursday, February 07, 2008

Thanks!

I'll search for your solution and check it out.

The initial problem I ran into that prompted this was that our BO may "lazy-load" child objects (or even other "using" type root BO's), and the object accessor doesn't know that a transaction is in progress, so it may end up using another connection and you deadlock on yourself (or read stale data not available outside the transaction, etc).

So, I want to funnel any database access through the "active" transaction, but it has to work with multiple clients doing transactions at once.

JoeFallon1 replied on Thursday, February 07, 2008

I had the same question.

As I recall Rocky told me that since the tr is occuring inside the DataPortal method and uses the local context that the thread it is running on is dedicated to the current user only. So multiple users can start their own transactions and it should be OK.

Joe

 

Copyright (c) Marimer LLC