Just a little presentation for which I wish to do:

Think about the situation where differing people from the firm get, annually, an exciting expenses compensated visit to somewhere. There might be 1000 persons that may entitled to the trip only 16 places can be found.

All of this 16 spots comes with an connected index which should be from 1 to 16. Those around the reservation have index beginning from 17.

The very first 16 persons that apply obtain a definite just right the trip. The relaxation finish on the reservation list. If among the first 16 persons cancels, the very first person having a reservation will get his place and all sorts of the indexes are renumbered to pay for that person who canceled.

All this is handled inside a Java web application by having an Oracle DB.

Now, my problem:

I must manage the index inside a right way (all consecutive, no duplicate indexes), with possible 100s of folks that concurrently make an application for the trip.

When placing an archive within the table for that trip, the clear way of obtaining the index is as simple as

SELECT MAX(INDEX_NR) + 1 AS NEXT_INDEX_NR FROM TABLE

and taking advantage of this because the new index (this is accomplished Java side after which a brand new query to place the record). It's apparent why we've multiple spots or bookings with similar index. So, we obtain, let us say, 19 people around the trip because 4 of these have index 10, for instance.

How do i manage this? I've been considering 3 ways to date:

  1. Make use of an isolation degree of Serializable for that DB transactions (do not like that one)
  2. Place an archive without any INDEX_NR after which possess a trigger manage the things… in some manner (never labored with triggers before)
  3. Each record also offers a UPDATED column. Could I personally use this in some manner? (observe that I can’t lose the INDEX_NR since other areas from the application take advantage from it).

It is possible to the easy way do that?

Why allow it to be complicated ?

Just place all bookings because they are joined and place a timestamp of once they resevered a place.

Then in your soul query only use the timestamp to sort them.

There's offcourse the possibility that you will find people who reserved a place at the identical nanosecond then only use a random approach to assign order.

Why must you clearly keep index? Rather you can store everyone's order (which never changes) together with an energetic flag. Inside your example if person #16 pulls out you just mark them as inactive.

To compute whether an individual qualifies for that trip you just count the amount of active individuals with order under that individual:

select count(*)
from CompetitionEntry
where PersonOrder < 16
  and Active = 1

This method removes the requirement for bulk updates towards the database (you simply ever update one row) and therefore mostly mitigates your condition of transactional integrity.

One other way is always to clearly lock an archive on another table around the choose.

-- Initial Setup
CREATE TABLE NUMBER_SOURCE (ID NUMBER(4));

INSERT INTO NUMBER_SOURCE(ID) VALUES 0;

-- Your regular code
SELECT ID AS NEXT_INDEX_NR FROM NUMBER_SOURCE FOR UPDATE; -- lock!

UPDATE NUMBER_SOURCE SET ID = ID + 1;

INSERT INTO TABLE ....

COMMIT; -- releases lock!

Not one other transaction will have the ability to carry out the query up for grabs NUMBER_SOURCE before the commit (or rollback).

  1. When adding individuals to the table, provide them with an ID in a way the ID is climbing within the order by which these were added. This is often a timestamp.

  2. Choose all of the records in the table which qualify, order by ID, increase their INDEX_NR

  3. Choose * from table where INDEX_NR <= 16 order by INDEX_NR

Step #2 appears complicated but it is really fairly simple:

    update (
        select *
        from TABLE
        where ...
        order by ID
    )
    set INDEX_NR = INDEXSEQ.NEXTVAL

Be sure to totally reset the succession to at least one.