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.

Advantages to this model:

1. transactions are all sub-second
2. One person leaving their workstation can't shut down the rest of the
business
3. Real-time automation and interactive usage are both supported
4. Reduced resource usage improves scalability

Disadvantages to this model:

1. It is more complex to design and code

On Fri, 2005-10-07 at 12:54 +1000, Alan McDonald wrote:
> > Searching through the archives, I see that pessimistic locking is not
> > necessary in Firebird in most cases. However, I still can't figure
> > out how to do the following:
> >
> > user A edits record A
> > user B edits record A
> > user B post changes
> > user A post changes
> >
> > How can I notify user A that record A has been changed? The only way
>
> my 2c
> telling user A that the record has changed in this case is too often, too
> late. User A has already invested time and energy into editing only to be
> told he can't save the changes... OR you go thru an elaborate sequence to
> ask him to adjudicate whether each of his field values should overwrite the
> previously saved (user B's) edits.
> Often, here, the user is too frightened to adjudicate or unwilling to blow
> away another user's work.
>
> The dummy update method is superior to these steps. Before you let User B
> (above) edit a record, User A has already made a dummy update on it and you
> get an exception straight away. You tell User B that he can't edit while
> another user is editing. You also handle the transaction timing of the edits
> for User A so they don't go on all day, locking all other users out.
>
> The possibility of analysing previous record version is not available.
>
> Alan
>
> Something like this:
> // Now try a dummy update to test if you can allocate the current job.
> JobNo := dmLocalStore.IB_QJobsUnAssigned.FieldByName('ID').AsInt64;
> if not dmLocalStore.IB_TransactionAllocate.Started then begin
> Application.OnException := UpdException;
> try
> with dmLocalStore.IB_CDummyUpdate do begin // update the PK value
> with the PK value
> ParamByName('ID').AsInt64 := JobNo;
> Execute;
> end;
> except
> on E1: EIBO_ISCError do begin
> // means someone is already allocating
> // This exception results in the Allocate Transaction being
> rolled back.
> UpdConflictException(E1);
> // return user to starting place.
> end;
> end;
> Application.OnException := AppException;
> // Now we have a successful dummy update and we can proceed to
> allocate the job.
> with dmLocalStore.IB_QJobAllocate do begin
> ParamByName('ID').AsInt64 := JobNo;
> // open the record and continue editing
> Open;
> end;
> end;
> // NOW TO EDIT AND ALLOCATE
>
> > I know how is to keep B's transaction open, but that's obviously not
> > the right way. I think the solution has something to do with record
> > numbers i.e. when user A posts his changes, the record number is
> > compared with that in the database, and if not the same, an exception
> > is raised. Is this correct? FWIW, I'm using FibPlus in Delphi.
> >
> > Thanks for any help.
> >
> > Ray Mond
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>