473,322 Members | 1,347 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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 :)
Jul 19 '05 #1
12 46597
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.

we*******@vbusers.com (Andrew Baker) wrote in message news:<c1**************************@posting.google. 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_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 :)

Jul 19 '05 #2
we*******@vbusers.com (Andrew Baker) wrote in message news:<c1**************************@posting.google. 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_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 :)


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
Jul 19 '05 #3
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

ro******@hotmail.com (Romeo Olympia) wrote in message news:<42*************************@posting.google.c om>...
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.

we*******@vbusers.com (Andrew Baker) wrote in message news:<c1**************************@posting.google. 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_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 :)

Jul 19 '05 #4

"Andrew Baker" <we*******@vbusers.com> wrote in message
news:c1**************************@posting.google.c om...
| 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 :)
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
Jul 19 '05 #5
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.

we*******@vbusers.com (Andrew Baker) wrote in message news:<c1**************************@posting.google. 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_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 :)

Jul 19 '05 #6
we*******@vbusers.com (Andrew Baker) wrote in message news:<c1*************************@posting.google.c om>...
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

[snip]

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

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
Jul 19 '05 #7
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********@magicinterface.com (Ed prochak) wrote in message news:<4b**************************@posting.google. com>...
we*******@vbusers.com (Andrew Baker) wrote in message news:<c1*************************@posting.google.c om>...
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

[snip]
>
> 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 :)

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

Jul 19 '05 #8
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********@magicinterface.com (Ed prochak) wrote in message news:<4b**************************@posting.google. com>...
we*******@vbusers.com (Andrew Baker) wrote in message news:<c1*************************@posting.google.c om>...
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

[snip]
>
> 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 :)

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

Jul 19 '05 #9

"Andrew Baker" <we*******@vbusers.com> wrote in message
news:c1**************************@posting.google.c om...
| 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
Jul 19 '05 #10
we*******@vbusers.com (Andrew Baker) wrote in message news:<c1**************************@posting.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?
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.

2. When the box reboots/crashes does it continue the sequence number
from where it was before the restart?
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.
3. Can you manually tune the sequence numbers to increment by
specified amounts?
Definitely.

4. Is a sequence number system wide? ie. I can it be table specific
like IDENTITY columns in SQL server?
The SEQUENCE is SCHEMA wide and identified by its own name. It is tied
to a specific table via a trigger.

thanks again
andrew


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
Jul 19 '05 #11
Thanks to everyone who has helped me with this...

andrew

ed********@magicinterface.com (Ed prochak) wrote in message news:<4b**************************@posting.google. com>...
we*******@vbusers.com (Andrew Baker) wrote in message news:<c1**************************@posting.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?


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.

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


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.

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


Definitely.

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


The SEQUENCE is SCHEMA wide and identified by its own name. It is tied
to a specific table via a trigger.

thanks again
andrew


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

Jul 19 '05 #12
andrew
2
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 ?
Aug 11 '05 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Arthur | last post by:
Hey Everyone, Is this an Oracle bug? Here is my cursor statement: CURSOR tax_portal_cursor IS SELECT * FROM web_payment@remotedb WHERE caps_code IN ( SELECT * FROM TABLE(CAST(l_caps_codes...
5
by: jayson_13 | last post by:
Hi, I need to implement a counter and i face problem of locking so hope that u guys can help me. I try to do test like this : 1st connection SELECT * FROM nextkey WHERE tblname = 'PLCN'...
4
by: Ed L. | last post by:
I think I'm seeing table-level lock contention in the following function when I have many different concurrent callers, each with mutually distinct values for $1. Is there a way to reimplement...
4
by: pike | last post by:
DB2 UDB 8.1 FP7 We are getting intermittent deadlocks (911 RC 2) even when U row-lock has been secured. The transaction is as follows: 1) Select current application number value from table....
19
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without...
2
by: Steve Richter | last post by:
what is the standard way of using sql in C# to increment a counter column in a shared control table? ( example: the next order number ) I would like to sql select a row in a table and lock it....
3
by: jbutler8192 | last post by:
Hello. We have a multi-user Java application that can connect to either a UniVerse database or a DB2 database on an AS400 through JDBC. The program executes a SELECT ... FOR UPDATE statement....
1
by: djpeg | last post by:
Hi, I have query something like this: select * from emp where sal > ( select avg(sal) from emp ) for update When ran multithreaded environment, i used to get this error "Abnormal end unit of...
11
by: Andrew Baker | last post by:
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 =...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.