How to lock a row over a SELECT followed by an UPDATE

[Andrew Baker]

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_tab le
WHERE app_id = app_id_in;

-- Update the next id on the table
UPDATE owner.my_id_tab le
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 :)

[Romeo Olympia]

Re: How to lock a row over a SELECT followed by an UPDATE

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_tab le
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@vbuse rs.com (Andrew Baker) wrote in message news:<c19b84e5. 0407271054.54ae 108a@posting.go ogle.com>...[color=blue]
> 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_tab le
> WHERE app_id = app_id_in;
>
> -- Update the next id on the table
> UPDATE owner.my_id_tab le
> 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 :)[/color]

[sybrandb@yahoo.com]

Re: How to lock a row over a SELECT followed by an UPDATE

webmaster@vbuse rs.com (Andrew Baker) wrote in message news:<c19b84e5. 0407271054.54ae 108a@posting.go ogle.com>...[color=blue]
> 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_tab le
> WHERE app_id = app_id_in;
>
> -- Update the next id on the table
> UPDATE owner.my_id_tab le
> 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 :)[/color]

declare
cursor my_date is
select SELECT next_id + 1
FROM owner.my_id_tab le
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

[Andrew Baker]

Re: How to lock a row over a SELECT followed by an UPDATE

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_ou t":

SELECT next_id
INTO next_id_out
FROM owner.my_id_tab le
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_tab le
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@hotmai l.com (Romeo Olympia) wrote in message news:<42fc55dc. 0407271828.1da3 ac2@posting.goo gle.com>...[color=blue]
> 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_tab le
> 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@vbuse rs.com (Andrew Baker) wrote in message news:<c19b84e5. 0407271054.54ae 108a@posting.go ogle.com>...[color=green]
> > 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_tab le
> > WHERE app_id = app_id_in;
> >
> > -- Update the next id on the table
> > UPDATE owner.my_id_tab le
> > 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 :)[/color][/color]

[Mark C. Stock]

Re: How to lock a row over a SELECT followed by an UPDATE


"Andrew Baker" <webmaster@vbus ers.com> wrote in message
news:c19b84e5.0 407271054.54ae1 08a@posting.goo gle.com...
| 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_tab le
| WHERE app_id = app_id_in;
|
| -- Update the next id on the table
| UPDATE owner.my_id_tab le
| 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 :)


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_tab le
set next_id = next_id +1
where app_id = app_id_in
returning next_id into some_plsql_vari able;

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_tab le
where app_id = app_id_in
for update nowait;

select next_id
into next_id_out
from owner.my_id_tab le
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

[Kevin]

Re: How to lock a row over a SELECT followed by an UPDATE

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@vbuse rs.com (Andrew Baker) wrote in message news:<c19b84e5. 0407271054.54ae 108a@posting.go ogle.com>...[color=blue]
> 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_tab le
> WHERE app_id = app_id_in;
>
> -- Update the next id on the table
> UPDATE owner.my_id_tab le
> 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 :)[/color]

[Ed prochak]

Re: How to lock a row over a SELECT followed by an UPDATE

webmaster@vbuse rs.com (Andrew Baker) wrote in message news:<c19b84e5. 0407280224.3651 abb@posting.goo gle.com>...[color=blue]
> 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_ou t":
>
> SELECT next_id
> INTO next_id_out
> FROM owner.my_id_tab le
> 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_tab le
> 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
>[/color]
[snip][color=blue][color=green][color=darkred]
> > >
> > > 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 :)[/color][/color][/color]


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.ne xtval 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

[Andrew Baker]

Re: How to lock a row over a SELECT followed by an UPDATE

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@magi cinterface.com (Ed prochak) wrote in message news:<4b5394b2. 0407280948.7b4f 06a6@posting.go ogle.com>...[color=blue]
> webmaster@vbuse rs.com (Andrew Baker) wrote in message news:<c19b84e5. 0407280224.3651 abb@posting.goo gle.com>...[color=green]
> > 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_ou t":
> >
> > SELECT next_id
> > INTO next_id_out
> > FROM owner.my_id_tab le
> > 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_tab le
> > 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
> >[/color]
> [snip][color=green][color=darkred]
> > > >
> > > > 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 :)[/color][/color]
>
>
> 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.ne xtval 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[/color]

[Andrew Baker]

Re: How to lock a row over a SELECT followed by an UPDATE

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@magi cinterface.com (Ed prochak) wrote in message news:<4b5394b2. 0407280948.7b4f 06a6@posting.go ogle.com>...[color=blue]
> webmaster@vbuse rs.com (Andrew Baker) wrote in message news:<c19b84e5. 0407280224.3651 abb@posting.goo gle.com>...[color=green]
> > 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_ou t":
> >
> > SELECT next_id
> > INTO next_id_out
> > FROM owner.my_id_tab le
> > 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_tab le
> > 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
> >[/color]
> [snip][color=green][color=darkred]
> > > >
> > > > 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 :)[/color][/color]
>
>
> 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.ne xtval 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[/color]

[Mark C. Stock]

Re: How to lock a row over a SELECT followed by an UPDATE


"Andrew Baker" <webmaster@vbus ers.com> wrote in message
news:c19b84e5.0 407290256.1e869 438@posting.goo gle.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?

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

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

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

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

yes -- lookup CREATE SEQUENCE in the Oracle SQL manual

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

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

[Ed prochak]

Re: How to lock a row over a SELECT followed by an UPDATE

webmaster@vbuse rs.com (Andrew Baker) wrote in message news:<c19b84e5. 0407290256.1e86 9438@posting.go ogle.com>...[color=blue]
> 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?[/color]

I think the real DBAs can answer this better. If the DR box is a
mirror system, then it has the same value by definition. If you are
talking about restoring the DB from backup, then note that sequences
are backed up as well. So I'd say YES.
[color=blue]
>
> 2. When the box reboots/crashes does it continue the sequence number
> from where it was before the restart?[/color]

the number from the last transaction. In ORACLE, some values are, by
default, cached so they would be "lost" on restart. But the number of
values cached is controllable, so you can reduce it to where none are
lost.[color=blue]
>
> 3. Can you manually tune the sequence numbers to increment by
> specified amounts?[/color]

Definitely.
[color=blue]
>
> 4. Is a sequence number system wide? ie. I can it be table specific
> like IDENTITY columns in SQL server?[/color]

The SEQUENCE is SCHEMA wide and identified by its own name. It is tied
to a specific table via a trigger.
[color=blue]
>
> thanks again
> andrew
>[/color]

In your other post you mentioned the need for a auditable sequence of
numbers (ie no gaps, or at least no large gaps). In that case, a table
might be best. There have been other discussions of this topic. (do a
GOOGLE search in comp.databases. oracle.misc)

Keep in mind the differences
SEQUENCE
ORACLE controls number allocation allowing multiple sessions access
without locking issues.
cached values may be "lost" (meaing allocated but never used, creating
"gaps")
tied to a table by a trigger
values accessed by pseudocolumns nextval, currval.

SINGLE ROW TABLE
single threaded access (via SELECT FOR UPDATE locks)
no "lost" values so no gaps
tied to a table by a trigger
values accessed by custom functions.

MULTIROW TABLE (has 2 attributes: value and used_flag, one row for
every possible value, marked whether it's used or not)
possible multithreaded access (controlled by custom functions)
no lost values, but depending on allocation algorithm there can be
temorary gaps
tied to a table by a trigger
values accessed by custom functions

If you really need to not lose values, the table may be better. But
the SEQUENCE can be set to cache nothing so it would not lose any
values either. Test them both in production level enviroments
(especially number of simultaneous requests).

HTH,
ed

[Andrew Baker]

Re: How to lock a row over a SELECT followed by an UPDATE

Thanks to everyone who has helped me with this...

andrew

ed.prochak@magi cinterface.com (Ed prochak) wrote in message news:<4b5394b2. 0407290943.2a75 5a0b@posting.go ogle.com>...[color=blue]
> webmaster@vbuse rs.com (Andrew Baker) wrote in message news:<c19b84e5. 0407290256.1e86 9438@posting.go ogle.com>...[color=green]
> > 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?[/color]
>
> I think the real DBAs can answer this better. If the DR box is a
> mirror system, then it has the same value by definition. If you are
> talking about restoring the DB from backup, then note that sequences
> are backed up as well. So I'd say YES.
>[color=green]
> >
> > 2. When the box reboots/crashes does it continue the sequence number
> > from where it was before the restart?[/color]
>
> the number from the last transaction. In ORACLE, some values are, by
> default, cached so they would be "lost" on restart. But the number of
> values cached is controllable, so you can reduce it to where none are
> lost.[color=green]
> >
> > 3. Can you manually tune the sequence numbers to increment by
> > specified amounts?[/color]
>
> Definitely.
>[color=green]
> >
> > 4. Is a sequence number system wide? ie. I can it be table specific
> > like IDENTITY columns in SQL server?[/color]
>
> The SEQUENCE is SCHEMA wide and identified by its own name. It is tied
> to a specific table via a trigger.
>[color=green]
> >
> > thanks again
> > andrew
> >[/color]
>
> In your other post you mentioned the need for a auditable sequence of
> numbers (ie no gaps, or at least no large gaps). In that case, a table
> might be best. There have been other discussions of this topic. (do a
> GOOGLE search in comp.databases. oracle.misc)
>
> Keep in mind the differences
> SEQUENCE
> ORACLE controls number allocation allowing multiple sessions access
> without locking issues.
> cached values may be "lost" (meaing allocated but never used, creating
> "gaps")
> tied to a table by a trigger
> values accessed by pseudocolumns nextval, currval.
>
> SINGLE ROW TABLE
> single threaded access (via SELECT FOR UPDATE locks)
> no "lost" values so no gaps
> tied to a table by a trigger
> values accessed by custom functions.
>
> MULTIROW TABLE (has 2 attributes: value and used_flag, one row for
> every possible value, marked whether it's used or not)
> possible multithreaded access (controlled by custom functions)
> no lost values, but depending on allocation algorithm there can be
> temorary gaps
> tied to a table by a trigger
> values accessed by custom functions
>
> If you really need to not lose values, the table may be better. But
> the SEQUENCE can be set to cache nothing so it would not lose any
> values either. Test them both in production level enviroments
> (especially number of simultaneous requests).
>
> HTH,
> ed[/color]

[andrew]

Similar problem - a little bit more complex

Scenario description -
We have two Oracle DB's A & B. Both DB's have a table test1 (customerid varchar2(50, val number). The idea is that for every customer id, the counter (stored in val) increases.
Whenever we send a file to the customer, we tag/identify it with a counter value. This is for verification on the customer side that they have received all files by checking for a break in the counter.
We have many customers in the table, each with a different counter value (depending on the number of files sent to each customer).

Our current solution -
Today, we use only one DB at a time to get these counter values from the table and periodically export the table contents.
When this active DB (suppose A) fails/crashes, we import the latest export file to the inactive database ( B ) then start using this table for getting the coutners for the customers.

Drawback of this process -
1. We loose changes of the counter values for the time between the last export and the time the DB crashes/fails. This results in duplicates of counter values to the customers.
2. We loose time in importing the data intot he standby database.


Can anyone provide a solution satisfying the following requirements -
1. The counter values are synched b/w the two databases at any given time.
2. The application should be able to increment the counter values from both the databases at any given time WITHOUT getting any duplciate counter values for a given customerid.
3. Suppose if A goes down/fails, the application should not have any problem in retreiving/incrementing the counter values in the active database B. When the failed database A comes up, the updates start flowing from B to A to bring upto "speed" / latest values and the also proogate any of the changes being made in B.
4. The ides is basically to be able to use either one of the databases or both at any given time WITHOUT any outages.



I have thought of using triggers, but the issue comes up with point 2. Also need to figure out if any of the transactions get caught in the DBA_2PC_PENDING table. If so, then how do we clear those rows out of it so that when the connection is established, these transactions get cleared ?


Also out of the box question -

Is there any other way to achive the above withoutusing database at all ? Meaning to be able to keep some counters in synch across servers and also being able to update it at all sites ?