Eventual consistency with both database and message queue records

Question

I have an application where I need to store some data in a database (mysql for instance) and then publish some data in a message queue. My problem is: If the application crashes after the storage in the database, my data will never be written in the message queue and then be lost (thus eventual consistency of my system will not be guaranted). How can I solve this problem ?

Answer 1

I have an application where I need to store some data in a database (mysql for instance) and then publish some data in a message queue. My problem is: If the application crashes after the storage in the database, my data will never be written in the message queue and then be lost (thus eventual consistency of my system will not be guaranted). How can I solve this problem ?

In this particular case, the answer is to load the queue data from the database.

That is, you write the messages that need to be queued to the database, in the same transaction that you use to write the data. Then, asynchronously, you read that data from the database, and write it to the queue.

See Reliable Messaging without Distributed Transactions, by Udi Dahan.

If the application crashes, recovery is simple -- during restart, you query the database for all unacknowledged messages, and send them again.

Note that this design really expects the consumers of the messages to be designed for at least once delivery.