Subject Re: [firebird-support] Avoiding pessimistic locking
Author Kjell Rilbe
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.


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.
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64