Transaction Isolation Across Systems
What you'll learn: Why keeping transactions truly isolated becomes extraordinarily difficult when they span different database systems.
The Core Challenge
In a single database, transaction isolation (like serializable isolation) ensures that concurrent transactions don't interfere with each other—they appear to execute one at a time. But when a distributed transaction involves multiple different databases (say, MySQL and PostgreSQL, or a SQL database and a key-value store), maintaining this isolation becomes a coordination nightmare.
Why Different Systems Make It Harder
Each database system has its own:
- Locking mechanisms (pessimistic vs optimistic)
- Isolation level implementations (what "serializable" actually means varies)
- Version control strategies (MVCC implementations differ)
- Lock timeout policies (one system might abort while another waits)
Even if both systems claim to support serializable isolation, they might detect conflicts differently or at different times. The coordinator (from 2PC) manages commit/abort, but it doesn't manage the ongoing isolation during transaction execution—each participant does that independently.
The Coordination Gap
Imagine two participants in a distributed transaction: one uses timestamp ordering, the other uses two-phase locking. A transaction that would be serializable in either system alone might produce non-serializable behavior when operations are interleaved across both systems. There's no global lock manager or global timestamp authority coordinating their isolation decisions.
This mismatch can lead to anomalies like write skew or lost updates that neither system would allow individually—but the combination permits.
Key Takeaway: Transaction isolation across heterogeneous systems requires every participant to not only support the same isolation level in name, but to coordinate their concurrency control mechanisms—something standard distributed transaction protocols don't guarantee.