473,724 Members | 2,222 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

we*******@vbuse rs.com (Andrew Baker) wrote in message news:<c1******* *************** ****@posting.go ogle.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 :)

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


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
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_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

ro******@hotmai l.com (Romeo Olympia) wrote in message news:<42******* *************** ***@posting.goo gle.com>...
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.

we*******@vbuse rs.com (Andrew Baker) wrote in message news:<c1******* *************** ****@posting.go ogle.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 :)

Jul 19 '05 #4

"Andrew Baker" <we*******@vbus ers.com> wrote in message
news:c1******** *************** ***@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
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*******@vbuse rs.com (Andrew Baker) wrote in message news:<c1******* *************** ****@posting.go ogle.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 :)

Jul 19 '05 #6
we*******@vbuse rs.com (Andrew Baker) wrote in message news:<c1******* *************** ***@posting.goo gle.com>...
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

[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.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
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********@magi cinterface.com (Ed prochak) wrote in message news:<4b******* *************** ****@posting.go ogle.com>...
we*******@vbuse rs.com (Andrew Baker) wrote in message news:<c1******* *************** ***@posting.goo gle.com>...
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

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

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********@magi cinterface.com (Ed prochak) wrote in message news:<4b******* *************** ****@posting.go ogle.com>...
we*******@vbuse rs.com (Andrew Baker) wrote in message news:<c1******* *************** ***@posting.goo gle.com>...
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

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

Jul 19 '05 #9

"Andrew Baker" <we*******@vbus ers.com> wrote in message
news:c1******** *************** ***@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
Jul 19 '05 #10

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

Similar topics

3
9629
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 AS myTableType)) ) AND processed_datetime IS NULL
5
3844
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' FOR Update; (when i execute this statement and i guess that this will lock the
4
4087
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 this function using select-for-update (or equivalent) in order to get a row-level lock (and thus less contention) while maintaining the function interface? The docs seem to suggest so, but it's not clear how to return the SETOF queued_item and also...
4
9608
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. To guarantee uniqueness of the application number SELECT FOR UPDATE statement is used. 2) Calculate new application number value.
19
8378
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 FOR UPDATE, it is fine and no error. I also tried Set objRs = objConn.Execute("SELECT * FROM EMP UPDATE OF EMPNO"), but it still couldn't help. any ideas? I tried to search in the web but couldn't find similar
2
3805
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. Then run code that assigns the new value to a column in the row. Then sql update the column in the row, releasing the lock. thanks, SqlDataReader rdr = null;
3
28646
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. When connected to the UniVerse database, if a second user runs the program, at the point where the selection executes, the program waits on the lock until the first user's updates are committed. However, when connected to the DB2 database, in the...
1
2158
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 work condition occured" which is i think is due to deadlock condition. I just wanted to know that What database lock ( share/update/exclusive) will be aquired for the above statement ? Is it that all txn will try to hold only U lock (since for...
11
464
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 = app_id_in;
0
8868
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8741
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9389
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9243
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9160
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9090
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8063
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4504
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2613
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.