What Ever Happened to Atomic Transactions?

First, there was RDB-SQL. Pega applications could execute SQL directly against external databases and bind those results to Properties on the Clipboard. Then, Pega introduced the ability to bind to external databases as if they were local using Connect-SQL rules in conjunction with a handy accelerator that derived all of the object-relational mapping (ORM). RDB-SQL became semi-obsolete - and it was really quite a lot better. Then came The Cloud. (Insert triumphant music here, as if it were the deus-ex-machina of our application hosting tragedy.) This is generally an auto-configured set of servlet containers that can readily scale into configurations beyond our wildest dreams.

All of these items made our life a little easier as implementors of Pega technology, and each is without question the right thing to do with respect to the product, but it has had the effect of pushing some of our database considerations into the background. The idea that you shouldn't have to worry about the database is a good one, right up to the point where it isn't. One example is when leveraging an external database. Here's why.

Remember when you first were introduced to ACID? No, not the acid where you swore you saw a full size giraffe wearing a fedora in the microwave, but ACID transactions: atomic, consistent, isolated, durable. The acronym described the conditions of a reliable database transaction, and it was especially important (and more difficult) across distributed databases. Part of this was solved by EJB containers - I use Java as my example here, since Pega deploys as a Java application - that managed XA transactions, provided you had all the right drivers installed, etc., etc. They ensured your transaction's atomicity by obtaining a "vote" from each database (or other data source) before committing. If one vote was "no," or a transaction otherwise failed, the entire set of transactions would roll back: all or nothing. Today's cloud servers (and internally hosted solutions as well) are most often configured with Servlet containers rather than EJB containers, and for the most part, it's perfectly acceptable. Except Servlet containers do not support XA transactions. This means that there is no guarantee of transactional atomicity across multiple data sources.

Let's take the example of saving a person's demographic information. You have a workflow that saves automatically into the PegaData database (using Pega's own deferred save model and established transaction boundaries), but you also need to save information into the company's system of record for this information, which is a database outside of the Pega ecosystem - let's call it the DemographicsDB.

First, since this is an external database, you are going to have to commit data into the DemographicsDB explicitly, as Pega only manages the transactions against PegaData. That means executing an explicit Commit in an Activity and justification of your multiple sins as evidenced by the severe and moderate guardrail warnings this necessity has produced. (You should also use the Data Access Object pattern we've previously described here. Seriously.) Those warnings - at least the one about the explicit commit - are real, though.

The reason that's a problem was probably already described to you in your Senior System Architect coursework, because it commits outside the transaction boundaries of the deferred save model - but that's always the case when you are using an external database! See where I'm going with this? What happens when the workflow is saved at a transaction boundary and it fails? You've already committed the changes to the system of record, but the user gets (hopefully) an error saying something's gone wrong. Error handling can manage some of these issues, but without delving into the murky and uncertain waters of compensating transactions (which can also fail), there is no automatic way of undoing transactions that have already been committed. In this instance, it might be ok, but what if the last of the three tables you needed to save to fails? You may find yourself having committed a partial transaction, and you now have data in an inconsistent state. What will the neighbors think?

Now... using servlet containers that do not support XA transactions is a conscious decision made for a number of reasons, not least of which is the necessary independence of cloud instances. So, generally speaking, we are asserting that the risk of having non-atomic transactions is worth the benefits of the more simplified architecture. Widespread adoption of this model seems to imply there is relative consensus, so how do we protect ourselves as best we can?

At one point, I started writing my own transaction registry to register and manage these transactions and their related compensating transactions. Don't do that. It's pretty difficult, for one, and it won't quite solve everything. You'll feel pretty darn clever about it for a few days, though. That feeling ends when you have to start making compromises and exceptions to a point where you're not really solving enough to make it worthwhile. Then, you feel like an idiot who has spent too much time on something that won't really work.

Try this instead:

1) Understand Pega's transaction boundaries and deferred save model. Basically, any time you're leaving an assignment, you're going to commit to PegaData.

2) Understand that you have to manage your external database transactions as atomically as possible. If you are saving information across multiple tables, issue a Commit after performing the Obj-Save on all the instances, not one at a time. It's not only better performance, it will ensure that the failure of one insert (or update, or delete) rolls back the entire transaction. You can then take steps in your error handling to make the Pega application react accordingly.

3) Understand the ramifications of not having distributed transactions and manage your workflow accordingly. "Knowing is half the battle," or so my Saturday morning cartoons once said, so be conscious of this issue rather than justifying the guardrails warning without consideration of the impact.

4) Keep this same scenario in mind when committing transactions to external resources such as web services, as well, since those are another category of external data that can both fail and/or require a compensating transaction should a later commit fail.

If you keep these simple guidelines in mind, you'll avoid some very unfortunate issues that tend to only surface at scale, in production... which makes for a number of unpleasant meetings and more work on the weekends that anyone prefers. Pega is an incredible, transformative technology that abstracts a lot of the technical details away, but that doesn't mean the technology leadership on any project shouldn't be aware of them.

Colin Campbell is Chief Executive Officer and a Principal Consultant with Stratosphere Consulting. Stratosphere specializes in the development of Centers of Excellence for Pega practices, staff training and enablement programs, and the design and implementation of Pega applications in both the public and private sector.