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?

Question

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).

Answer 1

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.

Answer 2

As already pointed out, you can get value from sequence and wait for an hour before commiting your data. After the commit you'll have a record with id that is much lower then the other records that are being commited immediately at that time.

I'd recommend either

1. adding a column that would mark the row as being processed and delete only these rows, or

2. using ORA_ROWSCN instead of Id column as it represents a commit timestamp (for your case you'd have to have table created with ROWDEPENDENCIES, otherwise ROWSCN is assigned to the whole block, not every row).

a query on the ORA_ROWSCN of row R after the commit of T1 will return a value lower than the value returned after the commit of T2

that is the quarantee you are looking for ...

Answer 3

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

That is the case. As others have stated the value obtained by a sequence won't necessarily be in an order either if you're using RAC and the inserts are done from multiple instances and you didn't include the ORDERED clause in the create sequence.

If you want to implement a queue then it would be wise to use the built in functionality - Advanced Queues . As always, there is a decent write up to get started with on Oracle-Base. This is tried and tested and performs very well.