Hi Martin!

The optimistic locking of Firebird is one of its advantages, not a
drawback. Occasionally we want pesimistic locking and we normally do
this by doing

UPDATE MyTable SET MyPrimaryKey = MyPrimaryKey
WHERE MyPrimaryKey = :Variable

and then avoid committing the transaction until all changes have been
completed and you're free to let go of the lock. I.e. no
SP_LOCK_RECORD procedure.

The reason for your problem isn't simply that two records
simultaneously tries to update the same record - Firebird would never
allow that, but that they both select the record simultaneously and
then the first transaction both UPDATE and COMMIT (maybe you use a
transaction isolation that waits for the first updating transaction to
be committed so that the second doesn't get an exception?).

My advice to you is to read a bit about transactions and transaction
isolation, and don't reinvent anything unless you have to. You could
take a look at http://www.ibobjects.com/TechInfo.html#ti_Transactions,
though I admit that I don't know where to direct people looking for
this kind of information.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Martin Dew" wrote:
> Hi,
>
> Currently in my app the accessing of records is controlled by having
> a field on a specific table called BEING_USED, this field has a Y or
> N set against it if a record is effectively being locked in use.
>
> The control of this is performed by a stored procedure;
>
> CREATE PROCEDURE SP_LOCK_RECORD (
> URN INTEGER
> ) RETURNS (
> BEING_USED CHAR(1)
> ) AS
> BEGIN
> Select Being_used from Log Where URN =:URN Into :Being_Used;
> if (:Being_Used = "N") then begin
> Update LOG Set Being_used = "Y" Where URN = :URN;
> end
> END
>
> The app calls this, through a routine that detects whether the
> BEING_USED parameter returned an N, if so it assumes that record was
> not in use, and this application session has successfully now set it
> to Y and thus locked it for the user.
>
> I have an issue at site where it would appear that more than one
> user can get into the record at the same time, I have tested until I
> am blue in the face and have come to the conclusion that the reason
> they can do this is that 2 users at the same split second must be
> calling this stored proc, and both get returned an N (possibly due
> to heavy use on the server during busy times etc).
>
> Can I stop this from happening by using a WITH LOCK statement ? is
> that what it is meant for, or would it not make a difference ?
>
> Thanks for any help you can offer.
>
> Regards
> Martin