Applying The Transactional Outbox Pattern In ColdFusion
On this blog, as with most ColdFusion applications, the majority of workflows orchestrate a change of state within the bounded context of the ColdFusion application itself. But, in some smaller portion of those workflows, I need to effect change in a remote system. In my case, that remote system is the Postmark SMTP service, which is how I send emails in response to people's posted comments. In order to ensure email delivery, I've added a transactional outbox as a point of indirection between the logic that persists a reader's comment and the logic that sends out the email to blog subscribers.
How My Blog Used To Work
Before the transactional outbox, the workflow that kicked-off when a reader posted a comment looked something like this (heavily redacted pseudo code):
component {
public any function addComment() {
transaction {
insertComment();
subscribeMemberToPost();
subscribeBenToPost();
}
sendCommentNotifications();
if ( isFirstMemberComment() ) {
sendThankYouNotification();
}
}
}
The actual code is quite a bit more complicated than this; but, this pseudo code more-or-less hits the high points. I have a transaction block that handles the database updates atomically (ie, all-or-nothing); then, once the database has been updated, I send the comment notifications and an optional "thank you" email if this is the reader's first time participating on this site.
And, the truth is, for a "blog", this has been 100% fine. In fact, I can't specifically think of a time in which this workflow was problematic.
Aside: I did, however, have about a year of time in which I had a bug in my subscription logic that stopped sending out emails to anyone other than me. Huge shout out to Chris Geirman, who still tirelessly participated in conversations despite never getting a single comment email! That man is a true hero!
That said, this blog is one of the places in which I get to practice my deep thinking about software development. And, this workflow has several critical points of failure, even if they are more theoretical than practical. Let me output the pseudo code one more time with annotations:
component {
public any function addComment() {
transaction {
insertComment();
subscribeMemberToPost();
subscribeBenToPost();
}
// ---> (A)
sendCommentNotifications(); // ---> (B-B')
// ---> (C)
if ( isFirstMemberComment() ) {
sendThankYouNotification(); // ---> (D)
}
}
}
Anything that happens inside the transaction block is fine - that's the point of a transaction: all the records either commit or roll-back in unison. The danger zone is the post-transaction logic, once the database changes have been committed and further action is still required.
(A) - if the ColdFusion server crashes or the request times-out after the
transactionblock, none of the emails ever get sent.(B-B') - the comment-to-email ratio is a fan-out algorithm. One comment might lead to N-emails, one for each reader that's already subscribed to the given post. The ColdFusion server could crash (or the request could time-out) at any point in that algorithm. Which means maybe none of the emails go out. Maybe one of the emails goes out. Maybe half of the emails go out. We really have no way of knowing without looking at the SMTP log.
(C) - if the ColdFusion server crashes or the request times-out after the subscriber emails go out, the "Thank You" email never goes out.
(B-B') / (D) - while not obvious in this pseudo code, I was using ColdFusion
CFMail's spooling feature. Which means that when I "send" an email, the email isn't sent immediately. Instead, ColdFusion writes a file representation of the email to disk and then processes those files in asynchronous batches. Which means that if the remote SMTP server is down (Postmark), theCFMailtag silently fails and I basically never find out about it.
The value-add of the transactional outbox pattern is that we can eliminate all of these points of failure; and, probably, make the request handling more responsive (ie, faster) in the process.
Never Move External Calls Into The transaction
The atomic guarantees of a database transaction are amazing. It's a huge part of the value-add of using a relational database (or any other type of database that offers transactions). In fact, it's so amazing that you might be tempted to just move "all the important bits" up and into the transaction block.
Do not do that. It is an anti-pattern. Database transactions lock database rows and hold database connections open. The longer a transaction block takes to complete, the more stress you place on your database server.
This is doubly true for calls to an external system. Network failures, remote system failures, slow responses - the messiness of the outside world can either cause transactions to be held-open for a nondeterministic amount of time; or, they can cause the persisted changes to be rolled-back upon failure.
Moving everything into the transaction block might solve one type of problem, but it introduces a slew of new problems.
Implementing the Transactional Outbox
The transactional outbox is, at a fundamental level, just a message queue. A producer(s) writes messages to the outbox. Then a consumer(s) read those messages and execute on them.
What makes the transactional outbox pattern different from every other type of message queue is where it persists your messages: your database. Unlike Amazon SQS, or RabbitMQ, or Kafka — which are all external systems — the transactional outbox stores all of its data inside the transactional boundary of your database.
Which means that you can write to the outbox within a transaction block in order to atomically guarantee that the outbox messages are persisted along with your database mutations.
That's the whole show — that's the value-add!
It's not "free". It adds complexity. And, it fundamentally changes the semantics of the workflows: logic goes from "at most once" processing to "at least once" processing. Both of these strategies make trade-offs. But, the outbox pattern has better trade-offs.
With a transactional outbox in place, the previous pseudo code for posting a comment now looks like this:
component {
public any function addComment() {
transaction {
insertComment();
subscribeMemberToPost();
subscribeBenToPost();
// Atomically enqueue outbox message.
enqueueCommentNotifications();
if ( isFirstMemberComment() ) {
// Atomically enqueue outbox message.
enqueueThankYouNotification();
}
}
}
}
The entire workflow now lives inside the atomic transaction block. This is now reasonable because the calls to the external systems are no longer taking place here. Instead, this workflow is atomically enqueuing messages to be processed asynchronously.
In my ColdFusion application, this asynchronous processing is implemented as a scheduled task. Every 60-seconds, I have a task that performs a while(true) loop and just pulls one message off of the outbox at a time and processes it (passes it off to a registered handler component). The heavily redacted pseudo code looks like this:
component {
public any function processOutbox() {
while ( true ) {
var message = getNextMessage();
var handler = registry[ message.type ];
try {
// Do the "external systems" integration here!
handler.processMessage( message );
// On success, we delete the message.
deleteMessage( message );
} catch ( any error ) {
// On failure, we atomically move the message to a "dead letter queue".
transaction {
createFailure( message );
deleteMessage( message );
}
}
}
}
}
My ColdFusion application runs on a single instance. And my scheduled tasks use an exclusive named lock for each task component. Which means that the processing of the outbox is implicitly single-threaded in my application architecture. As such, I don't need to worry about row locking or having multiple consumers trying to process the same message at the same time. This keeps my consumer logic relatively simple.
Earlier I mentioned that the external systems work changes from "at most once" processing to "at least once" processing. Now that we have the pseudo code for the asynchronous processing, its easier to see why: there's a point of failure in between the processMessage() call and the deleteMessage() call. If the server crashes in between these two calls, it means the comment email was sent out, but I never removed the message from the outbox message queue. Which means that when the service reboots, the message will be processed again, possibly resulting in a duplicate email.
Everything is a trade-off.
For email notifications, I would rather err on the side of sending a duplicate email if it means that the email has guaranteed delivery.
For other workflows, like logging errors, I'm happy to just do that inside a asynchronous CFThread; and if the server crashes and I never push the error data to the external Bugsnag system, oh well — I'm not losing sleep over it.
No More Email Spooling
In order to make sure that the email is truly delivered as part of the outbox processing, I disabled the spooling. By default, when you "send" an email with CFMail, the mail doesn't go out immediately. Instead, ColdFusion writes it to disk and then processes it asynchronously in batches. Which reintroduces the point of failure that the transactional outbox pattern was intending to remove.
So now that I have the outbox in place, in the CFMail tag I set spoolEnable=false. This causes ColdFusion to process the email immediately; and, most importantly, to raise SMTP errors immediately. This means that my while(true) loop will block-and-wait for each email to go out. But, it guarantees that a successful CFMail call leads to a successful email delivery (to at least Postmark - what happens after that is out of my application's control).
My Outbox Database Table Schema
As I mentioned earlier, my ColdFusion scheduled task processing is single-threaded. This greatly simplifies my outbox processing; and, as a byproduct, simplifies the outbox database structure. On top of that, I'm not doing any kind of automatic retries — any kind of retry at this point is an Administrative action using a user interface (UI) that I haven't even built yet.
In the following tables, the type column is how I map a message to the ColdFusion component that processes it. Everything else about the message is stored in the payload JSON column, which is an opaque object understood only by the component that enqueues and processes that message.
CREATE TABLE `outbox_message` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(50) NOT NULL,
`payload` json NOT NULL,
`attempt` tinyint unsigned NOT NULL,
`createdAt` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `outbox_failure` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(50) NOT NULL,
`payload` json NOT NULL,
`attempt` tinyint unsigned NOT NULL,
`error` text NOT NULL,
`createdAt` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
The outbox_failure table is my "dead letter queue". During the outbox while(true) loop, if a message fails to be processed, I move that record from the outbox_message table to the outbox_failure table for future review and possible re-queuing.
A Concrete Example
In my pseudo code, I talk about "enqueuing" and "processing" messages. In my actual ColdFusion code, both of those actions are handled by the same ColdFusion component. To make this more concrete, here's the CFC that manages the "Thank You" email for first-time commenters. It's the most simple example I have, but I think it paints the picture.
Notice that the enqueueMessage() does nothing more than write simple values to the outbox table. All "meaning processing" is handled on the asynchronous side of the outbox processing.
component {
// Define properties for dependency-injection.
property name="config" ioc:type="config";
property name="emailClient" ioc:type="core.lib.integration.email.EmailClient";
property name="memberModel" ioc:type="core.lib.model.blog.MemberModel";
property name="messageModel" ioc:type="core.lib.model.outbox.MessageModel";
// ColdFusion language extensions (global functions).
include "/core/cfmlx.cfm";
// ---
// PUBLIC METHODS.
// ---
/**
* I enqueue the outbox message for asynchronous processing.
*
* Caution: this method should be called inside a transaction block such that the
* enqueued message is created atomically within the broader workflow.
*/
public void function enqueueMessage( required numeric memberID ) {
messageModel.create(
type = "comment.thankYou.notification",
payload = {
memberID,
}
);
}
/**
* I process the outbox message.
*/
public void function processMessage(
required string type,
required struct payload
) {
send( argumentCollection = payload );
}
/**
* I send the welcome email to the given member.
*/
public void function send( required numeric memberID ) {
var member = memberModel.get( memberID );
var partial = {
subject: "Thank you for joining the conversation!",
teaser: "You rock!",
wwwUrl: config.url,
hero: {
url: "cid:bennadel",
width: 544,
height: 400
},
to: {
name: member.name
}
};
savecontent variable="local.body" {
include "/email/thank_you_for_joining.cfm";
}
emailClient.sendMail(
to = member.email,
from = "Ben Nadel <ben@bennadel.com>",
subject = "Thank you for joining the conversation!",
body = body,
tag = "member-welcome",
attachments = [
{
file: expandPath( "/images/blog/ben_nadel_of_bennadelcom_v2.jpg" ),
contentId: "bennadel",
disposition: "inline"
}
],
// Disabling the spool so that email failures FAIL-FAST, causing the outbox
// processing to register this message as a failure, not a false success.
async = false
);
}
}
While an unofficial requirement, I'm settling on a pattern in which every producer / consumer of the outbox is represented as a ColdFusion component with the following API:
enqueueMessage( ...args )processMessage( type, payload )
And if you recall from my outbox processing pseudo code earlier, it's the type value that maps the target CFC to the message.
As Simple As Possible, As Complex As Necessary
My ColdFusion blog is simple. It's a single instance with relatively low traffic and only minor throughput requirements. As such, I've been able to keep my transactional outbox implementation very simple. No row locking. No "claiming" rows. No deferred execution. No automatic retries. No change-data capture mechanics (CDC). No tailing the "write ahead log" (WAL).
The complexity of the implementation is commensurate with the needs of the system. If your system is more complicated, if your workflows are more demanding, you'll need a more complex implementation. The trick is to find the right balance.
For me, the right balance was allowing me to explore the concept of the transactional outbox pattern and increase the robustness of the architecture without doing too much hand-wringing.
Want to use code from this post? Check out the license.
Reader Comments
Hey Ben - any chance you're on Postgres? There's a fun Postgres-specific follow up to transactional outbox that I can send over.
@Joe,
I'm on MySQL. But, I'd be curious to know what you're talking about, both out of curiosity and also as something that might have parallels in MySQL.
Firstly, thanks for the shout out! That made me smile today. 😁
Secondly, I really REALLY like this approach! It's giving me thoughts about restructuring my transactional email notification strategy.
@Chris,
The nice part was that it was actually quite easy to move to. I already had
sendEmails()as a separate method — more or less. So, all I had to do was change that toenqueueMessage(), and then stick the scheduled task in the middle. It really wasn't much more than that.The biggest difference right now is that there's up to a 60-second delay between posting a comment and the outbound emails. But, in the grand scheme of things, I doubt anyone will ever notice.
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →