0

I have a system which includes MySQL as database and RabbitMQ for organizing asynchronous data processing. There are two processes (in two different containers) that work with the same record. First one updates record status in db transaction and sends message to rabbit queue. Second process fetches record from db and does some job. The problem is that the second process can read the message from the queue before the first process completes the update of the record.

Currently, in order to avoid this problem, the second process checks the status of the record, if it does not correspond to the target value, then the process waits for an update by re-sending it to the same queue.

This behavior occurs due to the fact that sending to the queue is performed within the transaction context. If I move the sending to the queue outside the transaction, it is possible that an error will occur or the process will be interrupted after the db transaction completed, the status in the database will change, but the message will not be sent to the queue, and the second process will not process this record.

What can you suggest to solve this architectural problem?

smiler
  • 1
  • Why you send to MQ message with info **Data is ready** instead of: **This is the data you need**. Second process will not have to read the row from db, but from message. – Peter Trcka Oct 14 '21 at 15:56
  • Because I don't have all data in first process, that I need in the second. – smiler Oct 20 '21 at 11:26
  • Based on your interpretation of time delay between processes and data availabity, I am affraid that you have a design flaw rather than issue with RMQ and MySQL. Reason is simple delay between completing transaction, sending the message and commit trasaction to MySQL is miliseconds. So I think that operation in both processes are atomic. Therefore can you be more specific with the process, what is each one doing and their purpose? – Peter Trcka Oct 20 '21 at 21:44
  • I agree that this is a disign issue (I wrote this in initial post). The question is how to avoid this problem. There are several solutions that to my mind. One is to send all the required data to the queue, but I cannot use that method at the moment. Another is to use mq transaction (I'm currently sticking with this one). You can also wait for the state in the database to update, as I described in the post. – smiler Oct 21 '21 at 19:09

0 Answers0