title: How to manage consecutive column values in table rows
body: <p><strong>A little presentation for what I want to do:</strong></p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>All of this is managed in a Java web app with an Oracle DB.</p>
  
  <p><strong>Now, my problem:</strong></p>
  
  <p>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. </p>
  
  <p>When inserting a record in the table for the trip, the way of getting the index is by</p>
  
  <p><code>SELECT MAX(INDEX_NR) + 1 AS NEXT_INDEX_NR FROM TABLE</code> </p>
  
  <p>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.</p>
  
  <p>How can I manage this? I have been thinking of 3 ways so far:</p>
  
  <ol>
  <li>Use an isolation level of <code>Serializable</code> for the DB transactions (don’t like this one);</li>
  <li>Insert a record with no <code>INDEX_NR</code> and then have a trigger manage the things… in some way (never worked with triggers before);</li>
  <li>Each record also has a <code>UPDATED</code> column. Could I use this in some way? (note that I can’t lose the <code>INDEX_NR</code> since other parts of the app make use of it).</li>
  </ol>
  
  <p>Is there a best way to do this?</p>