Transaction Scope and Application Complexity

Relational Transactions
Relational databases provide multi-row and multi-table ACID transactions (Atomicity: all or nothing; Consistency: valid state; Isolation: no interference; Durability: persisted). The database enforces constraints like uniqueness or referential integrity (foreign keys point to existing records). Transferring money between accounts is a single transaction: either both debit and credit succeed, or both roll back. Application code stays simple because the database handles concurrency. However, long-running transactions cause lock contention (multiple operations waiting for the same rows) and can trigger deadlocks (two transactions each waiting for locks the other holds).
NoSQL Transaction Scope
NoSQL systems limit atomicity to a single aggregate (a self-contained unit like a user profile or order with its line items). Cross-aggregate operations require application-level coordination. The saga pattern breaks a workflow into steps, each with a compensating action. Example: (1) reserve inventory, (2) charge payment, (3) schedule shipping. If shipping fails, compensating actions refund payment and release inventory. The outbox pattern ensures reliable event publishing: write the event to an outbox table in the same transaction as the data change, then a separate process reads and publishes events.
"Relational databases let you write less coordination code but hit scaling walls. NoSQL pushes coordination into application code but scales linearly."
When to Choose
Use relational when you need strict correctness with multi-entity invariants: financial transactions, inventory with hard constraints, regulatory audit trails. Use NoSQL when transactions stay within a single aggregate and you implement eventual consistency for cross-aggregate coordination: session state, activity feeds, telemetry.

💡 Key Takeaways

✓Relational ACID transactions handle multi-row invariants centrally, simplifying application code but risking lock contention on hot rows

✓NoSQL limits atomicity to single partitions; cross-partition workflows use sagas (compensating actions) or the outbox pattern (events in same transaction)

✓The saga pattern: break workflow into steps, each with an undo action; if step N fails, run compensating actions for steps N-1 through 1

✓Choose relational when invariants span multiple entities; choose NoSQL when transactions can stay within a single partition

📌 Interview Tips

1When designing payment systems, explain why relational is preferred: a debit and credit must atomically succeed or fail together

2Describe the outbox pattern concisely: write event to outbox table in same transaction as data change, separate process publishes reliably

3For saga design questions, mention idempotency: each step must be safely retryable, typically using unique operation IDs to detect duplicates

← Back to Relational vs NoSQL Overview