Forum Stats

  • 3,814,142 Users
  • 2,258,826 Discussions
  • 7,892,587 Comments

Discussions

Dequeue not yet committed message

Dmitrii Dunaev
Dmitrii Dunaev Member Posts: 76
edited Aug 22, 2019 9:18AM in SQL & PL/SQL

Hello everyone, we use Oracle 11.2.

Lets say I enqueue 100 messages in my session. There is a single user column in the queue table that holds unique keys. Now I want to dequeue a single message with a specific key, but I want to dequeue only the one that my sessions enqueued and not yet committed, I do not want to dequeue any messages inserted by any other sessions. In other words I just want to rollback a single message and commit the other 99. How do I do that?

Thank you in advance.

Tagged:
BEDEjflack

Answers

  • jflack
    jflack Member Posts: 1,518 Bronze Trophy
    edited Aug 22, 2019 8:36AM

    Someone correct me if I'm wrong, but I don't think what you want to do is possible.  Once a message is in the queue, you either commit and save all the enqueued messages, or rollback and save none.  So you probably need logic that will not enqueue the message with the specific key in the first place.

    Or, this may be a good case for a GLOBAL TEMPORARY TABLE.  Instead of queuing your messages, INSERT them into the table.  When you need to delete one, just delete it from the table.  When you are ready to commit, loop through the table, queue the messages that are still there, then commit.

    BEDE
  • Mike Kutz
    Mike Kutz Member Posts: 6,169 Silver Crown
    edited Aug 22, 2019 8:54AM
  • jflack
    jflack Member Posts: 1,518 Bronze Trophy
    edited Aug 22, 2019 9:18AM

    Forgot that procedure was there, thanks, Mike.

    Dmitrii:  If the amount of data to be queued isn't a lot ("a lot" being a relative term) then you might skip the temporary table and just add the messages to a PL/SQL collection (TABLE OF payload type), then you can use a bulk enqueue as Mike suggests to enqueue the whole table at once.  Define the table in a package body to give it session scope.  But if the amount is large enough to have an impact on memory used, then use the table, BULK COLLECT into a collection when you read the table, and enqueue it block by block.