Examine these statements issued from Session 1 which execute successfully: Now, examine this statement issued from Session 2: What is the outcome of the update statement in Session 2?

Question

Options

• AThe row will be updated immediately.
• BThe statement will wait for the transaction in Session 1 to finish.
• CA deadlock will occur.
• DThe transaction in Session 1 will be rolled back automatically.

• A
  19% (12)
• B
  10% (6)
• C
  5% (3)
• D
  67% (42)

Explanation

Option D is correct because the scenario describes a deadlock: Session 1 is holding a lock that Session 2 needs, while Session 2 holds a lock that Session 1 needs. Oracle's deadlock detection automatically resolves this by rolling back one of the blocking transactions - in this case, Session 1's - and raising an ORA-00060: deadlock detected error, so execution can continue.

Why the distractors are wrong:

• A is wrong because the row is already locked by Session 1; Session 2 cannot update it immediately.
• B is wrong because mutual waiting is what causes a deadlock - if each session holds what the other needs, simply waiting will never resolve the situation.
• C is a trap: a deadlock condition does exist, but Oracle doesn't leave it unresolved. Saying "a deadlock will occur" implies it just hangs, when in reality Oracle automatically intervenes.

Memory tip: Think of it as Oracle being the referee - when two sessions are stuck in a circular wait, Oracle blows the whistle and forces one player (the transaction it detects as the "cause") off the field. The key phrase to remember is "Oracle detects and resolves deadlocks automatically by rolling back one statement/transaction."

No community discussion yet for this question.