471,073 Members | 1,367 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

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

Locking a Table Within an INSERT

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
Jul 19 '05 #1
3 3830
sh*****@aol.com (Elliott) wrote in message news:<14**************************@posting.google. 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>.nextval and you are set.

Sybrand Bakker
Senior Oracle DBA
Jul 19 '05 #2
> Use a sequence
replace the horrible expression by
<sequence>.nextval 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.com if you'd care to hear more.
Jul 19 '05 #3


Elliott wrote:
Use a sequence
replace the horrible expression by
<sequence>.nextval 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.com 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

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Puvendran | last post: by
9 posts views Thread by john smile | last post: by
2 posts views Thread by Randall Sell | last post: by
3 posts views Thread by mahajan.sanjeev | last post: by
13 posts views Thread by Jeff Davis | last post: by
2 posts views Thread by qhjghz | last post: by
3 posts views Thread by Elliott | last post: by
3 posts views Thread by Brad Pears | last post: by

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.