Learn Vitess - Transaction & Consistency Models

#Introduction

Finance, e-commerce, and booking applications depend on transactions: sequences of operations that must all succeed or all fail. Episode 9 covers how Vitess handles transactions when data is spread across many shards — an area where scale and ACID guarantees often collide.

Episode 9 roadmap: ACID in the Vitess context, single-shard transactions, cross-shard transactions and XA, locking and stale reads, then consistency best practices. This episode determines how safely you can entrust critical data to Vitess.

#ACID in the Vitess Context

ACID: Atomicity (all or nothing), Consistency (data is valid after a transaction), Isolation (transactions don't interfere with each other), and Durability (data survives crashes). Vitess inherits ACID guarantees from MySQL, but with nuances that depend on the transaction's scope.

The key to understanding it: ACID guarantees in Vitess are fully honored within a single shard. As soon as a transaction spans more than one shard, some guarantees become weaker — and you need to be aware of these trade-offs when designing applications.

#Single-shard Transactions

If all operations in a transaction touch data in the same shard — which is usually guaranteed when all queries use the sharding key column — Vitess provides full ACID guarantees just like regular MySQL. Vitess routes the entire transaction to one VTTablet, and the transaction runs as if directly on MySQL.

BEGIN
INSERT INTO orders (order_id, user_id, amount)
VALUES (1001, 42, 250000)
UPDATE wallet SET balance = balance - 250000
WHERE user_id = 42
COMMIT

Because user_id = 42 and order_id belong to the same user (co-located), the whole transaction above runs in one shard — full ACID guarantees. BEGIN, COMMIT, and ROLLBACK are all supported at VTGate.

#Cross-shard Transactions and XA

What if a transaction touches data in two different shards? Example: transferring funds from a user in shard A to a user in shard B. That's a cross-shard transaction, and this is where the complexity appears.

Vitess handles cross-shard transactions with two approaches:

• 2PC (Two-Phase Commit) / XA: Vitess coordinates the commit across both shards through a prepare and commit process. This gives true atomicity, but with latency overhead and transaction state stored in the topology. 2PC support in Vitess is optional and must be enabled per keyspace.
• Without 2PC: operations run on the shards independently. Global atomicity isn't guaranteed — one shard might succeed while another fails. This approach is fast, but the application must handle partial failures itself (e.g., via compensation or saga patterns).

vtctlclient SetKeyspaceDurabilityPolicy \
  -durability-policy=semi_sync commerce

The vtctlclient SetKeyspaceDurabilityPolicy command sets the durability policy, part of the configuration that affects how transactions and failover maintain consistency.

#Locking and Stale Reads

#Locking

MySQL uses locking for isolation: row locks prevent two transactions from writing the same row concurrently. In single-shard Vitess, locking works like MySQL. In cross-shard scenarios, locking can't atomically lock data across two different MySQL instances — which is again why co-located design is so strongly recommended.

SELECT ... FOR UPDATE is supported and useful for maintaining consistency during staged updates. Because VTGate forwards it to the right shard, make sure this query uses the sharding key column so it locks the correct rows in one shard.

#Stale Reads and Read Replicas

Reading from a replica can produce a stale read — data that hasn't fully replicated from the primary. Vitess gives this control to the application:

• Read-write split: reads can be directed to replicas, with a selectable replica_consistency.
• For reads that must always be fresh, use shard_consistency=strong or make sure reads go through the primary.

SELECT balance FROM wallet WHERE user_id = 42

The SELECT balance query above runs through VTGate. Based on the configured policy, VTGate decides whether this read goes to the primary or a replica — the application doesn't need to know the details.

#Isolation Levels and Their Impact

MySQL provides several isolation levels, and this choice affects transaction behavior in Vitess:

• REPEATABLE READ — MySQL's default. Data read stays consistent within a transaction; suitable for most workloads.
• READ COMMITTED — only already committed data is visible; reduces lock contention.
• READ UNCOMMITTED — reads uncommitted data; fastest but must not be used for important data.
• SERIALIZABLE — the strictest guarantee at the cost of the most locking.

SELECT @@transaction_isolation

SELECT @@transaction_isolation shows the current session isolation level. In Vitess, most workloads use the default REPEATABLE READ — and because the strongest guarantees only hold within a single shard, looser isolation levels offer no real benefit for cross-shard transactions.

#Consistency Best Practices

• Co-locate data written together. Design the sharding key so related transactions land in the same shard.
• Use the sharding key column in all transactional queries. This keeps transactions single-shard and gives full ACID guarantees.
• Limit cross-shard transactions. If they're unavoidable, understand the trade-off: 2PC for atomicity at latency cost, or no 2PC with application-level compensation.
• Choose the read path deliberately. Reads through replicas can be stale; pick the consistency level that matches business needs.
• Test failures. Simulate transactions failing midway and make sure the application handles partial failure correctly.

#Closing

In this episode 9 you understood how ACID applies in Vitess: fully within one shard, weaker when crossing shards. You also learned about cross-shard transactions with the 2PC/XA option and its trade-offs, locking and stale reads on read replicas, and best practices for maintaining consistency.

Key takeaways:

• Full ACID in Vitess applies within one shard; beyond that, guarantees degrade.
• Single-shard transactions are the primary design target: always use the sharding key column.
• Cross-shard transactions need 2PC for atomicity, with latency overhead.
• Without 2PC, the application must handle partial failures itself.
• Reads from replicas can be stale; choose the consistency level to match needs.
• "One transaction, one shard" is the most powerful design principle in Vitess.

In the next episode, episode 10, we handle safe schema changes: schema changes and online migrations — VReplication for data migration, zero-downtime online schema changes, and the vtctlclient workflow. See you there!