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.

Answer 2

Here is a few problem that you might encounter if you go this way

• connection reset/close/timeout (if user goes to the bathroom)
• database configuration (database are mostly pre-configured for many short transactions, not long ones. E.g. the undo log that keeps track of what has been done during the tx may need to be tuned)
• lock issues, even maybe deadlocks (the longer the transactions are, bigger the chances are the same lock is acquired twice possibly in conflicting order)

This is a discouraged practice. Use optimistic locking instead. Read data when necessary, keep a copy in memory. When dialog is closed, attempt to synchronize the changes with the database. If data have been modified in the database in between, the action is aborted. The probability that it fails will depend on the numer of users, etc. But this is frequently acceptable in practice.

Answer 3

Long running transactions will seriously affect your ability to scale.

I would avoid if at all possible.

As others have noted, you should not keep a transaction open while waiting for user input.

Answer 4

NO, do not keep a transaction open while waiting for user input. It is bad design and will result in locking problems with your transactional resources (database).

You need to rethink your approach. Why would you have the transaction open while the user is filling in a form? We use transactions to manage concurrency and locks on shared resources. Filling out a form doesn't really qualify.