Transactional Outbox Pattern
When you transfer money from one account to another, two things happen: one account is debited and the other is credited. These operation must either succeed or both fail - anything else leaves the system in an inconsistent state. This is also known as atomicity, a principle where a group of related operations are treated as a single, indivisible unit.[1]
In systems where both actions occur in the same database, this guarantee is provided by transactions [2]. But what happens when we need to ensure atomicity between independent operations like sending a notification after a debit occurs on an account?
If you write the record (ie debit entry) to the database and the system crashes or fails before the notification is sent, the user might never be informed of a successful transaction. On the other hand, if you send the notification first and then fail to persist the debit record due to a database error, you risk sending a notification for an operation that never occured on the system.
The solution to this is the Transactional Outbox Pattern[3]. This approach
leverages the transaction guarantees of the database by introducing a seperate
table commonly referred to as the outbox table. When we perform an operation
in the database (ie debiting an account), we also insert a corresponding event
to the outbox table within the same transaction. By bundling both operations in
a single transaction, we ensure that if a debit succeeds the event is recorded
and if the debit fails, the event is not stored too.
After we write an event to the outbox table, we can publish it to the message broker to handle the notifcation, after which we delete the event from the outbox. If anything happens in between, say the message broker was down due to a network failure or the notification was sent but the database goes down before the event is deleted, the event remains in the outbox.
A background job routinely scans the outbox table, republishes the events that are left to the message broker and deletes them after a succesful operation.
One of the tradeoffs of this approach is that we risk sending multiple
notifications for the same event (the background job can fail to delete the
event after successfully sending the notification and therefore processes the
event in the next cycle). This is referred to as at-least-once delivery.
1. Atomicity (database systems) Wikipedia
2. Transactions in DBMS Geeks for Geeks
3. Transactional outbox pattern Amazon