I was reminded recently of the fact that long running transactions, especially those involving multiple databases, cannot be made to follow the ACID rules of database transactions.  On its face, this is completely true.  However, I’m thinking that there are mechanisms that could be used to allow the positive effects of ACID to remain, even when the actual implementation is not available in the automated manner we are used to.

As a refresher: A is atomicity (which means that the entire transaction has to occur or not occur… failure means to roll it back).  C is consistency (if part of a transaction breaks a rule, then the entire transaction fails), I is isolation (two people performing actions on the data should not affect one another), and D is durability (committed transactions are not lost when power fails or other adverse events occur).

So if a long running transaction causes a change in Database D1, then is transmitted to a remote system, where the next day, it affects Database D2, (where it could fail), then we lose both Atomicity (because the transaction was committed to D1 even before it is known to be successful at D2) as well as Isolation (since a user could ask both databases for info in the mean time, and get two different answers.

However, the positive effects of ACID come when viewed from the viewpoint of the user.  The user is not a concept.  He or she is real.  They have a goal and a purpose for using the database.  If you can present ACID-like interactions to them, then these flaws can be minimized.

In order to do this, I’d suggest that a “system of record” is kept seperate from the systems interacting in the transaction.  An interaction with the “system of record” would occur at the last step of the long running transaction.  That interaction would only occur if all prior interactions were successful.  All users who want the “correct” information would be encouraged to check there.  This gives you a kind of atomicity, since a change would not occur in this system until all parts of the transction are complete. 

Similarly to Atomicity, Isolation can be met from this location as well, since queries to this system would not return different results depending on the status of various transactions, until those transactions completed and updated the system.

So while long running transactions don’t meet the ACID test, systems that support and defect long running transactions can be set up to provide the benefits of ACID transactions fairly readily.