database - Are long-living transactions acceptable?

Question

I am thinking about using transactions in 2-tier WPF (or windows forms) applications in following way:

We can begin new transaction when we open new form for editing data, edit and persist changes transparently in this transaction. Then we can click "OK" button and commit transaction, or "Cancel" button and rollback it. If we want to open another dialog window with this data, we can use nested transactions.

The question is: Is this way of using transactions acceptable or not? I know that there are a lot of different ways to implement such logic, but I'd like to list advantages and disadvantages of this one.

Answer 1

Long-living transactions were a topic for hot discussion in academia around... 1980s I'd say. The problem is that a long-living transaction almost certainly creates a deadlock in a pessimistic execution and almost certainly requires complicated conflict resolution in an optimistic execution (for numbers you can consult Jim Gray's paper "The Dangers of Replication and a Solution", but shortly deadlocks rise as the fifth power of the transaction size, and the probability of a collision rises as the second power).

Now there were different proposals to the problem, like "sagas" from Salem and Garcia-Molina, "nested transactions" and so on (another Jim Gray's paper "The Transaction Concept: Virtues and Limitations" has several pages about that in the end). Most of the proposals deal with a transaction model, weaker than ACID. For example, "long transactions" may have to expose their intermediate results, which violates the Isolation property. But none of the proposals quite made it to the industry, so to say. Mostly because those techniques weren't really... simplifying, neither weren't necessary to solve the actual business problems.

So, to answer your question: no, long-living transactions are not welcome in the mainstream DB engines.