Senior LevelSQL & MySQL
How do 2PC (Two-Phase Commit) and Saga Patterns coordinate transactions across sharded SQL databases?
❓ Interview Prompt: Explain Cross-Shard Transactions, Two-Phase Commit (2PC) over database shards, Distributed Transaction Coordinators, and eventual consistency Sagas.
When an operation requires updating records located on two separate database shards (e.g. transferring money from User on Shard 1 to User on Shard 2), standard single-node transactions fail:
2 Resolution Approaches:
1
Distributed 2PC (Two-Phase Commit): Database coordinator issues Prepare and Commit commands across shards. Cons: Holds locks across network connections; high latency.
2
Application Saga Pattern: Executes local transactions on each shard sequentially, publishing events to trigger compensating rollbacks if Shard 2 fails. Recommended for high-throughput microservices!
Code Implementation Example
json
{
"transactionType": "Cross-Shard Transaction",
"sourceShard": "shard-01.db.internal",
"targetShard": "shard-04.db.internal",
"coordinationStrategy": "Application-Level Saga Pattern",
"compensatingAction": "Revert Shard 1 Local Transfer"
}💡Cross-shard transaction coordination architecture payload.
🎯 Interview Key Takeaway
Avoid cross-shard transactions when possible; use application-level Saga patterns when multi-shard updates are unavoidable.
#SQL#Cross-Shard#2PC#Sharding#Distributed DB#Senior
Back to all SQL & MySQL Questions→