История изменений

Кстати ChatGPT подсказал шикарный паттерн. Кажется это вообще лучший вариант.

To design a system that combines database transactions and event messaging in a durable way, consider implementing the Outbox Pattern. This pattern involves adding an intermediate step in your database to store events or messages that need to be published to the event queue. The workflow for Service A would look like this:

1. Begin Transaction.
2. Insert Row into your business table.
3. Insert Event into an «outbox» table within the same transaction. This event contains all the necessary information for the next service (e.g., Service B) to process, such as the row ID. 4 Commit Transaction. At this point, both the business data and the event are durably saved in the database.
4. Publish Event to the queue from the outbox table. This can be done by a separate process that scans the outbox table for new events and publishes them to the event queue. After a successful publish, the event can be marked as published or deleted from the outbox table to avoid re-publishing.

For Service B, the workflow remains as you described. It listens for messages from the event queue, begins a transaction to update the row, and commits the transaction.

By using the Outbox Pattern, you ensure that:

• The database operation and the creation of the event are atomic. If the application crashes before the transaction is committed, neither the business operation nor the event will be persisted, maintaining consistency.
• The event will only be published if the transaction succeeds, avoiding situations where an event is published without the corresponding database change.
• You decouple the transactional work from event publishing, which can improve performance and reliability.
• For the process that publishes events from the outbox to the event queue, you can implement a polling mechanism or use database features like Change Data Capture (CDC) if supported, to react to new events in the outbox table more efficiently.

This design improves fault tolerance and ensures that events corresponding to database changes are not lost, even if a service crashes immediately after committing a transaction.

Получается, что мы свели задачу к тому, чтобы для заданной записи в таблице outbox создать сообщение в message queue и потом удалить запись в таблице outbox. Это, конечно, тоже требует координации между базой данных и очередью сообщений. Тут либо будут (в случае проблем) двойные сообщения, либо нужна какая-то фича от системы сообщений для предотвращения двойных сообщений. В целом обе проблемы решить проще, чем исходную. Надо ещё покумекать, но кажется это тоже можно решить с ещё одной таблицей для полученных сообщений. Или мы обновляем БД и пишем, что сообщение с таким-то ID получили (в одной транзакции), или всё откатываем и тогда сообщение обработаем ещё раз.

Кстати ChatGPT подсказал шикарный паттерн. Кажется это вообще лучший вариант.

To design a system that combines database transactions and event messaging in a durable way, consider implementing the Outbox Pattern. This pattern involves adding an intermediate step in your database to store events or messages that need to be published to the event queue. The workflow for Service A would look like this:

1. Begin Transaction.
2. Insert Row into your business table.
3. Insert Event into an «outbox» table within the same transaction. This event contains all the necessary information for the next service (e.g., Service B) to process, such as the row ID. 4 Commit Transaction. At this point, both the business data and the event are durably saved in the database.
4. Publish Event to the queue from the outbox table. This can be done by a separate process that scans the outbox table for new events and publishes them to the event queue. After a successful publish, the event can be marked as published or deleted from the outbox table to avoid re-publishing.

For Service B, the workflow remains as you described. It listens for messages from the event queue, begins a transaction to update the row, and commits the transaction.

By using the Outbox Pattern, you ensure that:

• The database operation and the creation of the event are atomic. If the application crashes before the transaction is committed, neither the business operation nor the event will be persisted, maintaining consistency.
• The event will only be published if the transaction succeeds, avoiding situations where an event is published without the corresponding database change.
• You decouple the transactional work from event publishing, which can improve performance and reliability.
• For the process that publishes events from the outbox to the event queue, you can implement a polling mechanism or use database features like Change Data Capture (CDC) if supported, to react to new events in the outbox table more efficiently.

This design improves fault tolerance and ensures that events corresponding to database changes are not lost, even if a service crashes immediately after committing a transaction.

Получается, что мы свели задачу к тому, чтобы для заданной записи в таблице outbox создать сообщение в message queue и потом удалить запись в таблице outbox. Это, конечно, тоже требует координации между базой данных и очередью сообщений. Тут либо будут (в случае проблем) двойные сообщения, либо нужна какая-то фича от системы сообщений для предотвращения двойных сообщений. В целом обе проблемы решить проще, чем исходную.