We have an interesting setup where we have SqlTransactions persisted to Application memory on a web application. We're looking into moving our web application into an NLB scenario, so we need a way for those Transactions to not be tied to a single machine's memory. Is there any way we can accomplish this..I've tried serializing a SqlTransaction and a SqlCommand, neither seem to work. Do I need to start looking into the System.Transactions namespace?
+2
A:
I don't believe this can work. Transactions are designed to be temporary and local.
edit
Maybe "transient" is a better word than "temporary".
Steven Sudit
2009-07-31 17:47:42
+1
A:
It makes no sense to store a SqlTransaction in Application state. A transaction should not span a single request.
I will assume that you know that Application state is shared by all users.
Why did you use Application state instead of Session state? Session can work across load balancers, either using the State Server, or SQL Server forms of session persistence.
John Saunders
2009-07-31 17:53:00
Why shouldn't a transaction span more than one request? That's the whole point of transactions!
Randolpho
2009-07-31 17:57:21
No, it's not. I mean HTTP requests, BTW. The purpose of a transaction is to ensure that multiple database operations either succeed or fail as one. If you have a web site that performs one operation in one request, then comes back and does another operation in another request, then you really don't understand how the web works. What happens if the user shuts off his computer after the first operation?
John Saunders
2009-07-31 18:42:36
I know how the web works just fine, TYVM. But transactions can and have spanned multiple requests in the web world for *years*. Case in point: WCF transactions ( http://msdn.microsoft.com/en-us/library/ms730266.aspx ). I think you may be getting hung up on the fact that he asked about SQL transactions. The fact is he already has transactions spanning across multiple HTTP requests; he just wants to move them across multiple machines because he's load balancing now.
Randolpho
2009-07-31 22:03:35
WCF transactions are distributed transactions, and are meant to be so. I guarantee you I know about those. This appears to be different: treating a web application like a desktop application, holding a transaction in `Application` state the way one would have held it in a member variable of a form, or in a global variable. It fails to take into account that the web works much better in a stateless manner, and that things happen between one request and the next, and that the level of control in a web application is less than in a desktop application. Hope I'm wrong about the reasons.
John Saunders
2009-08-01 00:25:25
Even in a desktop application, this is a bad idea, and has been for decades. You've got the completion of a transaction waiting on user input! What if they go to lunch! You're keeping the transaction, and the corresponding locks, sitting around waiting for people!
John Saunders
2009-08-01 00:31:15
Oh, I agree, the web works *best* in a stateless manner, but let's face facts: people have been trying to add state to the web for, what, 15 years now? 16? It started with cookies, then we got server-side sessions... WS-AtomicTransaction protocol... it grows and grows. There are legitimate reasons to have atomicity across multiple requests, and they deal primarily in the need to allow the client to respond dynamically to server responses. I'm *assuming* the original asker is doing this after having weighed the options.
Randolpho
2009-08-01 02:07:34
Regarding "what if you go to lunch?": transactions *can* have a timeout, you know. Make it small.. a minute or two, ten if it's user-based rather than a web service.
Randolpho
2009-08-01 02:08:47
I don't see where SqlTransaction has a timeout. Also, isn't SqlTransaction bound to a particular SqlConnection? Will the OP also serialize the SqlConnection as well? This is truly not well thought out.
John Saunders
2009-08-01 03:46:03
A:
You may want to take a look at the Distributed Transaction Coordinator. I haven't personally used it for handling transactions across multiple requests, but that's its purpose. The link is the developer's guide for COM and Win32. Here's a tutorial that I think may help you get started on the .NET side of things.
Randolpho
2009-07-31 17:55:51
There's no need in .NET to program against DTC. A SqlTransaction will be promoted to a distributed transaction if necessary.
John Saunders
2009-07-31 17:58:21
@Randolpho: If I understand correctly, the point of DTC is to allow a single request to span tiers. In other words, it's for going deeper, not wider. Am I mistaken?
Steven Sudit
2009-07-31 20:03:44
A:
I haven't done this kind of thing before, but SqlTransaction has a CreateObjRef method, which returns an ObjRef, which is serializable. And ObjRef seems to be what you are supposed to mashal back and forth.
quillbreaker
2009-07-31 20:57:25
A:
Firstly serialization is only meant to be used to capture a snapshot of values/value types-sstudf like strings numbers etc. A transaction is something that is "alive" fir short period of time ( timeouts) for a specific user, with x resour es( typically connections to the database). Since these things are constantly changing serislizing and then restoring them would really screw things up if it were possible, as lots of things would have changed and you would have chaos.
If transactions were meant to be serializable they would have implemented the interface, what you are asking for is just plain bad/dumb.
mP
2009-08-01 00:51:51