Redundancy in process that reads and deletes from database

Question

I need to write a service that reads data (a row) from a database, sends out AWS SQS messages, and finally deletes the data from the DB.

Let me explain using an example. I have a database table D, the service S, and SQS queue Q. Every minute, the service S, reads one row (R1) from D, parses it, and sends a SQS message to Q, and finally deletes the row R1.

The problem is, this is a critical service and so I need redundancy. Additionally, SQS message for every row should be sent exactly once.

Possible solutions I thought of (and problems with them):

1. Multiple processes: I can run the same script on multiple servers. The likelihood of all of them crashing together is low. However, this can lead to multiple SQS messages being sent for the same row. Processes P1 and P2 might read and send SQS message for the same row. If I delete the row, before sending the SQS message, and the process crashes, then I might permanently lose the row without sending any message.

2. Multiple processes that operate with a delay: I can run multiple processes. P1 operates as explained above. P2 operates with the check that it processes rows that are at least (e.g) 10 minutes old. P3 operates on rows that are at least (e.g.) 20 minutes old. So if P1 crashes, P2 can process the data. If P1 and P2 crash, then P3 handles the load etc. The issue with this approach is that it can cause delays in processing.

Are there any standard design/architecture patterns that I can use for such a problem?

Thank you!

Answer 1

Are there any standard design/architecture patterns that I can use for such a problem?

Transactions and retry mechanisms.

A transaction is a mechanism that fully succeeds or fully fails. It never leaves your operation in an incomplete state; it will either execute the entire operation, or none of it.

You could write a stored procedure. Include your SQS messaging in the stored procedure, with a retry loop until it succeeds or permanently fails (it should succeed 99 percent of the time, and only permanently fail if there's some unrecoverable problem). Wrap the whole thing in a database transaction, and roll it back if you can't get the SQS message to succeed. Your database will automatically handle any concurrency issues.

Redundancy is an orthogonal concern; it's based on your infrastructure. Make sure your database server is robust.

Answer 2

Without a distributed transaction manager you cannot make absolute "only-once" guarantees.

There are ways around this. AWS FIFO queues, for example, will reject duplicate messages received within 5 minutes. You could also design the receiving system to discard duplicates.

That leaves the problem of redundancy. My preferred approach is to use a hot standby, using a system such as ZooKeeper to identify when the primary goes down.

Alternatively, you could use a database handshake. To do this you add a processed_by column to the source record; it will hold a unique process identifier. Then, you run the following within a transaction:

update  MYTABLE
set     processed_by = 'UNIQUE_PROCESS_IDENTIFIER'
where   processed_by is null;

select  -- rows
from    MYTABLE
where   processed_by = 'UNIQUE_PROCESS_IDENTIFIER'

This requires an index on processed_by that supports nulls (or a small table containing the messages to send), and will add load to the database. Perhaps more important, if you have multiple consumers you lose any ordering guarantees: while consumer #1 is processing messages, the producer may add rows that are picked up and processed by consumer #2.