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 ?

Kakalina

You would need a tracker that knows what messages were sent and what were not sent – Greene

This might help vimeo.com/111998645 – Artwork

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.

Nathanson

Very useful video. But I am surprised there are no known libraries doing "part" of the process – Kakalina

What's more a database that supports transactions is needed. Thus I think nosql databases like mongodb can't be used for that. – Kakalina

Should there be domain events are idempotent? Are whether other variants for implement the eventual consistency using non-idempotent domain events? – Protasis

Even if we use a table to write data to queue,and delete the record after a successful queue, still we run in to same problem don't we?. at t1 we read, t2 we queue to MQ and t3 we get ack, at t4 we clear the record in the db. If the process fails between t3-t4 we still have the same problem. I know MQ does not grantee exactly one delivery, but I like to here your thoughts – Beefy

@SajithSilva that is why author has mentioned the construct of aleast once delivery. – Diagnose

Answer 2

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.

Baggywrinkle

Thanks for your answer, I still have some problems: 1) If I write in the message queue before committing the transaction, the message in the queue could possibly be treated before the transaction is committed. 2) If my ID is generated by the database, I can't include it in the message posted in the queue. – Kakalina

This is a very simple approach that makes inconsistency very unlikely. It does not guarantee consistency, since a failure can occur between writing to the queue and committing to the database. This approach is interesting because of its simplicity and may well a good balance for many practical applications! Besides writing to a message queue, it works for "save to the database and contact another system" in general, given that there is only one side effect outside the database. Beware of database systems that have fail-on-commit scenarios, such as Galera Cluster. – Ramberg

@Kakalina Generally the database assigns the ID on insert, not on commit, so you should have access to the ID. You make a good point about the message queue outracing the database transaction, though. The consuming system could contact the producing system concerning this ID, and the producing system would have no knowledge of it. I wonder how likely that is... – Ramberg

Successful operations in a transaction would not guarantee 100% that the transaction itself will commit without errors. This is likely to cause inconsistencies because the message will already have been sent by that time. And also, if you decide to keep "sending messages" part outside the transaction, changes that are made durable (hence visible to outside world) will have to be rolled back if the message queue is unavailable. It is thus possible for another component of the system to have already acted upon seeing the database update which will eventually be rolled back. – Bramlett

Answer 3

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.

Metanephros

Technically, they have reduced the requirement to "the message must be idempotent". That helps a lot. It is generally achievable at that level, such as with INSERT IGNORE on the consuming side. – Ramberg

When you need mark (update/delete) in the database the message that it is sent you will come the same problem. – Yuonneyup

Answer 4

Adding to what @Gordon Linoff said, assuming durable messaging (something like MSMQ?) the method/handler is going to be transactional, so if it's all successful, the message will be written to the queue and the data to your view model, if it fails, all will fail...

To mitigate the ID issue you will need to use GUIDs instead of DB generated keys (if you are using messaging you will need to remove your referential integrity anyway and introduce GUIDS as keys).

One more suggestion, don't update the database, but inset only/upsert (the pending row and then the completed row) and have the reader do the projection of the data based on the latest row (for example)

Dewees

Answer 5

Writing message as part of transaction is a good idea but it has multiple drawbacks like

If your

a. database/language does not support transaction

b. transaction are time taking operation

c. you can not afford to wait for queue response while responding to your service call.

d. If your database is already under stress, writing message will exacerbate the impact of higher workload.

the best practice is to use Database Streams. Most of the modern databases support streams(Dynamodb, mongodb, orcale etc.). You have consumer of database stream running which reads from database stream and write to queue or invalidate cache, add to search indexer etc. Once all of them are successful you mark the stream item as processed.

Pros of this approach

1. it will work in the case of multi-region deployment where there is a regional failure. (you should read from regional stream and hydrate all the regional data stores.)

2. No Overhead of writing more records or performance bottle necks of queues.

3. You can use this pattern for other data sources as well like caching, queuing, searching.

Cons

1. You may need to call multiple services to construct appropriate message.

2. One database stream might not be sufficient to construct appropriate message.

3. ensure the reliability of your streams, like redis stream is not reliable

NOTE this approach also does not guarantee exactly once semantics. The consumer logic should be idempotent and should be able to handle duplicate message

Diagnose

As stated, and just to reiterate, database/change streams push events that occur only after that single established connection to consume data. That is a major drawback most of the time. – Bramlett