How to replace a distributed transaction with Message Queuing

Because of the large amount of data, most Web applications need to deploy many database instances. In this way, some user actions may need to modify the data in multiple database instances. The traditional solution is to use distributed transactions to ensure the global consistency of data, the classic approach is to use the two-phase commit protocol.

For a long time, the elegant global acid that distributed transactions provide ensures that the application developer's mind is anesthetized, and many people are Street to imagine a world without distributed transactions. Now, like MySQL and PostgreSQL, which are open source databases for low-end users that support distributed transactions, developers are addicted to it, regardless of whether distributed transactions are hurting the system.

In fact, the gains must be lost, and the acid guarantees provided by distributed transactions are made at the expense of system availability, performance, and scalability. Distributed transactions can be successfully completed only if each database instance participating in a distributed transaction is working properly, and the entire transaction cannot be completed as long as one is not working properly. In this way, the availability of the system is equal to the availability of the various instances participating in the distributed transaction, and the more instances, the more obvious the availability declines. From a performance and scalability standpoint, the first is that the total duration of the transaction is usually the sum of the instances ' operation times, because the operations in one transaction are usually executed sequentially, the response time for the transaction increases a lot, followed by a small number of transactions for general Web applications and a few milliseconds or less than 1 milliseconds for a single operation. When it comes to distributed transaction, the round-trip process of network communication between points of submission is also millisecond, and the influence on transaction response time can not be neglected. Due to the prolonged transaction duration, transaction locking time for related resources increases correspondingly, which can significantly increase concurrency conflicts, and affect system throughput and scalability.

Because of the above problems in distributed transactions, ebay does not use distributed transactions in design, but rather solves data consistency problems by other means. The most important technology to use is Message Queuing and message application state tables.

Give an example. Suppose the system has the following two tables
User (ID, name, amt_sold, Amt_bought)
Transaction (XID, seller_id, buyer_id, amount)
The user table records the summary information for the users, and the transaction table records the details of each transaction.

In this way, if you are using a transaction when you make a transaction, you need to do the following for the database:
Begin
INSERT into Transaction VALUES (XID, $seller _id, $buyer _id, $amount);
UPDATE user SET Amt_sold = amt_sold + $amount WHERE id = $seller _id;
UPDATE user SET amt_bought = amt_bought + $amount WHERE id = $buyer _id;
Commit
That is, record the transaction information in the Transaction table and update the status of the seller and buyer.

Assuming that the transaction table and the user table are stored on different nodes, the above transaction is a distributed transaction. To eliminate this distributed transaction, split it into two child transactions, one Update transaction table, and one update user table, because it is possible that after the transaction table update succeeds, the update user fails and the system cannot revert to a consistent state.

The solution is to use Message Queuing. As shown below, start a transaction, update the transaction table, not directly update the user table, but will be inserted into the user table updates to the message queue. There is also an asynchronous task that polls the queue content for processing.
Begin
INSERT into Transaction VALUES (XID, $seller _id, $buyer _id, $amount);
Put_to_queue "Update User" ("seller", $seller _id, amount);
Put_to_queue "Update User" ("buyer", $buyer _id, amount);
Commit
For the in queue
Begin
Dequeue message;
If Message.type = "Seller" Then
UPDATE user SET Amt_sold = amt_sold + message.amount WHERE id = message.user_id;
Else
UPDATE user SET amt_bought = amt_bought + message.amount WHERE id = message.user_id;
End
Commit
End

The solution looks perfect, and it doesn't actually solve the distributed problem. For the first transaction to not involve a distributed operation, Message Queuing must use the same set of storage resources as the transaction table, but for the second transaction to be local, Message Queuing storage must be with the user table. Neither of these can be satisfied at the same time.

If the message has a power of operation, that is, a message has been applied multiple times and the effect of the application is the same, the above problem is very good to solve, as long as the message queue into the transaction table, and then in the second transaction, first apply the message, and then delete from the message queue. Because the Message Queuing store is not with the user table, the system fails when the message is applied and may not have been removed from the queue before it can be used. At this point, the system will reapply once the message, due to power, the application can also produce the correct results.

But in practice, messages can be difficult to have power, such as the update operation above, and the end of execution and execution is obviously different. The solution to this problem is to use another table to record messages that have been successfully applied, and this table uses the same storage as the user table. Assuming that adding the following table Message_applied (msg_id) records the messages that were successfully applied, the resulting solution is the following:
Begin;
INSERT into Transaction VALUES (XID, $seller _id, $buyer _id, $amount);
Put_to_queue Update User ("seller", $seller _id, amount);
Put_to_queue Update User ("buyer", $buyer _id, amount);
Commit;
for all in queue
begin;
SELECT Count (*) as CNT from message_applied WHERE msg_id = message.id;
If cnt = 0 Then
if message.type = "Seller" then
UPDATE user SET amt_sold = amt_sold + message.amount WHERE ID = message.user_id;
Else
UPDATE user SET amt_bought = amt_bought + message.amount WHERE id = message.user_id;
End
Inserts into Message_applied VALUES (message.id);
End
Commit;
If above transaction succeeded
Dequeue message
DELETE from message_applied WHERE msg_id = message.id;
end< br> End

Let's analyze This carefully:
1, Message Queuing and transaction use the same instance, so the first transaction does not involve a distributed operation;
2, message_applied and user table in the same instance, can also ensure consistency;
3, after the end of the second transaction, Dequeue message before the system may fail, after the failure of the system will be removed from the message queue, but through the message_applied table can be checked out this message has been applied, skip this message to achieve the correct behavior;
4, the final will have been successfully applied, and has been deleted from the message queue from the Message_applied table deleted, you can message_applied table to ensure in a very small state (not clear is also possible, does not affect system correctness). Because message queues and message_applied on different instances, dequeue messages, the corresponding message_applied records may fail before they are deleted. Once the failure occurs, the Message_applied table will leave some rubbish content, but does not affect the system correctness, in addition these rubbish content also can clean correctly.

Although there is no strong consistency guarantee for distributed transactions, the system will be in inconsistent condition for a short time when the system fails. However, based on Message Queuing and message application state tables, the system can eventually be restored to the same. With the Message Queuing scenario, the tight coupling between two database instances is lifted, and the performance and scalability of the distributed transaction are unmatched.

Of course, using distributed transactions can help simplify application development, and using Message Queuing clearly requires more effort, both of which have their pros and cons. The personal view is that for systems with time constraints or low performance requirements, distributed transactions should be used to accelerate development efficiency, and for systems with high performance requirements, the use of Message Queuing schemes should be considered. For systems that use distributed transactions, which are stable and require high performance, the Message Queuing scheme can be used for refactoring to optimize performance.

Note: This article is based on the paper written by the ebay engineer Dan Pritchet.