DB Transactions and UI

[Chris Muller]

Hi Mariano, I have a couple of brief comments.

>       The problem is that a user wants to work in a transactional way, but
> that is not supported by today databases. That's because transactions are
> meant to be extremely short and their lifetime cannot be associated to the
> UI transaction lifetime. The UI transaction may problably take several
> operations to complete (several request from a web application point of
> view), but in general transactions implementations use locks for concurrency
> control. So I cannot keep a transaction opened till the user finishes the
> operations from the UI (for example, he may want to create an object of the
> model, then add some items to some collection of that object, all in a
> transactional way).

The longer the transaction, the greater chance for for a commit
conflict.  So how should a database handle this?  Same question
regarding isolation.

I have thought a lot about trying to incorporate a generic "long
transactions" function for Magma, where each user session tranparently
imports chunks of the db they're working with into their own local
copy.  Later, when they "sync up", it checks every changed object
against the original and presents a list of conflicts.  Once they're
all resolved the commit can succeed.

But I think a simpler answer lies in handling this type of transaction
at the application level.  It lets users build a persistent model
(spanning many transactions) in suspense and promote its status to
"official data" or whatever.

>       What's more, db transactions get restarted silently in case of
> concurrency issues (records read or write failures, deadlocks; this depends
> on concurrency control implementation) and the application cannot be aware
> of that. The consecuence is that the user may have performed operations
> based on some knowleadge that will not hold if the transaction gets
> restarted silently. For example, in the UI transaction, I may have done some
> query and present the results to the user for him to make a desision. Then
> the user goes on performing some operations and commits the transaction
> based in the results that were presented to him. As a result, the db
> transaction has a query and some updates as a result. When the db
> transaction commits, some concurrency related errors arise, so the db engine
> decides to restart it silently. But now, the query results are different as
> a consecuence of other user's changes, but the transaction still commits
> successfully. The application is not aware of that and the user is not
> warned of that (the new query results are never displayed again to the
> user).

I can't speak for others, but Magma doesn't silently restart any
transactions, ever.  That example sounds like mismanagement of a
dirty-read situation.

>       I think no db engines address these problems.

Once again, I'm not too clear about what you expect the db to do about it..

> Locking is not quite
> possible to have (we don't want users to be locked for so much time). So we
> need non-locking algorithms. I think the appropiate one would be some kind
> of optimistic algorithm. Changes are performed isolated without blocking
> anything. At the time of doing the commit, the transaction is validated. If
> it is not valid (some other user modified our transaction "memory"), then
> the application gets notified of that. The application is able to notify the
> user and provide mechanisms for recovering. The user could be presented with
> the new data, decide to
> goon with the commit, let the transaction restart silently till it is
> successful (he may not care about the result presented to him, he just wants
> his operations commited).

Yeah, Magma uses optimistic locking.  GemStone also uses optimistic
and supports pessimistic too.

Cheers,
  Chris