2PC or not 2PC, Wherefore Art Thou XA?

Friends, architects, engineers, lend me your ears; I come here not to bury distributed transactions, not to praise them. Now that I've thoroughly mixed and paraphrased the Bard, maybe I should get to the point of this post. It comes as a shock to many, but distributed transactions are the bane of high performance and high availability.

But, I need to insure data is committed across multiple databases? How can I possibly do that? Well, there are really two answers to that problem. The first is, most of the time you don't need the data committed. If you've determined you absolutely do, there's a better way to insure it happens.

And now you're thinking, "What do you mean I don't need it to occur within a transaction? How do you know that?"

Most transactional use cases that perform updates to multiple data sources will be manipulating data of differing importance. There is the primary data that is the point of the use case. For example, in a blog application, capturing the article is the primary data. But there is also secondary data. Perhaps the user has changed a setting and selected "Make Default" indicating they would like this new setting to be reflected in their preferences. I will assert that you should make every effort to insure that the article makes it to the database and inform the user if it doesn't. But what if the preference can't be committed because the data source is down? Do you tell the user you can't accept their article? Or would you rather commit the article but let them know the preference couldn't be saved at this time? If it was your article that you just spent 30 minutes crafting, how would you want the application to behave?

But what if you really do need to update multiple databases. You have information that must be captured. Surely distributed transactions are the only safe way to do this. And why would you not want to leverage them anyway? Well, one reason is that while distributed transactions are an easy way to insure data integrity, they are a terrible way to insure high performance and availability.

How does 2PC impact scalability? Well, as the name suggests, transactions are committed in two phases. This involves communicating with every database involved to determine if the transaction will commit in the first phase. During the second phase each database is asked to complete the commit. While all of this coordination is going on, locks in all of the data sources are being held. The longer duration locks create the risk of higher contention. Additionally, the two phases require more database processing time than a single phase commit. The result is lower overall TPS in the system.

More insidious though is the impact on availability. First, a simple lesson on availability. A system's availability will be equal to the product of each component's availability. Using 2PC will reduce the availability of an application to the product of the availability of the databases involved. For example, if an application depends upon two databases and each database has an availability of 99.5%,, the application will have no better than a 99% availability. Add a third dependency, and the availability drops to 98.5%. These numbers may be okay for certain types of applications but commerce typically requires substantially better than 99%. The 2PC coordinator also represents a SPOF (Single Point of Failure), which shouldn't be present in any highly available design.

So what can you do? Design for failure and let asynchronous recovery save the day. An approach that works very well is to use single database commits on each of the databases. These will succeed, independently, 99.5% of the time. For the 0.5% of the time they fail, capture the operation in a journal. There are a couple of options for what this journal might be:

• In many cases, the contents of one database can be derived from another. In this case, do nothing. The asynchronous process described below will take care of everything as long as you order writes to complete the parent data first.
• If there is no option for deriving the data, then it has to be queued in a separate fail over database. This works very well for insert operations but less so for updates. Designing for failure might cause you to rethink critical operations to make them more friendly to this style of journaling.

Asynchronous recovery is easiest to build if all operations can be made idempotent. This simplifies the recovery process and is usually not too difficult to accommodate in the database design. The recovery agent would immediately after a database is brought back online.

There is little argument that this approach is more complex than relying on XA. The gains are significant however. Each database operation is a single phase commit which is not only more scalable but also easier to debug, even in production. Decoupling the databases involved increases application availability. Designing for failures by introducing concepts like fail over journals will further increase the effective availability of your application to your customers.

Is it worth it? That's a business question, but in many cases the availability is needed and the approach I've described is the most reliable way to achieve it.

Technorati Tags: architecture, asynchronous, engineering, events, messaging, performance, programming, scalability, software, to_read, toread

Comments

[Noah Campbell]

It sounds like you're defining a queue that captures the initial requests and then processes the request in order of priority. Failures are logged to a failure response list. At each transaction stage, you have a response of the success of the operation.

If I can generalize a little further, you're really talking about a best effort commit with a feedback channel to describe the success or failure of multiple operations. If it's a web application this comes in the form of UI feedback. If this is a MOM then you you have failure queues (assuming success is not reported) for batch processing.

2PC is definitely a heavy weight operation, but if the requests can be handle asynchronously then the overhead may be worth the simplicity.