JSON Responses from HTTP Sender and Querying a SQL Database

[jcurry5]

I'm looking to get some feedback on a process flow I'm designing and I'm falling short.

The problem I'm trying to solve is when the initial record is sent and the HTTP receiver sends back a 'locked' response message and that same record is sent again some time later (but the initial record is still hasn't been successfully processed). I need to maintain FIFO as this drives the process flow for the receiver destination.

I tried Message Queuing but from what I initially saw, the account was trying to constantly get processed but it was locked so it was in a loop. This prevented other messages from being processed which I can't have happen.

I created another interface that takes the response from the main interface which works but the logical issue I'm having a problem with is when another instance of that record is sent and needs to be sent back. The original message hasn't been processed and I can't cleanly think of a way to handle this.

I can share code if needed but I'm wondering if I'm not using the Queueing feature correctly.
I have queue messages on failure
Retry count: 3
Rotate Queue: no (need to maintain FIFO)
Regen template: yes
Include filter/transformer: yes
I set the response status of the message when I receive a 'lock' in the header response.

Edit:
Napkin note:
if the account is locked, then set the updatedby to queued
if the account is locked a second time, then set the updated to queued
when the account's first entry is successful, set the updated to processed and retry the second entry
we need to maintain FIFO

Thanks.

[ab-20-m]

I don't fully understand the problem as stated but that could just be a me thing.

I tried Message Queuing but from what I initially saw, the account was trying to constantly get processed but it was locked so it was in a loop. This prevented other messages from being processed which I can't have happen.

I'm interpreting this statement as; you don't want a message that is pending for say Account A to block processing for all other messages when you have messages for Account B C and D. In that case, using the Advanced Queue Settings Queue Threads to a value N greater than 1 and using a Thread Assignment Variable will allow messages from up to N accounts to process simultaneously while keeping the sequence of messages with respect to the accounts. You could still get "deadlocked" if all N accounts are stuck in a pending state from the upstream responding system.

I created another interface that takes the response from the main interface which works but the logical issue I'm having a problem with is when another instance of that record is sent and needs to be sent back. The original message hasn't been processed and I can't cleanly think of a way to handle this.

I just don't understand this statement. I'm not sure which record from where and what you mean by sent back.

[jcurry5]

I tried to keep it simple but let me add more context (always seems simpler in my head!).

Here's the full process flow:
Account is sent to our application and stored in a log table in pipe delimited format. E.g. 123456|P| The |P| value can be one of 6 values depending on certain actions taken against that account (not relevant).

I have an interface that polls that log table every 5 seconds to check for new records. The db schema is simple:
create table {
id (pk),
account_string (varchar),
updated (varchar),
updated_date (datetime)
}

This interface sends out to an API URL and I'll receive a few responses back from the host depending on what happens with the record.
When the receiving interface sends a response (JSON) that contains a 'locked' error, then I set the Mirth message to Filtered and change the 'updated' column in my log table to 'queued'. There's a second interface that will poll that 'log' database only looking for 'queued' records. The reason for this approach is that when I had message queuing enabled, if a record was locked, then subsequent messages for the same record were trying to be sent, blocking other records from being processed. I can't have the main interface that sends back responses to be tied up because of one account is locked.

The complexity for me, comes from that I need to maintain FIFO for records.
Example: record-A is sent to our application and we send back a an API request and we receive back a 'locked' response. Our application, based on end-user action, will trigger that record-A to be sent back to the host API application. However, I need to maintain record continuity so record-A's first attempt is processed and then the latest attempt for record-A is processed.

Message Queuing handles that but at the expense of all other messages being held up from being sent out. However, this is just my limited understanding and using Advanced Queuing in Mirth. So I tried to get around this by creating a second interface to handle the queued records but I maybe over-engineering this.

Appreciate the response so far!

[jcurry5]

Reading more into the advanced queue settings and thread assignment variable, it seems like this will alleviate my problem.