Subject RE: [firebird-support] Avoiding pessimistic locking
Author Alan McDonald
> 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.


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;
with dmLocalStore.IB_CDummyUpdate do begin // update the PK value
with the PK value
ParamByName('ID').AsInt64 := JobNo;
on E1: EIBO_ISCError do begin
// means someone is already allocating
// This exception results in the Allocate Transaction being
rolled back.
// return user to starting place.
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

> 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