What is the best way to lock an individual row in the following
scenerio:

--TODO - LOCK THIS ROW
-- Return the next id
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;

-- Update the next id on the table
UPDATE owner.my_id_table
SET next_id = next_id_out + 1
WHERE app_id = app_id_in;

I need to make sure that nothing changes the id table between me
selecting the id and updating the table with the next available id.

thanks
andrew

PS. yes I am new to oracle

Unless you will do some other procedural processing or checking
between your SELECT and your UPDATE (w/c you did not include in your
code sample), then you could just use one statement:

UPDATE owner.my_id_table
SET next_id = next_id + 1
WHERE app_id = app_id_in;

Otherwise, check out "FOR UPDATE" in the manuals: Concepts, or SQL
Guide, or Application Developer's Guide (Fundamentals). But I really
think the above would work for your purposes.

HTH.

webmaster (AT) vbusers (DOT) com (Andrew Baker) wrote in message news:<c19b84e5.0407271054.54ae108a (AT) posting (DOT) google.com>...

webmaster (AT) vbusers (DOT) com (Andrew Baker) wrote in message news:<c19b84e5.0407271054.54ae108a (AT) posting (DOT) google.com>...
declare
cursor my_date is
select SELECT next_id + 1
FROM owner.my_id_table
WHERE app_id = app_id_in
for update of next_id;
dummy number;
begin
open my_date;
fetch my_date into dummy;
update owner.my_table
set next_id = dummy
where current of my_date;
close my_date;
commit;
end;


This approach avoids two sessions select the same next_id.
However, this approach will hamper concurrency, and this is the reason
why Oracle implemented *sequences*. They don't need explicit locks.

Sybrand Bakker
Senior Oracle DBA

Thanks for the reply, but I think you may have miss-read the SQL (with
hindsight I wasn't being very clear!). The first statement does a
select into the store procs output parameter called "next_id_out":

SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;

The next sql updates the table to move the next available Id on by
one:

UPDATE owner.my_id_table
SET next_id = next_id_out + 1
WHERE app_id = app_id_in;

my problem is that this sp will be called v. intensively by lots of
processes. So I think that the same Id could be returned twice unless
I lock the row...

thanks in advance

andrew



rolympia (AT) hotmail (DOT) com (Romeo Olympia) wrote in message news:<42fc55dc.0407271828.1da3ac2 (AT) posting (DOT) google.com>...

"Andrew Baker" <webmaster (AT) vbusers (DOT) com> wrote


First, make sure that you really want to do this -- if you're simply
assigning surrogate key (ID) values, then you probably want to use a
SEQUENCE object -- they avoid the serialization that this approach causes,
but have the sometimes unwanted characteristic of allowing gaps between IDs
(only a problem if you've got auditing requirements that disallow gaps in a
series of IDs)

If you do need to have a table-based counter, here's the most reliable way
to increment it:

update owner.my_id_table
set next_id = next_id +1
where app_id = app_id_in
returning next_id into some_plsql_variable;

however, if the row is locked by another process (which is likely executing
the same statement and has delayed committing or rolling back) then this
process will hang until the other process's transaction completes. if you
want to return control to this process rather than wait on a lock, you need
to do one of the following before you update statement:

select next_id
into next_id_out
from owner.my_id_table
where app_id = app_id_in
for update nowait;

select next_id
into next_id_out
from owner.my_id_table
where app_id = app_id_in
for update wait 5;

the first raises an oracle error immediately if it cannot lock the row, the
second waits up to 5 seconds to obtain the lock. the second syntax takes
whatever number of seconds you want, but unfortunately the number of seconds
has to be specified in a literal (not with a bind variable)

++ mcs

I *highly* recommend using an Oracle sequence rather than a table to
store this value. I cannot fathom of any business requirement by
which you would need to use a table to generate incrementing ID's
instead of a sequence (with the exception of a business mandate that
ID's must never skip numbers).

Seriously, do not try to do this level of locking and releasing
manually except as a last resort.

webmaster (AT) vbusers (DOT) com (Andrew Baker) wrote in message news:<c19b84e5.0407271054.54ae108a (AT) posting (DOT) google.com>...

webmaster (AT) vbusers (DOT) com (Andrew Baker) wrote in message news:<c19b84e5.0407280224.3651abb (AT) posting (DOT) google.com>...

Since you are new, you may not realize, you are reimplementing an
ORACLE feature known as a SEQUENCE. A SEQUENCE will provide exactly
what you need without the need to implement stored procedures which
single thread processing by putting locks on resources. So instead of

mystoredproc( next_id_out );

use the SEQUENCE as a pseudo-column on dual:

select sequencename.nextval into next_id_out from dual ;

SEQUENCE advantages include:
fast returns of next value (no locking to slow things down)
session independence (my value will never be the same as your value)
application independent (each sequence is named so each can be used
independently)
robust (it works for many oracle customers, why write your own?)

SEQUENCE, look for it in an ORACLE SQL Manual near you!

HTH,
ed

Thanks to everyone for helping me out with this!

One of the replys noted that the only reason to use a table instead of
a sequence would be to have sequential ids and this is indeed a
requirement of our auditing systems.

So I have had to use a "FOR UPDATE" cursor and the initial testing I
have done shows that it absolutely flys (bear in mind the current
database is a Sybase box (which is imho marginally better than
Access!).

Thanks again

andrew


ed.prochak (AT) magicinterface (DOT) com (Ed prochak) wrote in message news:<4b5394b2.0407280948.7b4f06a6 (AT) posting (DOT) google.com>...

After thinking through swapping to using a sequence number I came up
with a couple of potential gotchas:

1. When we fail over to our DR (disaster recovery) boxes the numbers
must continue their sequence. Would a sequence number work under this
circumstance?

2. When the box reboots/crashes does it continue the sequence number
from where it was before the restart?

3. Can you manually tune the sequence numbers to increment by
specified amounts?

4. Is a sequence number system wide? ie. I can it be table specific
like IDENTITY columns in SQL server?

thanks again
andrew

ed.prochak (AT) magicinterface (DOT) com (Ed prochak) wrote in message news:<4b5394b2.0407280948.7b4f06a6 (AT) posting (DOT) google.com>...

"Andrew Baker" <webmaster (AT) vbusers (DOT) com> wrote

yes, the sequence definition should remained synchronized (see #2)

lookup the information on SEQUENCE in the oracle docs, it explains how each
sequence has a cache that is loaded into memory as needed, and discarded at
shutdown if not used

yes -- lookup CREATE SEQUENCE in the Oracle SQL manual

just like tables, it depends on privileges that you grant -- lookup
information about object
security in the manuals, including GRANT in the Oracle SQL manual

5) http://tahiti.oracle.com for manuals

6) your earlier post says 'it flies', referring to performance of
table-based SA-ID management. maybe with one user, but if you simulate a
load you will begin to see degradation due to serialization


++ mcs