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

Solution 1

Altering the sequence in this scenario is really bad idea. Particularly in multiuser environment. You'll get your transaction committed and probably several "race condition" data bugs or integrity errors. It would be appropriate if you had legacy data alredy imported and want to insert new data with ids from sequence. Then you may alter the sequence to move currval to max existing ...

It seems to me that here you want to generate Ids from the sequence. That need not to be done by

select seq.nextval into l_variable from dual;
insert into table (id, ...) values (l_variable, ....);

You can use the sequence directly in the insert:

insert into table values (id, ...) values (seq.nextval, ....);

and optionally get the assigned value back by

insert into table values (id, ...) values (seq.nextval, ....)
returning id into l_variable;

It certainly is possible even for bulk operations with execBatch. Either just creating the ids or even returning them. I am not sure about the right syntax in java but it will be something about the lines

insert into table values (id, ...) values (seq.nextval, ....)
returning id bulk collect into l_cursor;

and you'll be given a ResultSet to browse the assigned numbers.

Solution 2

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; 

Solution 3

1. You can't prevent the implicit commit.

2. Your solution is not multi user proof. It is perfectly possible that another session will have 'restored' the increment to 1, just as you described.

I would suggest you keep fetching values one by one from the sequence, store these IDs one by one on your list and have the batch execution operate on that list.

What is the reason that you want to fetch a contiguous block of values from the sequence? I would not be too worried about performance, but maybe there are other requirements that I don't know of.

Solution 4

In Oracle, you can use following query to get next N values from a sequence that increments by one:

select level, PDQ_ACT_COMB_SEQ.nextval as seq from dual connect by level <= 5;

View more solutions

Comments

[Tim Meyer]

fetching 70.000 IDs from the sequence one by one through my program takes 80 seconds. This is by far not acceptable.

[Tim Meyer]

I don't need to retrieve the IDs after they have been inserted into the table, but using seq.nextval directly in the values section could work even with execBatch. I can't test it before Monday but I will report if it worked.

[Tim Meyer]

I am pretty sure this solution would have helped out, but simply using sequence.nextval in the query did the job so I didn't try this out. Thanks anyway, maybe it helps someone else that stumbles upon this topic!

[Tim Meyer]

Using seq.nextval in the query did the job! At first my execBatch() execution time tripled up (from 10 to around 30 seconds) as the sequence had no cache, but playing around with the cache value, I am now down to only around 5% slower execution time (compared to generating contiguous values in my windows application) which is alright. Thanks!

[Martin Schapendonk]

Ouch. Fetching 70000 id's from a sequence in PL/SQL takes ~4 secs on the database I have at hand right now. That's a lot of overhead.