[elephant-devel] DB Transactions and UI

[Ian Eslick]

Just a few short comments:

1) I think that DBs are designed for certain kinds of short-running  
transactions, but over time have been co-opted into supporting longer  
and more complex ones.

2) User interaction takes place in a completely different domain and  
I think that the constraints of that domain should not be conflated  
with the low-level DB semantics.

3) As for user interactions like checkout; I think you probably want  
to implement your own dependency tracking, for example a multi-stage  
process for buying tickets.  Here we use two levels of transactions  
and a DB field to perform application-level locking.

GUI:
User searches for and selects some tickets.  Those tickets are added  
to a user record that can be non-durable (simple, in-memory object)  
or durable (a database record updated after every page refresh)  
depending on the criticality of the application.

DB:
Short transaction moves these from an available state to a pending  
state with a timestamp and a reference to the user record so it can  
be automatically reclaimed if the user fails to complete the order in  
a certain timeframe.

GUI:
User goes through several steps, updating the user record at each  
step and then committing the order.

DB:
Transaction: change the ticket state to pending payment, user record  
updated to record the order info

Network:
Billing confirmation

DB:
Transaction: update ticket to purchased, user record to complete  
purchase, drop user object

GUI:
Send final confirmation to user.


Here we have a user-visible transaction implemented in a set of DB  
transactions.  Each DB transaction is short lived, even if the user  
transaction lasts minutes.  We use an application level lock (pending  
ticket) to lock a record.  This is better than DB locking, actually,  
because the DB lock also locks nodes in the BTree.

What is the default policy used for SQL-based web frameworks in PHP,  
Ruby-on-Rails, Java, etc?  This can't be the first time this question  
has been asked and answered.

Ian

On Jun 27, 2007, at 10:13 PM, Pierre THIERRY wrote:

> Scribit Mariano Montone dies 27/06/2007 hora 13:12:
>> I've been developing web applications so far, so I'll be describing a
>> web UI.
>
> I've been making web apps too, mostly. I don't think it's web  
> specific,
> but it's probably worse in web UIs, because they have a much worse
> latency than GUIs running locally.
>
>> There are cases in which a user wants to perform several  
>> operations in
>> a transactional way.
>
> Do you have examples? The only cases where my own apps needed several
> pages for an operation were creation operation, IIRC. On the other  
> hand,
> there is at least one famous example, namely most online shops  
> checkout
> operation. On Amazon, ISTR you go through 3 or 4 pages to checkout. I
> never tried to update my cart while doing that. I wonder how it  
> behaves.
>
>> 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.
>
> The good thing may be that the semantics and some practical  
> implication
> are msotly the same. The interactions of long transactions may be
> radically different than of extremely short ones, though.
>
> They may fail more, and be slower to restart. It would be fairly
> important to identify what transactions can be silently restarted and
> which need user input again. When this is needed, it will take a huge
> time for the new transaction to be committed, compared to when it is
> automatically restarted.
>
>> The consecuence is that the user may have performed operations based
>> on some knowleadge that will not hold if the transaction gets
>> restarted silently.
>
> That would be a bug. Transactions that are restarted are code, and  
> this
> code makes decisions based on data it sees. In STM, for example, the
> side-effects of a restarted transaction could be different from  
> those of
> its first run.
>
>> 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).
>
> The application must be aware of what data was read to build the UI  
> from
> which the user made decisions.
>
>> The algorithm would be similar to the one used for STM [1]. It  
>> doesn't
>> lock records and deadlock cannot occur.
>
> There is a huge difference with STM, in that there is a huge  
> latency in
> our case. This opens up a vulnerability on denial of service. If an
> hostile agent manages to modify some data often enough, it can prevent
> the user from committing his own modifications.
>
> This means that locking should be made available to the user, so  
> that it
> can protect himself from that (of course, that means he can DoS  
> others,
> and you have to deal with that also...).
>
>>      1) Does what I am saying make any sense? :)
>
> Definitely. That could be a good step towards making some applications
> more robust.
>
>>      2) How do you attack these problems in your applications?
>
> By ignoring them. ;-)
>
> Now that I think of it, I'm pretty sure many of my applications are
> vulnerable when multiple users make overlapping modifications. This
> means some modifications will be silently overwritten, sometimes  
> making
> the whole data set inconsistent.
>
> That's an interesting issue, BTW.
>
> Curiously,
> Pierre
> -- 
> nowhere.man at levallois.eu.org
> OpenPGP 0xD9D50D8A
> _______________________________________________
> elephant-devel site list
> elephant-devel at common-lisp.net
> http://common-lisp.net/mailman/listinfo/elephant-devel

-------------- next part --------------
A non-text attachment was scrubbed...
Name: PGP.sig
Type: application/pgp-signature
Size: 186 bytes
Desc: This is a digitally signed message part
URL: <https://mailman.common-lisp.net/pipermail/elephant-devel/attachments/20070628/0749729d/attachment.sig>