Overview
The transactional outbox pattern solves the dual-write problem in distributed systems, ensuring state consistency when a single operation involves updating a database and sending a message or event notification
Traditional distributed transactions (2PC) are often impractical due to their performance overhead. Additionally, many databases and message brokers do not support 2PC, and even when they do, coupling the service to both systems is generally undesirable.
Implementation
- Application writes the event to the outbox table within the same transaction as the business data update
- After the transaction commits, a separate process (message relay) reads events from the outbox and reliably publishes them to the message bus, ensuring eventual consistency
- After publishing the event, it is deleted from the outbox or marked as processed to prevent duplicates
There are a couple of different ways to implement a message relay:
Polling
Message relay publishes messages by periodically polling the outbox table
To prevent multiple relays from reading the same events, we use SELECT FOR UPDATE to lock the rows until the transaction is committed. The SKIP LOCKED option ensures that the relay doesn’t block other transactions while they commit
Once the transaction is committed and the lock is released, other relays can select the processed records. To prevent this, we delete the processed rows from the outbox table within the transaction
BEGIN;
DELETE FROM outbox
WHERE id IN (SELECT o.id
FROM outbox o
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 10)
RETURNING *;
-- publish message here
COMMIT;Change Data Capture (CDC)
Message relay can use Change Data Capture (CDC) to tails the events from the outbox table