Multi-phase transactions: implementing fault-tolerant operations in DBMS

Building Resilient Transactions: Idempotent Operations in Practice

Multi-phase transactions ensure data integrity during failures using idempotent operations and a state log. This approach works with any database, including MongoDB, and prevents data loss from power outages or system crashes.

How Multi-Phase Transactions Work

Transactions break into sequential stages, each logged in a separate TRANSACTIONS table. Key statuses:

• blocking — locking accounts to prevent concurrent operations.
• save_balance — snapshotting original balances in the log.
• main — executing the core funds transfer.
• unlock — releasing account locks after completion.
• finished — final state for successful transactions.

Idempotency ensures repeating a stage after a failure won't corrupt data. For example, A := B + 1 is idempotent, while A := A + 1 isn't—but it can be broken into two idempotent steps.

Implementation Steps with Code Examples

Let's transfer $5 from Assange's account to Agent Y's. Starting data in the ACCOUNTS table:

| ID | NAME | BALANCE | TR_ID |

|----|----------|---------|-------|

| 34 | Assange | 300 | null |

| 78 | Agent Y | 7 | null |

1. Lock Accounts (STATUS='blocking')

First, create a TRANSACTIONS record:

| ID | FROM_ID | TO_ID | AMOUNT | FROM_BALANCE | TO_BALANCE | STATUS |

|----|---------|-------|--------|--------------|------------|----------|

| 172| 34 | 78 | 250 | null | null | blocking |

Locking uses atomic commands, e.g., in MongoDB:

db.accounts.updateOne(
  { ID: 34, TR_ID: null },
  { $set: { TR_ID: 172 } }
);

If no document updates, the lock failed—retry or abort. Sort IDs beforehand to avoid deadlocks.

2. Snapshot Balances (STATUS='save_balance')

After locking, copy original balances to TRANSACTIONS—idempotent by design:

db.transactions.updateOne(
  { ID: 172 },
  { $set: { FROM_BALANCE: 300, TO_BALANCE: 7, STATUS: 'save_balance' } }
);

3. Execute Transfer (STATUS='main')

Core operation uses saved balances for idempotency:

db.accounts.updateOne(
  { ID: 34, TR_ID: 172 },
  { $set: { BALANCE: 50 } } // 300 - 250
);

db.accounts.updateOne(
  { ID: 78, TR_ID: 172 },
  { $set: { BALANCE: 257 } } // 7 + 250
);

Update status to 'unlock' only after changes apply.

4. Unlock and Finish (STATUS='unlock')

Release locks with TR_ID check:

db.accounts.updateOne(
  { ID: 34, TR_ID: 172 },
  { $set: { TR_ID: null } }
);

After unlocking both, set status to 'finished'.

Benefits and Limitations

Key advantages:

• Fault tolerance: Recovers from any failure without data loss.
• Idempotency: Safely retry operations, vital for distributed systems.
• Versatility: Works across databases, not just MongoDB.
• Consistency: Maintains data integrity at every stage.
• Performance: Extra logging may slow things down.

Drawbacks include added complexity and journal overhead. For financial apps or mission-critical systems, it's worth it.

Developer Tips

• Leverage your DB's atomic ops for locks and updates.
• Build retry logic for failed locks.
• Test crash scenarios, like sudden process kills.
• Sort IDs to cut deadlock risks.

This delivers 2PC-level reliability with NoSQL flexibility.

— Editorial Team