DBMS Interview Questions and Answers

Two-phase commit, 2PC, is an atomic commit protocol for transactions spanning multiple databases or resource managers.

Phase one, prepare: the coordinator asks every participant to prepare. Each does the work, writes it to a durable log, acquires locks and votes yes or no.

Phase two, commit: if all voted yes, the coordinator logs the decision and tells everyone to commit; if any voted no, it tells everyone to roll back.

coordinator -> prepare -> all vote yes
            -> commit  -> all commit

Atomicity holds even across a crash, because a participant that voted yes can recover from its log and follow the coordinator's decision. The main problems are blocking: if the coordinator fails after prepare, participants hold locks and wait for it to recover. It is also synchronous and slow.

Consequently, modern distributed systems often prefer consensus-based replication or sagas, which trade isolation for availability and use compensating actions instead of a global atomic commit.

Scaling relational databases follows a progression, because a single node eventually hits CPU, memory or I/O limits.

1. Optimise first: fix queries, add indexes, tune the buffer pool and connection pooling, and archive old data.
2. Read scaling: add replicas and route reads to them, accepting replication lag. Use a cache such as Redis for hot data.
3. Vertical scaling: more RAM and faster disks, such as NVMe, often beat complex sharding and buy time.
4. Partition large tables by range or hash to keep working sets manageable.
5. Shard: split data across nodes by a shard key so writes scale. This makes cross-shard joins, transactions and unique constraints hard.

app -> primary (writes)
         | replication
         v
     replicas (reads)   + cache

Choose the shard key for even distribution and locality, and plan resharding and rebalancing. Some workloads are better served by a NoSQL or NewSQL store, or by CQRS with separate read and write models.