nerdexam
Oracle

1Z0-052 · Question #93

The session of user SCOTT receives the following error after executing an UPDATE command on the EMP table: ERROR at line 1: ORA-00060: deadlock detected while waiting for resource On investigation…

The correct answer is B. SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction. D. SCOTT has to reexecute the last command in the transaction after he commits the transaction. When Oracle detects a deadlock, it automatically rolls back only the offending statement (not the entire transaction), leaving SCOTT's session active with remaining locks still held. SCOTT must commit or rollback the remaining transaction and reexecute the failed statement.

Managing Data and Concurrency

Question

The session of user SCOTT receives the following error after executing an UPDATE command on the EMP table:

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource On investigation, you find that a session opened by user JIM has a transaction that caused the deadlock. Which two statements are true regarding the session of SCOTT in this scenario? (Choose two.)

Options

  • AThe session is terminated after receiving the error and JIM can continue with his transaction.
  • BSCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.
  • CThe session is rolled back after receiving the error and JIM can continue with his transaction.
  • DSCOTT has to reexecute the last command in the transaction after he commits the transaction.

How the community answered

(23 responses)
  • A
    13% (3)
  • B
    78% (18)
  • C
    9% (2)

Why each option

When Oracle detects a deadlock, it automatically rolls back only the offending statement (not the entire transaction), leaving SCOTT's session active with remaining locks still held. SCOTT must commit or rollback the remaining transaction and reexecute the failed statement.

AThe session is terminated after receiving the error and JIM can continue with his transaction.

Oracle does not terminate the session on a deadlock - it only rolls back the single statement that was the deadlock victim, leaving the session and its open transaction intact.

BSCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.Correct

After a deadlock, Oracle rolls back only SCOTT's last statement but leaves his transaction open, meaning he still holds locks on rows modified earlier in the transaction. SCOTT must issue a COMMIT or ROLLBACK to release those remaining locks so JIM's transaction can proceed without being blocked.

CThe session is rolled back after receiving the error and JIM can continue with his transaction.

Only the individual statement that caused the deadlock is rolled back automatically by Oracle, not the entire transaction; SCOTT's session and all other statements in his transaction remain active.

DSCOTT has to reexecute the last command in the transaction after he commits the transaction.Correct

Because Oracle only rolled back the specific UPDATE statement that caused the deadlock and not the entire transaction, SCOTT must reexecute that statement manually once he decides how to resolve the transaction.

Concept tested: Oracle deadlock detection and statement-level rollback

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html#GUID-C1971E9B-849A-4634-9575-4F8FAD697750

Topics

#deadlock#ORA-00060#transaction management#concurrency

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-052 Practice