> Thanks for the reply. Unfortunately I am using the BDE for the app,

I do not have the time to convert or go through a rigorous testing
phase to implement IBX etc.

>
>
>
> The main problem appears to me that both users in this scenario are

able to get an N returned by the stored proc, I need to be able to
avoid this in some way using the existing architecture within the app,
or else both people get the same record open for editing, and in the
environment that my app works in this is NOT a good idea. I need to
stop the 2nd user from being returned an N, but cannot work out how to
guarantee this from happening. Perhaps my model of locking is not very
good and someone else could offer an alternative approach (that still
uses the BDE type of connection).

>

The problem here is that your locking system was designed for a non
MGA database (probably Paradox etc). These databases have only one
copy of a given record, so if transaction 1 changes its value,
transaction 2 either sees that change or is not allowed to read it.

Firebird does not work that way. I am guessing that the BDE is using a
snapshot transaction. This transaction isolation level means that
transaction 1 and 2 both see the committed records in the database at
the time that the transaction started, as well as any uncommitted
changes made by itself.

So if transaction 1 starts, then transaction 2 starts, then
transaction 1 calls your stored procedure, transaction 2 will still
read the old value. In fact this will be the case with all
transactions until the moment you commit transaction 1. After that all
the snapshot transactions that were started before transaction 1
committed will still see the old value.

Pessimistic locking introduces bottlenecks and is often used
unnecessarily. Your first thing to investigate is whether it is
necessary in a MGA database. If so (there are some situations), you
can try a select with lock (see FB 1.5 release notes for details).


Adam