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





[Non-text portions of this message have been removed]