Eventual consistency with both database and message queue records

I am assuming that you have a loss-less message queue, where once you get a confirmation for writing data, the queue is guaranteed to have the record.

Basically, you need a loop with a transaction that can roll back or a status in the database. The pseudo code for a transaction is:

• Begin transaction
• Insert into database
• Write to message queue
• When message queue confirms, commit transaction

Personally, I would probably do this with a status:

• Insert into database with a status of "pending" (or something like that)
• Write to message queue
• When message confirms, change status to "committed" (or something like that)

In the case of recovery from failure, you may need to check the message queue to see if any "pending" records were actually written to the queue.

I'm afraid that answers (VoiceOfUnreason, Udi Dahan) just sweep the problem under the carpet. The problem under carpet is: How the movement of data from database to queue should be designed so that the message will be posted just once (without XA). If you solve this, then you can easily extend that concept by any additional business logic.

CAP theorem tells you the limits clearly.

XA transactions is not 100% bullet proof solution, but seems to me best of all others that I have seen.

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.