Hi Pythonistas!
You know database transactions.Either both happen.Or neither.
ACID guarantees it.
Simple.
Now imagine:
User A's account is in Bank A.
User B's account is in Bank B.
Two different databases.
Two different machines.
You want to transfer $100.
Bank A: deduct $100 from User A
Bank B: add $100 to User B
You can't wrap these in one transaction.
Different databases.
This is the distributed transaction problem.
A Simpler Example
You're ordering food online.
Three things must happen:
- Create your order (Order database)
- Reserve the food (Restaurant database)
- Charge your card (Payment database)
Three separate systems.
All must succeed together.
Or all must fail together.
What Goes Wrong Without Coordination
No coordination:
Step 1: Create order ✅
Step 2: Reserve food ✅
Step 3: Charge card ❌ card declined
Now:
order exists.
food reserved.
money not taken.
Restaurant thinks order is coming.
You think it failed.
Food wasted.
Inconsistent state.
Two-Phase Commit - The Idea
2PC tries to solve this.
Think of it like a wedding ceremony.
Priest asks everyone:
"Do you agree?"
Everyone says yes.
Priest says: "It's done."
Anyone says no:
"We're not doing this."
Everything cancelled.
The Two Phases
Phase 1 - Ask Everyone
A coordinator asks all services:
"Are you ready?"
Coordinator → Order Service: "Ready to create order?"
Coordinator → Restaurant: "Ready to reserve food?"
Coordinator → Payment Service: "Ready to charge card?"
Each service:
prepares everything.
locks the resources.
doesn't execute yet.
replies YES or NO.
Order Service → YES ✅
Restaurant → YES ✅
Payment Service → YES ✅
Phase 2 - Tell Everyone to Go
All said YES?
Coordinator sends COMMIT:
Coordinator → Order Service: "DO IT"
Coordinator → Restaurant: "DO IT"
Coordinator → Payment Service: "DO IT"
All execute.
Done.
What If Someone Says No?
Payment Service says NO:
Order Service → YES ✅
Restaurant → YES ✅
Payment Service → NO ❌
Coordinator sends ROLLBACK:
Coordinator → Order Service: "CANCEL"
Coordinator → Restaurant: "CANCEL"
Coordinator → Payment Service: "CANCEL"
All undo preparations.
System back to original state.
Nothing happened.
Visual
All say YES → COMMIT:
Coordinator
├──→ Order Service: "Ready?" → "YES"
├──→ Restaurant: "Ready?" → "YES"
└──→ Payment Service: "Ready?" → "YES"
↓
Coordinator
├──→ Order Service: "COMMIT"
├──→ Restaurant: "COMMIT"
└──→ Payment Service: "COMMIT"Result: everything done ✅
Someone says NO → ROLLBACK:
Coordinator
├──→ Order Service: "Ready?" → "YES"
├──→ Restaurant: "Ready?" → "YES"
└──→ Payment Service: "Ready?" → "NO"
↓
Coordinator
├──→ Order Service: "ROLLBACK"
├──→ Restaurant: "ROLLBACK"
└──→ Payment Service: "ROLLBACK"Result: nothing happened ✅
Who Is the Coordinator?
Not a magic box.
Not a special system.
Just code.
Usually the service that starts the operation.
class OrderService:
def place_order(self, user_id, items, card):
transaction_id = str(uuid.uuid4())
# save to database in case of crash
self.save_transaction(transaction_id, 'started')
# Phase 1 — prepare
try:
self.prepare(transaction_id, user_id, items)
inventory_service.prepare(transaction_id, items)
payment_service.prepare(transaction_id, card, amount=100)
except Exception:
# someone failed → rollback all
self.rollback(transaction_id)
inventory_service.rollback(transaction_id)
payment_service.rollback(transaction_id)
self.save_transaction(transaction_id, 'rolled_back')
raise
# Phase 2 — commit
self.save_transaction(transaction_id, 'decided_commit')
self.commit(transaction_id)
inventory_service.commit(transaction_id)
payment_service.commit(transaction_id)
self.save_transaction(transaction_id, 'committed')
return {'status': 'success'}Or a dedicated coordinator service:
class TransactionCoordinator:
def place_order(self, order_data):
transaction_id = str(uuid.uuid4())
self.save_transaction(transaction_id, 'started')
prepared = {}
try:
prepared['order'] = order_service.prepare(
transaction_id, order_data
)
prepared['inventory'] = inventory_service.prepare(
transaction_id, order_data['items']
)
prepared['payment'] = payment_service.prepare(
transaction_id, order_data['card']
)
except Exception:
self.rollback_all(transaction_id, prepared)
raise
self.save_transaction(transaction_id, 'decided_commit')
order_service.commit(transaction_id)
inventory_service.commit(transaction_id)
payment_service.commit(transaction_id)
self.save_transaction(transaction_id, 'committed')
return {'status': 'success'}What Each Participant Looks Like
Each service has three methods:
class InventoryService:
def prepare(self, transaction_id, items):
for item in items:
stock = db.get_stock(item['id'])
if stock < item['quantity']:
raise InsufficientStockError
# lock items (don't deduct yet)
db.execute("""
INSERT INTO reservations
(transaction_id, item_id, quantity, status)
VALUES (%s, %s, %s, 'prepared')
""", (transaction_id, item['id'], item['quantity']))
return 'YES'
def commit(self, transaction_id):
# actually deduct stock
reservations = db.get_reservations(transaction_id)
for r in reservations:
db.execute("""
UPDATE inventory
SET stock = stock - %s
WHERE item_id = %s
""", (r['quantity'], r['item_id']))
db.update_reservation_status(transaction_id, 'committed')
def rollback(self, transaction_id):
# release reservation
db.update_reservation_status(
transaction_id, 'rolled_back'
)Does the Database Support This?
Yes.
Databases have built-in 2PC support.
PostgreSQL: PREPARE TRANSACTION
MySQL: XA Transactions
Database support:
PostgreSQL → PREPARE TRANSACTION ✅ excellent
MySQL → XA protocol ✅ good
Oracle → XA protocol ✅ excellent
SQLite → not supported ❌
MongoDB → limited ⚠️
Cassandra → not supported ❌
Redis → not supported ❌
NoSQL databases generally don't support 2PC.
They're built for availability.
Remember CAP theorem (Post 12).
The Transaction Log
Critical.
Coordinator writes decision to database BEFORE sending to participants.
def commit_all(self, transaction_id):
# write decision FIRST
db.execute("""
UPDATE transactions
SET status = 'decided_commit'
WHERE id = %s
""", transaction_id)
# THEN send to participants
order_service.commit(transaction_id)
inventory_service.commit(transaction_id)
payment_service.commit(transaction_id)
db.execute("""
UPDATE transactions
SET status = 'fully_committed'
WHERE id = %s
""", transaction_id)Why?
If coordinator crashes between writing log and sending commits:
on recovery:
def recover(self):
incomplete = db.query("""
SELECT * FROM transactions
WHERE status = 'decided_commit'
""")
for tx in incomplete:
# resume sending commits
order_service.commit(tx['id'])
inventory_service.commit(tx['id'])
payment_service.commit(tx['id'])Log = coordinator's memory.
Survives crashes.
What If Coordinator Crashes?
The biggest weakness of 2PC.
Phase 1 done.
All said YES.
All resources locked.
Coordinator crashes before sending COMMIT.
Order Service: locked, waiting... ⏳
Restaurant: locked, waiting... ⏳
Payment Service: locked, waiting... ⏳
They don't know:
did coordinator decide COMMIT?
or ROLLBACK?
They just wait.
Resources locked.
Nothing else can use them.
This is called blocking.
How Long Do They Wait?
Depends on timeout:
After 30 seconds with no response:
→ transaction marked "in-doubt"
→ resources stay locked
→ wait for coordinator to recover
When coordinator recovers:
reads transaction log.
sees: "decided_commit for tx_123."
sends COMMIT to all.
Services complete.
Unlock resources.
But:
during downtime → everything locked.
No other transactions could use those resources.
What If Coordinator Never Recovers?
Manual intervention needed.
DBA looks at each service.
Decides manually.
-- PostgreSQL: view in-doubt
SELECT * FROM pg_prepared_transactions;
-- force commit
COMMIT PREPARED 'tx_order_123';
-- force rollback
ROLLBACK PREPARED 'tx_order_123';Painful.
Error-prone.
This is called an in-doubt transaction.
The Problems - Simply
Problem 1: Blocking
If coordinator crashes → all services locked waiting
Nothing else can proceed
System stuck
Problem 2: Coordinator is single point of failure
Coordinator down → transaction stalled
Problem 3: Slow
Two round trips for every operation
All services wait during both rounds
High latency
Problem 4: Availability decreases
3 services each 99% available:
99% × 99% × 99% = 97%
More services → lower availability
One service down → entire transaction fails
When to Use 2PC
Despite problems 2PC is used.
Specific cases:
Same database vendor:
PostgreSQL → PostgreSQL (different servers)
Both understand same protocol
Built-in support
Financial systems:
Bank transfers between branches
Strong consistency non-negotiable
Slow is acceptable
Short transactions:
In and out quickly
Locks not held long
Less blocking risk
When NOT to use:
Many services → availability drops too much
Long operations → locks held too long
High traffic → locks cause contention
General microservices → use Saga instead
Mental Model
Distributed transaction → operation across multiple databases
Coordinator → service that starts and manages it
Participants → services being coordinated
Phase 1 → prepare, lock, say YES or NO
Phase 2 → COMMIT (all YES) or ROLLBACK (any NO)
Transaction log → coordinator's decision saved to DB
Blocking → services stuck if coordinator crashes
In-doubt → transaction neither committed nor rolled back
PREPARE TRANSACTION → PostgreSQL Phase 1 command
XA → industry standard 2PC protocol
Manual resolution → DBA force commits/rollbacks in-doubt TX
What's Coming Next
2PC tries to make distributed transactions
feel like single database transactions.
Strict. Synchronous. Blocking.
But modern systems need something different.
More available.
Less blocking.
More resilient.
Saga Pattern.
A completely different approach.
Accept that things will fail.
Design for failure upfront.
Each step can undo itself.
No locks. No blocking.
See you in next post
