Set,



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).



T.I.A

Martin

________________________________

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: 20 March 2006 14:42
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Record Locking (WITH LOCK)



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





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





SPONSORED LINKS

Technical support <http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw>

Computer technical support <http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA>

Compaq computer technical support <http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g>

Compaq technical support <http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w>

Hewlett packard technical support <http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA>

Microsoft technical support <http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw>



________________________________

YAHOO! GROUPS LINKS



* Visit your group "firebird-support <http://groups.yahoo.com/group/firebird-support> " on the web.

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> .



________________________________



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