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

Others will probably give more in-depth info about this, but I have two
suggestions (untested):

1. When user A requests his changes to be posted, your application first
does a select on that record and compares the returned data with those
that A had before starting to edit the record. Requires that you save
backups of A's pre-edit data of course. Also requires a primary key on
the table in questions.

2. Store a record version number in a separate column. In your
application, always increment that number before posting an update. In
the database, create a before update trigger that raises an exception if
the application tries to store a record version number that's not
strictly larger than the current one. I guess you'd have to be careful
to make sure you always use the right transaction isolation for this to
work, i.e. A's transaction has to "see" changes commited in B's transaction.

Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64