RE: Hmmmmm

From: Grabowy, Chris <cgrabowy_at_fcg.com>
Date: Fri, 25 Jan 2002 08:04:33 -0800
Message-ID: <F001.003FB340.20020125080032@fatcity.com>

Interesting. Are you sure?

I have actually started reading Tom Kyte's book, Expert One-on-One, and he mentions this problem and the solution(specifically, page 32). Basically, you code..

SELECT * FROM table WHERE field = something FOR UPDATE NOWAIT;

UPDATE table set field = blah WHERE field = something;

this allows user A to lock and update a specific record, and prevents others from updating that record. Please note, I specified "...FOR UPDATE NOWAIT" because if user B tries to update the record at the same time, then a lock error is returned which can then be coded for. If you use "...FOR UPDATE" then user B simply sits there and waits to update, which is dangerous since the record was changed for a reason, and will be updated again. Bad.

I know of one project that I support that is using this standard method successfully.

HTH!! Chris
"May Oracle be with you...always"

• Stefan Jakobsson <stefan_at_areldata.se> wrote:
  > Having a small problem I need some help with...
  >
  > The problem is trying to lock a row on a parameter table for an
  > application.
  >
  > The thing we need to do is following:
  >
  > Read the value in one row. LOCK that row for other users, increase
  > the value
  > with one
  > update the row with the new value and release if for access to other
  > users.
  >
  > We are trying to use the following SQL statement.
  >
  > SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT;
  >
  > But the thing is that when we try, anyone can head on in and select
  > the same
  > value
  > and even make an update of that row...
  >
  > What am I missing here?
  >
  > Regards,
  > Stefan Jakobsson
  > Programmer
  > Arel-Data
  -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: cgrabowy_at_fcg.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jan 25 2002 - 10:04:33 CST