[Zope-DB] Avoid Lost Updates?

[kapil thangavelu]

On Wednesday 16 October 2002 03:57 am, Martin Gebert wrote:
> Hi all!
>
> I didn't found much about this issue, so I'd like to ask this list.
> Please correct me if I'm stating anything wrong...
>
> Zope provides transaction management for RDBMS bound to it's internal
> transactions - begin and end of a TA are marked by Request and Response
> (request for the page until end of uploading it). This surely is a
> convincing and quite secure concept, working for the most practical
> purposes.
> The problem is how to manage longer transactions which spread over two
> or more requests, esp. in combination with locking? Locking is bound to
> commit/rollback cycles, too.

>
> The problem I face is the following:
> Consider a person retrieving his personal data from an RDBSM
> (MySQL/InnoDB) via Web (Zope) to change it. After that he presses the
> submit button to write the data back.
> If this person takes a while for entering, and some other person (e. g.
> with a manager role) changes his data in the meanwhile and saves
> *before* the other one, this update will be overwritten with the update
> of our first person and is simply lost.
> Doing an exclusive lock ("for update") on retrieving isn't of much use,
> for the lock will be set back at the moment the result page was sent to
> the client; the transaction is at it's end there and a new one begins,
> ending with sending the updated data back to the DBMS (or cancelling the
> process).

>
> I know this is an old problem, and due to the statelessness of the HTTP
> protocol. But I've already seen a Zope site which was trying to keep the
> Request up until the submitting of the site, which showed as a
> neverending retrieval of the page (the "Loading..." sign in the browser
> didn't stop until you either hit the "Stop" button, went to another
> page, or pressed "Submit").
>
> My question is:
> Is there any way in Zope to extend TAs over a couple Requests, e. g. to
> keep the Request open as described above? Is this a practical solution,
> and what are the problems (there are at least timeout issues)? May there
> be a solution which is based on the DBMS (ignoring commits to keep the
> TA open)??? Or is this one of the long-known-and-so-far-unresolved
> problems of Web programming?

extending zope transactions to multiple requests, is non trivial, and not 
advised, though doable.

storing the data into a session, or using an explicit lock table (a table with 
lock ids, flags, creation_times, and table names) that provides a token to 
use that is checked when updating the table seem like decent alternatives...

does the problem really get any better, with exclusive db locks, someone else 
may still come in and clobber updates, after the fact. if you really don't 
want to loose data, you should try an rdbms that supports triggers and create 
an audit table.

hth,

kapil