Re: concurrency Access on a table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/19
Message-ID: <349ae364.47289648@inet16>#1/1

use a sequence.

SQL> create sequence myseq;

SQL> insert into some_table values ( ...., myseq.nextVal, .... );

SQL> select myseq.currval from dual;

sequences generate numeric keys and are non-blocking. When you select nextval, you will be the only one to get that value. When you select currval, it'll give you the value of the last nextval YOU did (never someone elses nextval, its always your nextval).

On Thu, 18 Dec 1997 23:06:34 GMT, fbraun_at_rz.uni-sb.de (Frank Braun) wrote:

>Hi.
>
>I have to write a SQL-Script under PL/SQL 2.3.2.3.0
>
>In this script I have to get a Primary Key (PK) for a table A from a
> table ID_MANAGEMENT (IDM).
>
>That means, every row in A has a PK which is taken out
> from IDM.next_id. IDM.next_id will be updated with next_id+1.
>
>Since several skripts may run, which want to write something in A,
>it could be that two Skripts read same IDM.next_id, so that they
>both will write something in A with same PK
> (Producer-Consumer-Problem).
>Is there something like a semaphore in Oracle, so that always after
>reading IDM.next_id it will be increased before some other instance of
>a script could read the same value in IDM.next_id?
>
>Thank you very much,
>
>bye Frank.
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Dec 19 1997 - 00:00:00 CST