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