Transactions & Isolation Levels
ACID guarantees, and the isolation-level trade-off between correctness and concurrent throughput.
Try answering in your head first, then click a question to check the model answer.
Q1.Explain the difference between a dirty read and a non-repeatable read.(show answer)
A dirty read sees another transaction's uncommitted changes, which might be rolled back and never actually happen. A non-repeatable read sees committed data, but a value read once changes if read again within the same transaction, because another transaction committed a change to it in between — the data was always valid, just not stable across reads.
Q2.Why is Serializable isolation the strongest but least commonly used by default?(show answer)
Serializable guarantees transactions behave exactly as if run one after another with no overlap — eliminating every concurrency anomaly. But achieving that typically requires more locking or conflict detection, which reduces throughput and increases the chance of transactions being aborted and retried under contention, so most databases default to Read Committed as a practical balance.
Q3.What is a "phantom read," and which isolation level is specifically required to prevent it in the standard SQL definition?(show answer)
A phantom read occurs when a transaction re-runs a range query (e.g. WHERE age > 30) and sees new rows that another transaction inserted and committed in between — rows that "weren't there" the first time. Per the SQL standard, only Serializable is guaranteed to prevent it (though some databases, like PostgreSQL, prevent it at Repeatable Read too via their specific implementation).
Q4.Why would an application deliberately choose a weaker isolation level (like Read Committed) instead of always using Serializable?(show answer)
Weaker isolation levels generally allow more concurrency and higher throughput, since they require less locking/blocking between transactions. Many applications don't actually need Serializable's strict guarantees for most of their queries — the anomalies it prevents (like phantom reads) may not matter for the specific business logic, so using it everywhere would be paying a real performance cost for protection that isn't needed.