Re: Locking a Table Within an INSERT

shevine_at_aol.com (Elliott) wrote in message news:<149413ab.0407061210.38cc8b7d_at_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 Received on Wed Jul 07 2004 - 09:34:21 CEST