Locking a Table Within an INSERT

[Elliott]

I'm trying to perform this insert in a non-procedural environment:

INSERT INTO table
(column1,
column2)
SELECT
:col1value,
MAX(column2) + :count
FROM table
WHERE column1 = :col1value
;

My problem is that two or more processes might be executing this
statement concurrently. If so, they could get the same value for
MAX(column2). But I want the second process to get the updated value
for that aggregate that is set by the first process, and insert an
incremented value based on THAT.

Is there a way I can write this statement so one instance locks out
the second one? In my environment, one statement is all I'm allowed.

Thanks,
Elliott

[sybrandb]

sh*****@aol.com (Elliott) wrote in message news:<14******* *************** ****@posting.go ogle.com>...

I'm trying to perform this insert in a non-procedural environment:

INSERT INTO table
(column1,
column2)
SELECT
:col1value,
MAX(column2) + :count
FROM table
WHERE column1 = :col1value
;

My problem is that two or more processes might be executing this
statement concurrently. If so, they could get the same value for
MAX(column2). But I want the second process to get the updated value
for that aggregate that is set by the first process, and insert an
incremented value based on THAT.

Is there a way I can write this statement so one instance locks out
the second one? In my environment, one statement is all I'm allowed.

Thanks,
Elliott

Use a sequence
replace the horrible expression by
<sequence>.next val and you are set.

Sybrand Bakker
Senior Oracle DBA

[Elliott]

> Use a sequence

replace the horrible expression by
<sequence>.next val and you are set.

Sybrand Bakker
Senior Oracle DBA

Would that I could. Unfortunately, the business requirements are that
I know the generated values in advance of inserting them.

I've thought of establishing an intermediate table into which I could
insert the values from a sequence, together with other record
identifying information, to be used in a lookup later. But I'm dealing
with, in some cases, a couple of million rows per run, with a dozen
runs a day.

You can write me at es*****@ford.co m if you'd care to hear more.

[D Rolfe]

Elliott wrote:

Use a sequence
replace the horrible expression by
<sequence>.ne xtval and you are set.

Sybrand Bakker
Senior Oracle DBA

Would that I could. Unfortunately, the business requirements are that
I know the generated values in advance of inserting them.

I've thought of establishing an intermediate table into which I could
insert the values from a sequence, together with other record
identifying information, to be used in a lookup later. But I'm dealing
with, in some cases, a couple of million rows per run, with a dozen
runs a day.

You can write me at es*****@ford.co m if you'd care to hear more.

<Various Random Ideas>
Have you thought about:

* Accessing a sequence once per run and adding 8 zeros to the end of the
number you get. Then add the number of the record to get a unique
identifer that is derived from a sequence. You could also use a sequence
that increments by 10 million each time. Oracle won't be phased by this
but check the size of the numeric data types used by non-oracle
languages to access the data.

* Using a numeric identifier that has decimal places. The left hand half
identifies the number of the row in the batch and the right hand half is
pulled from a sequence once at the start of the process.

* splitting the PK into two - a batch number (pulled from sequence like
above) and a number within a batch

</Various Random Ideas>

David Rolfe
Orinda Software
Dublin, Ireland