CODEWITHSUNDEEP
c#asp.net.netsql-server-2008
Zahid
Zahid

Reputation: 1830

How to rollback transaction at later stage?

I have a data entry ASP.NET application. During a one complete data entry many transactions occur. I would like to keep track of all those transactions so that if the user wants to abandon the data entry, all the transaction of which I have been keeping record can be rolled back.

SQL 2008 ,Framework version is 4.0 and I am using c#.

Upvotes: 1

Views: 1075

Answers (4)

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93424

This is always a tough lesson to learn for people that are new to web development. But here it is:

Each round trip web request is a separate, stand-alone thread of execution

That means, simply put, each time you submit a page request (click a button, navigate to a new page, even refresh a page) then it can run on a different thread than the previous one. What's more, even if you do get the same thread twice, several other web requests may have been processed by the thread in the time between your two requests.

This makes it effectively impossible to span simple transactions across more than one web request.

Here's another concept that you should keep in mind:

Transactions are intended for batch operations, not interactive operations.

What this means is that transactions are meant to be short-lived, and to encompass several operations executing sequentially (or simultaneously) in which all operations are atomic, and intended to either all complete, or all fail. Transactions are not typically designed to be long-lived (meaning waiting for a user to decide on various actions interactively).

Web apps are not desktop apps. They don't function like them. You have to change your thinking when you do web apps. And the biggest lesson to learn, each request is a stand-alone unit of execution.

Now, above, I said "simple transactions", also known as lightweight or local transactions. There's also what's known as a Distributed Transaction, and to use those requires a Distributed Transaction Coordinator. MSDTC is pretty commonly used. However, DT's perform much more slowly than LWT's. Also, they require that the infrastructure be setup to use a DTC.

It's possible to span a transaction over web requests using a DTC. This is done by "Enlisting" in a Distribute Transaction, and then somehow sharing this transaction identifier between requests. But this is a lot of work to setup, and deal with, and has a lot of error prone situations. It's not something you want to do if you have other options.

In general, you're better off adding the data to a temporary table or tables, and then when the final save is done, transfer that data to the permanent tables. Another option is to maintain some state (such as using ViewState or Session) to keep track of the changes.

One popular way of doing this is to perform operations client-side using JavaScript and then submitting all the changes to the server when you are done. This is difficult to implement if you need to navigate to different pages, however.

Upvotes: 5

Tim M.
Tim M.

Reputation: 54368

If you are just managing transactions during a single save operation, use TransactionScope. But it doesn't sound like that is the case.

If the user may wish to abandon n number of previous save operations, it suggests that an item may exist in draft form. There might be one working draft or many. Subsequently, there must be a way to promote a draft to a final version, either implicitly or explicitly. Think of how an email program saves a draft. It doesn't actually send your message, you may abandon it at any time, and you may recall it at a later time. When you send the message, you have "committed the transaction".

You might also add a user interface to rollback to a specific version.

This will be a fair amount of work, but if you are willing to save and manage multiple copies of the same item it can be accomplished.

You may save the a copy of the same data in the same schema using a status flag to indicate that it is a draft, or you might store the data in an intermediate format in separate table(s). I would prefer the first approach in that it allows the same structures to be used.

Upvotes: 0

techBeginner
techBeginner

Reputation: 3850

You can also store them on temporary Table and move those records to your original table 'at later stage'..

Upvotes: 0

Vikdor
Vikdor

Reputation: 24124

From your question, it appears that the transactions are complete when the user exercises the option to roll them back. In such cases, I doubt if the DBMS's transaction rollback semantics would be available. So, I would provide such semantics at the application layer as follows:

  1. Any atomic operation that can be performed on the database should be encapsulated in a Command object. Each command will implement the undo method that would revert the action performed by its execute method.
  2. Each transaction would contain a list of commands that were run as part of it. The transaction is persisted as is for further operations in future.
  3. The user would be provided with a way to view these transactions that can be potentially rolled back. Upon selection of a transaction by user to roll it back, the list of commands corresponding to such a transaction are retrieved and the undo method is called on all those command objects.

HTH.

Upvotes: 1

Related Questions