> 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.

agree with all this - another addition is to timer edits and cause rollback
of edit transaction automatically if there is no user intervention past a
certain time span. If they go to lunch in the middle of edits, they deserve
to have to start again.
Alan

>
> 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
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>
>
>
>
>
>
>