Incrementing Oracle Sequence by certain amount

Question

I am programming a Windows Application (in Qt 4.6) which - at some point - inserts any number of datasets between 1 and around 76000 into some oracle (10.2) table. The application has to retrieve the primary keys, or at least the primary key range, from a sequence. It will then store the IDs in a list which is used for Batch Execution of a prepared query.

(Note: Triggers shall not be used, and the sequence is used by other tasks as well)

In order to avoid calling the sequence X times, I would like to increment the sequence by X instead.

What I have found out so far, is that the following code would be possible in a procedure:

ALTER SEQUENCE my_sequence INCREMENT BY X;

SELECT my_sequence.CURVAL + 1, my_sequence.NEXTVAL
INTO   v_first_number, v_last_number
FROM   dual;

ALTER SEQUENCE my_sequence INCREMENT BY 1;

I have two major concerns though:

1. I have read that ALTER SEQUENCE produces an implicit commit. Does this mean the transaction started by the Windows Application will be commited? If so, can you somehow avoid it?

2. Is this concept multi-user proof? Or could the following thing happen:

   Sequence is at 10,000
   Session A sets increment to 2,000
   Session A selects 10,001 as first and 12,000 as last
   Session B sets increment to 5,000
   Session A sets increment to 1
   Session B selects 12,001 as first and 12,001 as last
   Session B sets increment to 1
   

   Even if the procedure would be rather quick, it is not that unlikely in my application that two different users cause the procedure to be called almost simultaneously

Answer 1

1) ALTER SEQUENCE is DDL so it implicitly commits before and after the statement. The database transaction started by the Windows application will be committed. If you are using a distributed transaction coordinator other than the Oracle database, hopefully the transaction coordinator will commit the entire distributed transaction but transaction coordinators will sometimes have problems with commits issued that it is not aware of. There is nothing that you can do to prevent DDL from committing.

2) The scenario you outline with multiple users is quite possible. So it doesn't sound like this approach would behave correctly in your environment.

You could potentially use the DBMS_LOCK package to ensure that only one session is calling your procedure at any point in time and then call the sequence N times from a single SQL statement. But if other processes are also using the sequence, there is no guarantee that you'll get a contiguous set of values.

CREATE PROCEDURE some_proc( p_num_rows IN NUMBER,
                            p_first_val OUT NUMBER,
                            p_last_val  OUT NUMBER )
AS
  l_lockhandle       VARCHAR2(128);
  l_lock_return_code INTEGER;
BEGIN
  dbms_lock.allocate_unique( 'SOME_PROC_LOCK',
                             l_lockhandle );
  l_lock_return_code := dbms_lock.request( lockhandle => l_lockhandle,
                                           lockmode => dbms_lock.x_mode,
                                           release_on_commit => true );
  if( l_lock_return_code IN (0, 4) ) -- Success or already owned
  then
    <<do something>>
  end if;

  dbms_lock.release( l_lockhandle );
END;