Questions: Transaction Isolation Levels: READ UNCOMMITTED to SERIALIZABLE

Question 1

Transaction T1 reads a row, then Transaction T2 modifies that row and commits, then T1 reads the same row again within its transaction and gets a different value. What anomaly is this, and what isolation level prevents it?

• A. Dirty read; READ COMMITTED prevents it
• B. Non-repeatable read; REPEATABLE READ prevents it
• C. Phantom read; SERIALIZABLE prevents it
• D. Dirty read; REPEATABLE READ prevents it

Answer: (B)

A non-repeatable read occurs when a transaction reads the same row twice and gets different values because another committed transaction modified it in between. READ COMMITTED prevents dirty reads (reads of uncommitted data) but still allows non-repeatable reads. REPEATABLE READ prevents this specific anomaly by holding read locks on rows already read. A phantom read, by contrast, involves new rows appearing in a query's result set — not existing rows changing.

Question 2

A dashboard query runs at READ UNCOMMITTED to display near-real-time totals. A bulk-insert transaction begins, writes 50,000 rows, and then rolls back due to an error. What risk did the dashboard face?

• A. The dashboard would see stale data from before the bulk insert started
• B. The dashboard may have displayed totals that included the 50,000 rows that officially never existed after the rollback
• C. The dashboard would be blocked and unable to query while the insert was in progress
• D. The dashboard's query would fail with a lock timeout error

Answer: (B)

READ UNCOMMITTED allows dirty reads — the dashboard can see uncommitted data from in-progress transactions. If that transaction rolls back, the dashboard has acted on data that never officially existed. This is the defining danger of READ UNCOMMITTED and why it is rarely used in production outside of scenarios (like approximate monitoring) where acting on rolled-back data has no real consequences.

Question 3

SERIALIZABLE isolation guarantees that concurrent transactions can seldom overlap in time — they physically execute one at a time.

• T. True
• F. False

Answer: False

SERIALIZABLE guarantees that the *result* is equivalent to some serial (sequential) execution — but the transactions may still run concurrently. The database ensures the outcome is indistinguishable from some sequential ordering, using techniques like locking or multi-version concurrency control. Physical sequentiality is not required and would be enormously inefficient. The guarantee is about logical equivalence, not physical ordering.

Question 4

At READ COMMITTED isolation, a transaction may read different values for the same row if it queries that row twice within a single transaction.

• T. True
• F. False

Answer: True

READ COMMITTED guarantees you only see committed data, but only at the statement level — not for the entire transaction duration. If T1 reads a row, then T2 modifies and commits it, then T1 reads the row again, T1 sees the new value. This is a non-repeatable read, and it is explicitly permitted at READ COMMITTED. REPEATABLE READ closes this gap by holding read locks (or using snapshot isolation) for the duration of the transaction.

Question 5

Why might a financial application choose REPEATABLE READ or SERIALIZABLE rather than the READ COMMITTED default, even though higher isolation reduces throughput?

Model answer: Financial calculations often require consistent reads across multiple statements within a transaction — for example, computing a balance that is read, then used to validate a transfer, then updated. At READ COMMITTED, another transaction could modify the balance between those steps, causing the calculation to be based on stale or inconsistent data. The cost of acting on anomalous data (incorrect transfers, double-spending) outweighs the performance cost of higher isolation.

The key principle is 'choose the weakest isolation level your correctness requirements can tolerate.' For most read-heavy web applications, READ COMMITTED is fine — anomalies are rare and the consequences are minor. For systems where mid-transaction data changes could cause incorrect monetary decisions, the correctness requirement is stronger and justifies the performance cost of REPEATABLE READ or SERIALIZABLE.