Precisely - it is not a one pass operation.

On Fri, 2005-10-07 at 07:40 +0200, Kjell Rilbe wrote:
> One problem that has to be dealt with:
>
> 1. A starts edit.
> 2. B starts edit.
> 3. B commits.
> 4. A tries to commits, but conflicting updates require A to review
> suggested merge.
> 5. C starts edit.
> 6. C commits.
> 7. A is done revieweing suggested merge and tries to commit again.
>
> In step 7, the application has to be able to detect that C has made
> additional changes - it can't just do a save without checking the record
> version number, because than it would overwrite C's edits.
>
> Instead, in step 4, it should read B's record version number and use
> that in step 7, using the same logic as in step 4. It would then keep
> repeating steps 4-7 until no user C makes edits between step 4 and 7.
>
> Kjell
>
> David Johnson wrote:
>
> > The problem with the false write method is someone can go for lunch (or
> > home for the night) after the false write, leaving the row in what is
> > logically a locked state. For key tables, this has the potential of
> > shutting down a chunk of the business until that person returns to
> > complete their transaction.
> >
> > Another option is to use a pseudo-conversational model of interaction
> > like CICS or web applications use.
> >
> > Read data from the database and commit.
> >
> > User does work in the GUI. They alter a copy of the original data,
> > leaving the originally read data intact in memory (or attached to the
> > session in web apps).
> >
> > Write changed data back to the database and commit.
> >
> >
> > Where it gets a bit tricky is that a trigger needs to update a "current
> > version" column. In systems with nanosecond clocks, a timestamp works
> > well. For firebird, a trigger that autoincrements the column is better.
> >
> > Your clause should be conditional upon "where primaryKey
> > = :old_primaryKey current_version = :old_current_version" - the old
> > values are passed into the update SQL as parameters. I don't recall if
> > Firebird throws an exception (SQL Code 100) in this case, but at the
> > very least you can check for rows affected = 0 for a fail condition.
> >
> > On fail, you go into error resolution mode. Since you have the current
> > version, the old version, and your modifications, you can determine
> > which fields you edited and which someone else edited. If the columns
> > that you edited do not overlap the ones that someone else edited, then
> > you are usually safe (in most situations) just applying your changes. Be
> > aware that this generalization does not hold true in all cases - you
> > must specifically ensure that it is true.
> >
> > If they do overlap, or user decision is required by the nature of the
> > business transacted, then return to the screen with a proposed merger of
> > the new values from your changes and the other changes, providing
> > indicators of where there are conflicts/overlaps, and allow the user to
> > edit and retry.