If the maximum current value of an ID generated by a sequence is N, does that guarantee that all future rows will have index > N


I'm debugging some legacy code and have found what might be a flaw in how it interacts with the database. A simplified version of it is as follows:

TableA is acting as a queue. It has data inserted into it from multiple users/sessions at pretty high velocity. There are never any updates or deletes except the one I am about to describe. This table has an integer ID which is populated by a 'before insert' trigger on the table that draws values from a sequence.

A consumer application repeatedly selects the whole table, processes the data, and stores the maximum index value of the resultset each time. It then executes

DELETE FROM TableA WHERE id<=:maxSelectedID

My question is, is this a reliable way to delete all the data that was previously selected (and only that data)? I feel like with the volume of inserts on this table that it is quite possible that a higher ID might get committed (and thus be available to select) before a lower one. In this case the lower one could potentially be deleted without ever having been selected (or if we are lucky it would also miss the delete get caught in the next select).

Best Answer

If the database is running on a RAC cluster, each node in the cluster would have a separate sequence cache. That would cause the values returned to be out of order unless the sequence was created with ORDER specified (i.e. CREATE SEQUENCE seq_foo START WITH 1 ORDER).

If the database is running on a standalone instance, sequence numbers will always be generated in order. If you're depending on this behavior for functional reasons, I'd be pedantic and specify ORDER when creating the sequence even on a standalone database. But it isn't necessary to do so.

From the documentation

ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.

The other potential issue would be if the sequence reached the MAXVALUE and was set to CYCLE by starting over at the minimum value. NOMAXVALUE and NOCYCLE are the defaults, though, so it would be pretty unusual that someone using a sequence like this would create the sequence in that way.