English
Menu

Database Basics

Among the isolation levels defined by the SQL standard, which correctly lists the phenomena that can occur under READ COMMITTED?

  1. ANone of dirty reads, non-repeatable reads or phantom reads can occur
  2. BDirty reads cannot occur, but non-repeatable reads and phantom reads can
  3. CDirty reads and non-repeatable reads cannot occur, but phantom reads can
Show answer and explanation

Answer: B. Dirty reads cannot occur, but non-repeatable reads and phantom reads can

The SQL standard defines four isolation levels, in order of how few phenomena they prevent: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Under READ UNCOMMITTED, all three phenomena can occur. READ COMMITTED reads only committed data and so prevents dirty reads, but because each read sees the committed value at that moment, non-repeatable reads and phantom reads can occur. REPEATABLE READ additionally prevents non-repeatable reads, but phantom reads can still occur. SERIALIZABLE prevents all three and guarantees the same result as executing the transactions one at a time in some order. The higher the isolation level, the more lock waits and retries there are and the less work can be processed concurrently, so the level is chosen to suit the application. In real DBMSs, the default isolation level, and the phenomena each level actually prevents, may be stronger than the standard's minimum requirements.

  • A. The level at which none of the three occurs is SERIALIZABLE.
  • B. Correct. READ COMMITTED prevents only dirty reads.
  • C. This describes REPEATABLE READ.
Practice this topic Back to topic

Related calculators

SQL PracticeRun SQL in your browser and check the results, with 10 practice exercises that grade your answers

Books on Database Basics Ad

As an Amazon Associate I earn from qualifying purchases.

More Database Basics questions

  1. Transaction T1 holds a shared lock on a row and is reading it. T2 requests a shared lock and T3 requests an exclusive lock on the same row. Which is correct for the period until T1 releases its lock?
  2. Which correctly describes two-phase locking (2PL), used for concurrency control of transactions?
  3. Transaction T1 took an exclusive lock on row A and then requested a lock on row B, while T2 took an exclusive lock on row B and then requested a lock on row A, so neither can proceed. What does a DBMS typically do when it detects this situation?
  4. When updating data, many DBMSs record the changes in a log (journal) and write that log to nonvolatile storage before the database itself (WAL: write-ahead logging). What is the main reason for this approach?
  5. There are transactions T1 to T5, and one checkpoint was taken before a failure occurred. T1 started and committed before the checkpoint. T2 and T3 started before the checkpoint; T2 committed after the checkpoint but before the failure, and T3 had not committed at the time of the failure. T4 and T5 started after the checkpoint; T4 committed before the failure, and T5 had not committed. In recovery after the failure, which transactions must be rolled forward using the log?