Examine this statement that execute successfully in an interactive session: The user running this session now goes to lunch for an hour. Now, examine these statements executed independently in…

Question

Exhibit

Options

• A5
• B1
• C2
• D0
• E3
• F4

• A
  8% (2)
• B
  65% (17)
• C
  19% (5)
• D
  4% (1)
• F
  4% (1)

Explanation

Only one statement completes because Session 0 holds an uncommitted transaction, which means it retains all its row-level (and potentially table-level) locks for the entire duration of the lunch break. In Oracle, uncommitted DML locks the affected rows, so any other session attempting to modify those same rows - or execute DDL on the locked object - will be blocked and simply wait indefinitely. The single statement that can complete is typically a plain SELECT (without FOR UPDATE), because Oracle's multi-version read consistency allows readers to see the pre-change data without waiting for a lock.

The distractors (A=5, C=2, E=3, F=4) are wrong because they imply multiple sessions can bypass the lock; in reality, only non-locking reads are immune. D=0 is wrong because at least one statement (the SELECT) does not need to acquire any lock and completes immediately.

Memory tip: Think "Readers never block in Oracle, but everyone else waits for COMMIT." If a session goes to lunch without committing, only pure readers get through - everyone else is stuck at the door.

No community discussion yet for this question.