Acid And Long Transactions

Here is an article suggesting there are problems with AcId under what it calls "long transactions".

http://www.theserverside.com/articles/article.tss?l=AcidShortDoses

Regarding the travel reservation example, generally I create a temp buffer that stores the in-progress info. Only when the final "Confirm" button is pressed does it become an ACID transaction. (I prefer "work tables" for such buffers, but there are many alternatives for non-table fans, some mentioned below.) Generally it requires some kind of unique user or session ID to keep different users' stuff separated. Most web frameworks provide unique session ID's. I have used this approach fairly often, and have not found any real problems with it.

I suppose it can be a bit resource intensive, and it may require a periodic clean-up job on old, unfinished "transactions". For example, put a date-stamp on each record, and use a periodic batch job to delete stuff that is a day or so old. Remember that finished transactions should be cleared out right after the final transaction is accepted and committed. Thus, only problem or abandoned transactions should remain in the work buffers. These remaining artifacts can even be used for marketing research, such as studying the reasons behind shopping-cart abandonment.

One possible design drawback is that the work buffers must mirror the final table schemas. If you change one, you have to remember to change the other. However, theoretical technologies such as "dynamic relational" (see MultiParadigmDatabase) would alleviate such by allowing dynamic copying of the "master" schema.

An alternative to copying is perhaps to put the in-progress stuff in the primary tables, and simply mark them with a "finish" status only when they are complete. I often feel uncomfortable about this approach though. But, I am having trouble putting my finger on why it bothers me right now.

I have also worked on web apps where they try to pass the work buffer info around to other web pages via long parameters and array serialization. It was butt-ugly. Do that only as a last resort. They didn't want to use "session variables" for some kind of load-balancing reason. I don't know exactly why, but the people who did it had left the project already, so we were stuck with their design. Storing arrays in session variables can be a headache also, especially when the information has a complex structure such that a lot of parsing or nested arrays, and/or compound or multiple keys are needed. Tables are happier for this stuff. If performance is a problem, then perhaps put the buffer tables on a separate server from the "regular" DB server to divide the load.

-- top

What you sacrifice by your 'speculative transaction' approach is atomicity, isolation, and consistency, leaving only the 'durable'. A 'transaction' isn't about just a sequence of updates to a database; it is about making decisions based on what was read earlier, and doing so atomically - as if there were nobody else reading and writing to the same database. In your case, the user might learn a room is available, select it, then do some other things involving the flight and car rental, then finally confirm and find out the room is not available - somebody else has taken it.

I'm not saying your approach is a bad one, but it also is rather specialized, not ACID, and is not at all a viable solution to the problems people are attempting to solve by studying and implementing LongRunningTransactions.