Each test is 5 questions with varying difficulty.
AI Prep covers AI Agents, Generative AI, ML Fundamentals, NLP & LLMs and a lot more, with adaptive tests and daily challenges. Fully offline on Android. Free to try, one-time unlock for lifetime access.
Database transactions form the foundational contract of data integrity across modern software architectures, dictating how operations are grouped, isolated, and persisted. In technical interviews for backend, systems, and database engineering roles, mastering the dichotomy between ACID (Atomicity, Consistency, Isolation, Durability) and BASE (Basically Available, Soft state, Eventual consistency) models is a critical differentiator. As engineering systems scale across distributed clouds in 2026, the rigid guarantees of traditional relational databases frequently collide with the high availability and partition tolerance requirements of massive distributed stores. Interviewers probe this tension to evaluate a candidate's grasp of trade-offs, underlying storage engine mechanics, and failure handling under network partitions. Junior engineers are typically expected to define the four ACID properties and list isolation levels, whereas senior and staff engineers must articulate the implications of the CAP theorem, design distributed transactions using sagas or two-phase commit protocols, and diagnose race conditions or split-brain scenarios in globally replicated databases. This preparation page provides a thorough technical roadmap to navigate these core concepts, design patterns, and architectural trade-offs.
In high-throughput enterprise systems, choosing the correct transaction model directly impacts business revenue, data correctness, and system availability. Financial ledgers and inventory management systems demand strict ACID compliance to prevent double-spending or overselling, where a single lost update can result in massive financial liabilities. Conversely, large-scale social media platforms, global content delivery networks, and multi-region telemetry ingestion pipelines require BASE models to maintain sub-millisecond write latencies and high availability despite network partitions or node failures. Real-world engineering failures frequently stem from misjudging these trade-offs—such as assuming a distributed NoSQL store provides immediate serializability or failing to handle retry storms in eventually consistent environments. In technical interviews, this topic serves as a high-signal indicator of engineering maturity. A weak candidate merely regurgitates acronym definitions, whereas a strong candidate analyzes the physical cost of write-ahead logging, explains how Multi-Version Concurrency Control (MVCC) implements snapshot isolation without locks, and navigates the complexities of distributed consensus protocols like Raft or Paxos. Furthermore, with the proliferation of globally distributed NewSQL databases in 2026, engineers must understand how modern engines bridge the gap between ACID guarantees and cloud-scale horizontal elasticity.
The transaction execution pipeline manages concurrency, durability, and coordination between client requests and storage engines. In an ACID relational database, incoming statements pass through parser and query optimizer stages before hitting the concurrency control manager and storage engine. The engine coordinates lock tables, MVCC snapshot generators, and write-ahead logging to guarantee atomicity and isolation. In contrast, BASE and distributed architectures bypass centralized lock managers, relying on asynchronous replication, gossip protocols, and consensus algorithms like Paxos or Raft to achieve eventual consistency across partitioned cluster nodes.
Client requests arrive at the connection pool and are parsed into execution plans. The concurrency control manager assigns a transaction ID and a read view timestamp. Data modifications are written sequentially to the WAL buffer on disk for durability while updating memory buffer pages. Concurrently, replication daemons stream WAL log segments to follower nodes or consensus quorums. Once the WAL write is flushed via fsync, a commit acknowledgment is returned to the client.
Client Request
↓
[Connection Pool]
↓
[Parser & Optimizer]
↓
[Concurrency Manager] ⇄ [Lock Table / MVCC Snapshot]
↓
[Storage Engine Execution]
├──────────────────┐
↓ ↓
[Buffer Pool Pages] [Write-Ahead Log (WAL)]
│ │
└────────┬─────────┘
↓
[Replication / Consensus Quorum (Raft/Paxos)]Coordinates a distributed transaction across multiple database nodes by splitting the commit process into a prepare phase (where all nodes vote and lock resources) and a commit phase (where the coordinator enforces the final outcome). If any node votes abort, all nodes roll back.
Trade-offs: Provides strong ACID consistency across distributed nodes, but is a blocking protocol vulnerable to coordinator failures and network partitions, leading to severe latency degradation.
Decentralized saga pattern where each microservice executes its local transaction and publishes domain events via a message broker (e.g., Kafka). Subsequent services listen to these events and execute their local steps independently without a central controller.
Trade-offs: Maximizes service autonomy and removes single points of failure, but makes tracking overall transaction progress difficult and risks complex event looping if not carefully designed.
Assumes transaction conflicts are rare. Transactions execute without acquiring locks, reading data and buffering changes. At commit time, the system checks if any modified records were altered by another transaction; if so, the transaction aborts and retries.
Trade-offs: Maximizes read throughput and eliminates locking overhead, but causes high retry rates and CPU thrashing under high contention workloads.
Solves the dual-write problem between a database and a message broker by writing business data changes and an outbox event message to the same local ACID database transaction. A separate log tailer or CDC worker reads the outbox table and publishes events to the broker.
Trade-offs: Guarantees reliable at-least-once message delivery without distributed locks, but introduces slight delivery lag and requires idempotent message consumers.
| Reliability | Production transaction systems must handle abrupt node crashes, network partitions, and split-brain scenarios. Relational databases achieve this via mirrored WAL streams and automated failover to standby replicas. Distributed databases rely on consensus quorums (Raft/Paxos) where a majority of nodes must acknowledge state changes before commit. |
| Scalability | Vertical scaling has strict hardware ceilings for ACID databases due to lock manager contention and disk I/O limits. Horizontal scaling requires partitioning (sharding) or adopting BASE architectures with eventual consistency, allowing linear write throughput expansion across multi-region clusters. |
| Performance | Write performance is bounded by WAL disk flush latency (fsync). Read performance depends on buffer pool hit ratios and MVCC snapshot overhead. Distributed transactions add significant tail latency (p99) due to network round-trips for quorum consensus or two-phase commits. |
| Cost | Storage costs scale with data volume, WAL retention policies, and MVCC version bloat. Multi-region synchronous ACID replication incurs high cross-datacenter network bandwidth costs compared to asynchronous BASE replication models. |
| Security | Database transactions must enforce role-based access control (RBAC), row-level security (RLS), and encryption of WAL logs and data-at-rest. Distributed transactions require mTLS encryption for inter-node consensus communication. |
| Monitoring | Key operational metrics include transaction commit latency, rollback rates, deadlocks per second, active connection pool utilization, WAL generation rate, replication lag, and slow query execution times. |
ACID prioritizes strict data consistency, atomicity, isolation, and durability, typically implemented in relational databases with synchronous locking or MVCC. BASE (Basically Available, Soft state, Eventual consistency) trades immediate consistency for high availability and partition tolerance, making it ideal for large-scale distributed NoSQL stores where synchronous cross-datacenter locking is impractical.
The CAP theorem states that a distributed system can guarantee at most two of Consistency, Availability, and Partition Tolerance. Because network partitions are inevitable in real-world infrastructure, systems must choose between CP (Consistency and Partition Tolerance, aligning with ACID guarantees where writes block during partitions) and AP (Availability and Partition Tolerance, aligning with BASE eventual consistency models).
Two-Phase Commit is a blocking protocol vulnerable to coordinator failures and network partitions. If a coordinator crashes during the commit phase, participant nodes remain locked indefinitely, causing cascading availability failures across microservices. Consequently, modern architectures prefer asynchronous patterns like Sagas or outbox workflows.
MVCC creates physical new row versions for every update and deletion. Background vacuum processes reclaim this space only when no active transactions hold read views older than the deleted versions. If long-running transactions remain open, vacuum workers cannot purge obsolete rows, causing table and index bloat that degrades query performance and consumes excessive disk space.
Pessimistic locking assumes conflicts are frequent and acquires exclusive or shared locks on data rows immediately upon reading them, blocking concurrent users. Optimistic Concurrency Control assumes conflicts are rare, allowing transactions to execute without locking. At commit time, the system validates whether data was modified concurrently; if a conflict is detected, the transaction aborts and retries.
Write skew occurs when two concurrent transactions read overlapping sets of data, make independent validation checks based on constraints neither violated individually, and commit mutually exclusive updates that collectively violate a business invariant. This anomaly can occur under Snapshot Isolation and Repeatable Read, but is prevented under full Serializable isolation.
Because distributed sagas avoid global locking across microservices, a failure in a later step requires executing compensating transactions in reverse order to semantically undo the effects of previously completed local steps (e.g., refunding a payment if flight booking fails). These compensations must be strictly idempotent to handle network retries safely.
The WAL records all data modifications sequentially to non-volatile storage before dirty buffer pool pages are written to disk. During an unexpected crash or power outage, the database recovery engine reads the WAL, performs REDO passes to reapply committed transactions that had not yet hit data files, and UNDO passes to roll back uncommitted transactions, ensuring durability and atomicity.
Eventually consistent systems allow data replicas to diverge temporarily (soft state) to maintain high availability during network partitions or high write loads. Asynchronous replication streams propagate updates across nodes at different rates, meaning clients reading from different replicas may temporarily observe stale data until background anti-entropy mechanisms converge the state.
NewSQL databases combine relational ACID guarantees (such as serializable transactions and SQL support) with distributed architectures (like sharding and consensus protocols such as Raft or Paxos). By leveraging synchronized atomic clocks (e.g., Google TrueTime) or distributed concurrency control, they provide external consistency across globally distributed cluster nodes without sacrificing horizontal scalability.
The Outbox Pattern solves the dual-write problem—the inability to atomically write to a database and publish a message to a broker simultaneously. By writing business data changes and an outbox event message within the same local database transaction, and using a separate CDC tailer to publish events, it guarantees reliable at-least-once message delivery without distributed locks.
AI Prep covers AI Agents, Generative AI, ML Fundamentals, NLP & LLMs and a lot more, with adaptive tests and daily challenges. Fully offline on Android. Free to try, one-time unlock for lifetime access.