I want to build in transaction reversal process in my database design. Example: I have a process which inserts/updates records in a number of tables. But when I want to reverse the transaction same day or later date, it must reverse itself. Can I include such a facility in database design itself?
Whew! This question could be correctly answered almost any way at all.
Yes, it is physically possible to build a transaction processing facility into a database schema. This is one of the fundamental requirements for true ACID database transactions. The problem is that transaction logging is a very low level process that is normally completely invisible to the user. Like law and sausage, those who enjoy the fruits of transactional management shouldn't inspect the process too closely!
You need to consult one of the college level database management books or one of the better "public domain" database engines for details on how transactions are managed. You essentially need to safeguard each of the ACID components.
A is for atomicity. Each transaction needs to be atomic from beginning to end. No part can be allowed to commit unless the whole transaction commits.
C is for consistency. Once a transaction starts, all of the changes that it makes to the database need to be kept in a "hidden" state until the transaction ends. If the transaction is rolled back, none of the changes ever become visible. If the transaction is committed, all of the changes become visible at once.
I is for isolation. While a transaction is in progress, it may see the changes that it is making to the database, but no other connection to the database should be allowed to see those changes.
D is for durability. Once the transaction is committed, the data must be available to all connections and must not change unless acted upon by a client. Even if the database goes down a millisecond after the commit completes, the data must be available whenever the database comes back online.
There is more than one way to implement the ACID principles. I would not want to have to build the logic needed to support this kind of processing into any application due to the complexity that it would add. It can be done (I've had to do it in the past), but I certainly wouldn't want to be the one to build an application to do it today!
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your database design -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.