Oracle SQL: How to read-and-increment a field

Question

I'm refactoring the data import procedure for an enterprise application and came across a snippet I'd like to find a better solution. When importing data we have to create a unique entity for each data set and there is a counter in a field to be used to assign this id sequentially. You read the field to get the next free id and increment it afterwards to prepare for the next time.

At the moment this is done in two steps in the original app, written in 'C':

   SELECT idnext FROM mytable;
   UPDATE mytable SET idnext = idnext + 1;

Obviously there is a race condition here, if multiple processes do the same thing.

Edit: Important corequisite: I can not touch the database/field definition, this rules out a sequence.

We are rewriting in perl, and I'd like to do the same thing, but better. An atomic solution would be nice. Unfortunately my SQL skills are limited, so I'm turning to collective wisdom :-)

Answer 1

In this particular case, a sequence is the right solution as mentioned. But if in some future situation you need to both update something and return a value in the same statement, you can use the RETURNING clause:

UPDATE atable SET foo = do_something_with(foo) RETURNING foo INTO ?

If the calling code is PL/SQL, replace the ? with a local PL/SQL variable; otherwise you can bind it as an output parameter in your program.

Edit: Since you mentioned Perl, something like this ought to work (untested):

my $sth = $dbh->prepare('UPDATE mytable SET idnext = idnext + 1 returning idnext into ?');
my $idnext;
$sth->bind_param_inout(1, \$idnext, 8);
$sth->execute; # now $idnext should contain the value

See DBI.

Answer 2

Why not use a sequence?

Create the sequence one time, using whatever START WITH value you want:

CREATE SEQUENCE mysequence
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

Then in your application code at runtime you can use this statement to get the next value:

SELECT mysequence.NEXTVAL
  INTO idnext
  FROM DUAL;

Update: Using a sequence would be the preferred method, but since you can't change the database then I agree that using RETURNING should work for your situation:

   UPDATE mytable 
      SET idnext = idnext + 1 
RETURNING idnext 
     INTO mylocalvariable;

Answer 3

Use SELECT FOR UPDATE statement. It guarantees mutually exclusive rights to the record :

"SELECT FOR UPDATE;