Inside UnionPay’s Distributed MySQL Middleware: Architecture, Transactions & SQL Optimizations

Background

UnionPay began investing in the open‑source MySQL ecosystem in 2014 to achieve autonomous control and replace legacy IOE systems. The goal was to build a scalable, high‑availability distributed database that could handle massive data volumes and high performance.

Architecture Overview

The initial architecture consists of three layers: the bottom layer is the UPSQL data node providing logical high‑availability; the middle layer is the UPSQL Proxy , typically deployed in pairs or more for redundancy; the top layer is the client side, which balances connections across data nodes and proxies. An adm‑server component collaborates with the data nodes and proxies to perform health checks, failover decisions, and proxy isolation.

Distributed Transaction Mechanism

UnionPay evaluated two industry approaches—Google Spanner‑style and Proxy‑based solutions. Both ultimately rely on a two‑phase commit (2PC) protocol. UnionPay’s implementation stores XA logs in a backend database to avoid external dependencies and adopts a "final participant" strategy to reduce the number of 2PC phases.

In the generic scheme, each participating shard writes an XA log during the commit phase, and a coordinator records a distributed log before completing the XA commit. UnionPay’s variant selects one shard (e.g., data‑shard 1) as the final participant, eliminating the need for a separate XA session and commit, thereby simplifying the protocol.

Advantages and Drawbacks

Advantages :

No dedicated XA session for log writing.

One fewer 2PC round‑trip, reducing latency.

Drawbacks :

All backend users must have permission to the XA log table, introducing operational risk.

The scheme fits public‑cloud products better, while UnionPay’s approach requires strict operational controls.

Deadlock Detection and Prevention

Distributed deadlocks arise when global transactions interleave writes to different shards in opposite order, creating circular wait conditions. UnionPay addresses detection by extending the innodb_trx table with an XAID column, treating XAID as a global transaction identifier. By scanning this table, a conflict‑graph can be built to spot deadlocks.

Prevention uses a timestamp‑ordering (TO) strategy: the XAID generation incorporates a logical timestamp (transaction start time + proxy ID + node ID, etc.), ensuring a total order of lock acquisition. The DeadlockChecker::search() routine reads the XAID and applies the TO rule to avoid deadlocks.

SQL Parsing Optimization

Proxy processing incurs significant cost in SQL parsing and network I/O. Two techniques reduce parsing overhead:

Prepare : Parse once, then reuse a statement ID with bound variables, eliminating repeated parsing.

Soft parsing : Reuse previously built syntax trees for similar statements.

UnionPay further introduced a similarity optimization . After lexical analysis, if two statements produce similar token sequences (same token types and, for non‑parameter tokens, identical values ignoring case), the existing syntax tree can be reused. A custom similarity hash algorithm accelerates this lookup.

Implementation required extending the LEX_STRING structure with an index field (position in lexical stream) and a next_lex_string field (link to the subsequent token).

Complex Query Handling

To support cross‑shard joins and other complex queries, UnionPay explored two directions:

Enhance the Proxy layer to generate distributed execution plans—high cost and risky due to incomplete parsing knowledge.

Leverage distributed storage engines (e.g., MariaDB Spider, MySQL NDB). Spider offers completeness but limited performance scaling; NDB lacks sufficient production experience.

UnionPay’s chosen solution combines a Federated+Proxy approach: a Federated engine forwards queries to UPSQL Proxy, which handles data sharding and transaction management, presenting a single logical table to the client.

Additionally, a statement classification routing mechanism separates OLAP (complex) and OLTP (simple) statements, directing each to the appropriate execution path while sharing a common transaction manager.

The architecture also introduces an XProxy co‑processor. When a complex statement arrives, XProxy parses and classifies it; if complex, it forwards the statement to a MySQL server for decomposition into simpler statements, which are then routed back through the Proxy. This adds latency but enables handling of sophisticated queries.

Deployment Models

Two deployment options are considered:

Partner deployment : Co‑locate UPSQL, Proxy, and MySQL on the same host, simplifying management at the cost of extra resources.

Cluster deployment : Deploy XProxy on data nodes, allowing resource reuse across the cluster. This model improves scalability but requires careful handling of Proxy‑to‑Proxy communication and failover.

Further Optimizations and Limitations

Additional performance enhancements (lower priority for production) include:

Index Condition Pushdown (ICP)

Engine Condition Pushdown (ECP)

However, the solution performs poorly in scenarios such as pagination without filters (requiring storage‑engine interfaces) and heavy statistical aggregations, where approaches like MariaDB Spider’s MapReduce may be more suitable.