0

I have a node.js function that needs to be executed for each order on my application. In this function my app gets an order number from a oracle database, process the order and then adds + 1 to that number on the database (needs to be the last thing on the function because order can fail and therefore the number will not be used).

If all recieved orders at time T are processed at the same time (asynchronously) then the same order number will be used for multiple orders and I don't want that.

So I used rabbit to try to remedy this situation since it was a queue. It seems that the processes finishes in the order they should, but a second process does NOT wait for the first one to finish (ack) to begin, so in the end I'm having the same problem of using the same order number multiple times.

Is there anyway I can configure my queue to process one message at a time? To only start process n+1 when process n has been acknowledged?

This would be a life saver to me!

Bruno Lira
  • 177
  • 3
  • 13
  • 1
    I'm confused, let's keep RabbitMQ out of this for the moment. Please update the question with more detail on the interaction between your app and Oracle. 1) It sounds like you get orders which invoke a function in your app. I get that 2) this bit needs clarification: "process the order and then adds + 1 to that number on the database". What are you adding 1 to? Where did the original number come from? What is the general flow to the database? Just a single insert at the end of your work? And you don't need an order number until then? – Dan McGhan May 15 '20 at 19:41
  • 1
    You said, "Unfortunately I can't because it's a client's db (my app should integrate with an existing application)" Are you saying that you're inserting into an existing orders table that doesn't have any logic to manage the orders number? My guess is you are wrong about this. You should ask the client how order numbers are generated on their end. – Dan McGhan May 15 '20 at 19:42
  • Hi Dan! Yes, the situation is exactly like you said, I am inserting into an existing orders table that doesn't have any logic to manage the orders numbers!... So, I've created a way to control order processing and manage order numbers using Redis (since there really is no managing on the database). When I showed it to the people responsible for the other app they couldn't have cared less..."it's working like it is, why change now?"...well, I guess there will be multiple orders using the same number and theres nothing I can do :( – Bruno Lira May 17 '20 at 02:11
  • I think there's a misunderstanding here. Can you show us the definition of the table? Do you know how to generate the DDL for it? See this: https://stackoverflow.com/questions/26249892/how-can-i-generate-or-get-a-ddl-script-on-an-existing-table-in-oracle-i-have – Dan McGhan May 17 '20 at 16:16

1 Answers1

1

If the problem is to avoid duplicate order numbers, then use an Oracle sequence, or use an identity column when you insert into a table to generate the order number:

CREATE TABLE mytab (
       id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY(START WITH 1),
       data VARCHAR2(20));
INSERT INTO mytab (data) VALUES ('abc');
INSERT INTO mytab (data) VALUES ('def');
SELECT * FROM mytab;

This will give:

        ID DATA
---------- --------------------
         1 abc
         2 def

If the problem is that you want orders to be processed sequentially, then don't pull an order from the queue until the previous one is finished. This will limit your throughput, so you need to understand your requirements and make some architectural decisions.

Overall, it sounds Oracle Advanced Queuing would be a good fit. See the node-oracledb documentation on AQ.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
  • Hi Christopher! YES, identity column would be nice if I could make changes to oracle. Unfortunately I can't because it's a client's db (my app should integrate with an existing application). I was under the impression that rabbitmq would only start consuming the second message once the first message returns ack (which happens once the order process is complete), but that doesn't seem to be the case. – Bruno Lira May 14 '20 at 22:27