[elephant-devel] DB Transactions and UI

[Mariano Montone]

Hello,
      this mail is quite off-topic. It is not related to elephant itself,
but to user interface design and databases.

      I think there are problems with the way db transactions work and how
the user interacts with the application. I've been developing web
applications so far, so I'll be describing a web UI.

      There are cases in which a user wants to perform several operations in
a transactional way. That means he wants, among other things, isolation:
don't invade others view of the data until he has commited the changes
(probably hitting an "accept" button) and work with a local memory
"snapshot".

      The problem is that long transactions are not supported by today
databases. When I say long transactions I mean the ones that have a lifetime
related to the user interaction with the application. 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 and performing several requests).

      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 think no db engines address these problems. 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
go on 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).

      The algorithm would be similar to the one used for STM [1]. It doesn't
lock records and deadlock cannot occur. Progress cannot be guarranteed,
though they claim it is unlikely to occur in practice. The difference would
be in removing silent restarts.

      I hope I've been able to explain my problems successfully to you. I
can reformulate them in case I haven't explained them clearly. On the other
hand, I may be totally wrong.

      My questions are:
      1) Does what I am saying make any sense? :)
      2) How do you attack these problems in your applications?

      Sorry for the long mail and thanks for your time.

      Cheers,
            Mariano
        marianomontone at gmail.com
        http://www.eureka-consulting.com.ar
        http://phpwebbuilder.sourceforge.net

[1] Software Transactional Memory -
http://research.microsoft.com/~simonpj/papers/stm/stm.pdf
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.common-lisp.net/pipermail/elephant-devel/attachments/20070627/5e2cacf1/attachment.html>