Why Replace Distributed Transactions? A Message Queue and State‑Table Solution

When a system’s data volume grows, databases are sharded across multiple instances, making it inevitable that some operations must modify several databases simultaneously. Developers often rely on distributed transactions, typically using the classic two‑phase commit protocol, to ensure data accuracy and consistency.

While distributed transactions simplify development and speed up delivery for time‑critical, low‑performance‑requirement systems, they also introduce serious drawbacks: the overall availability becomes the product of the availabilities of all participating instances, and the sequential nature of the protocol inflates response times, reduces concurrency, and hampers scalability, especially for high‑throughput OLTP workloads.

To avoid these issues, the article presents a pattern that replaces distributed transactions with a message queue and a state table. The approach consists of two logical transactions:

Begin; Update user1 set account = account + $b; Put_queue user2; Put_queue user3; Commit;

and a second transaction that processes each queued message, checks an idempotent message_state table, applies the update to the appropriate read‑only replica, and records the message as processed.

The processing logic (simplified) is:

For each message in queue Begin If (routedb = 'db2') then Begin Select count(1) cnt from message_state where msg_id = $messageid; If (cnt = 0) then Update user2 set account = account + $b; Insert into message_state values($messageid); End; Elseif (routedb = 'db3') then Begin Select count(1) cnt from message_state where msg_id = $messageid; If (cnt = 0) then Update user3 set account = account + $b; Insert into message_state values($messageid); End; End; Commit; End;

This design eliminates cross‑instance transactions; the first step only touches the primary database, while subsequent updates are performed asynchronously via the queue. The state table guarantees idempotency, preventing duplicate updates even if failures occur during processing.

Although the solution cannot guarantee instantaneous consistency—there may be a short window of inconsistency after a failure—it ensures eventual consistency and allows the system to recover without tight coupling between database instances. Performance and scalability become comparable to, or better than, traditional distributed transactions.

In summary, for systems where development speed is less critical than performance and scalability, replacing distributed transactions with a message‑queue‑plus‑state‑table pattern provides a more resilient and scalable architecture.