DBMS_AQ.DEQUEUE_ARRAY randomly dequeues same records twice
Got this with 11g, 12c, and 19c.
We use DBMS_AQ.DEQUEUE_ARRAY to dequeue a large number of items.
We expected the number of dequeued items to be the same as the enqueued items.
The array size is 8092.
Randomly, we get the same record dequeued twice.
We initially had a COMMIT inside the loop that processes the array (commit once per batch).
When we move the COMMIT outside the loop, we no longer get duplicates, but then performance suffers badly.
Bouncing the database regularly solves the issue for an undetermined while, but this will not be possible in production.