How to manage consecutive column values in table rows

Question

A little presentation for what I want to do:

Consider the case where different people from a firm get, once a year, an all expenses paid trip to somewhere. There may be 1000 persons that could qualify for the trip but only 16 places are available.

Each of this 16 spots has an associated index which must be from 1 to 16. The ones on the reservation have index starting from 17.

The first 16 persons that apply get a definite spot on the trip. The rest end up on the reservation list. If one of the first 16 persons cancels, the first person with a reservation gets his place and all the indexes are renumbered to compensate for the person that canceled.

All of this is managed in a Java web app with an Oracle DB.

Now, my problem:

I have to manage the index in a correct way (all sequential, no duplicate indexes), with possible hundreds of people that simultaneously apply for the trip.

When inserting a record in the table for the trip, the way of getting the index is by

SELECT MAX(INDEX_NR) + 1 AS NEXT_INDEX_NR FROM TABLE

and using this as the new index (this is done Java side and then a new query to insert the record). It is obvious why we have multiple spots or reservations with the same index. So, we get, let’s say, 19 people on the trip because 4 of them have index 10, for example.

How can I manage this? I have been thinking of 3 ways so far:

1. Use an isolation level of Serializable for the DB transactions (don’t like this one);
2. Insert a record with no INDEX_NR and then have a trigger manage the things… in some way (never worked with triggers before);
3. Each record also has a UPDATED column. Could I use this in some way? (note that I can’t lose the INDEX_NR since other parts of the app make use of it).

Is there a best way to do this?

Answer 1

Why do you need to explicitly store the index? Instead you could store each person's order (which never changes) along with an active flag. In your example if person #16 pulls out you simply mark them as inactive.

To compute whether a person qualifies for the trip you simply count the number of active people with order less than that person:

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

This approach removes the need for bulk updates to the database (you only ever update one row) and hence mostly mitigates your problem of transactional integrity.

Answer 2

Why make it complicated ?

Just insert all reservations as they are entered and insert a timestamp of when they resevered a spot.

Then in you query just use the timestamp to sort them.

There is offcourse the chance that there are people that reserved a spot at the very same millisecond then just use a random method to assign order.

Answer 3

1. When adding people to the table, give them an ID in such a way that the ID is ascending in the order in which they were added. This can be a timestamp.

2. Select all the records from the table which qualify, order by ID, and update their INDEX_NR

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

Step #2 seems complicated but it's actually quite simple:

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

Don't forget to reset the sequence to 1.

Answer 4

Another way would be to explicitly lock a record on another table on the select.

-- 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!

No other transaction will be able to perform the query on the table NUMBER_SOURCE until the commit (or rollback).

Answer 5

Calculate your index in runtime:

CREATE OR REPLACE VIEW v_person
AS
SELECT  id, name, ROW_NUMBER() OVER (ORDER BY id) AS index_rn
FROM    t_person

CREATE OR REPLACE TRIGGER trg_person_ii
INSTEAD OF INSERT ON v_person
BEGIN
        INSERT
        INTO    t_person (id, name)
        VALUES  (:new.id, :new.name);
END;

CREATE OR REPLACE TRIGGER trg_person_iu
INSTEAD OF UPDATE ON v_person
BEGIN
        UPDATE  t_person
        SET     id = :new.id,
                name = :new.name
        WHERE   id = :old.id;
END;

CREATE OR REPLACE TRIGGER trg_person_id
INSTEAD OF DELETE ON v_person
BEGIN
        DELETE
        FROM    t_person
        WHERE   id = :old.id;
END;

INSERT
INTO    v_person
VALUES  (1, 'test', 1)

SELECT  *
FROM    v_person

--
id   name   index_rn

 1   test          1

INSERT
INTO    v_person
VALUES  (2, 'test 2', 1)

SELECT  *
FROM    v_person

--
id   name   index_rn

 1   test          1
 2   test 2        2


DELETE
FROM    v_person
WHERE   id = 1

SELECT  *
FROM    v_person

--
id   name   index_rn

 2   test 2        1

Answer 6

"I have to manage the index in a correct way (all sequential, no duplicate indexes), with possible hundreds of people that simultaneously apply for the trip.

When inserting a record in the table for the trip, the way of getting the index is by

SELECT MAX(INDEX_NR) + 1 AS NEXT_INDEX_NR FROM TABLE

and using this as the new index (this is done Java side and then a new query to insert the record). It is obvious why we have multiple spots or reservations with the same index."

Yeah. Oracle's MVCC ("snapshot isolation") used incorrectly by someone who shouldn't have been in IT to begin with.

Really, Peter is right. Your index number is, or rather should be, a sort of "ranking number" on the ordered timestamps that he mentions (this holds a requirement that the DBMS can guarantee that any timestamp value appears only once in the entire database).

You say you are concerned with "regression bugs". I say "Why do you need to be concerned with "regression bugs" in an application that is DEMONSTRABLY beyond curing ?". Because your bosses paid a lot of money for the crap they've been given and you don't want to be the pianist that gets shot for bringing the message ?

Answer 7

The solution depends on what you have under your control. I assume that you can change both database and Java code, but refrain from modifying the database scheme since you had to adapt too much Java code otherwise.

A cheap solution might be to add a uniqueness constraint on the pair (trip_id, index_nr) or just on index_nr if there is just one trip. Additionally add a check contraint check(index_nr > 0) - unless index_nr is already unsigned. Everything else is then done in Java: When inserting a new applicant as described by you, you have to add code catching the exception when someone else got inserted concurrently. If some record is updated or deleted, you either have to live with holes between sequence numbers (by selecting the 16 candidates with the lowest index_nr as shown by Quassnoi in his view) or fill them up by hand (similarily to what Aaron suggested) after every update/delete.

If index_nr is mostly used in the application as read-only, a better solution might be to combine the answers of Peter and Quassnoi: Use either a time stamp (automatically inserted by the database by defining the current time as default) or an auto-incremented integer (as default inserted by the database) as value stored in the table. And use a view (like the one defined by Quassnoi) to access the table and the automatically calculated index_nr from Java. But also define both constraints like for the cheap solution.