PL/SQL (MOSC)

MOSC Banner

DBMS_AQ.DEQUEUE_ARRAY randomly dequeues same records twice

in PL/SQL (MOSC) 1 commentAnswered

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center