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.)
A.
The session is terminated after receiving the error and JIM can continue with his transaction.
B.
SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.
C.
The session is rolled back after receiving the error and JIM can continue with his transaction.
D.
SCOTT has to reexecute the last command in the transaction after he commits the transaction.
b is incorrect, Oracle will detect the deadlock and roll back one of these statements automatically.
c is incorrect, oracle only rollback statement not session.
only d is correct.
something is wrong here.
I think is Jim the one that should commit to allow Scott to continue.
yes, something is wrong. because
“ORA-00060 error indicates that a dead lock occurred due to a resource contention with another session and Oracle rolled back your current statement to resolve the dead lock. The other session can proceed further as usual . Your current sessions rolled backed statements needs to be resubmitted for the execution after the required resources are available.”
http://oracle-error.blogspot.nl/2008/10/ora-00060-deadlock-detected-while_20.html
b,d are correct
b,d correct
B and D are correct. To get a deadlock you need to have the following sequence of events:
JIM update Record1
SCOTT update Record2
JIM update Record 2 as well and his waiting for lock held by scott
SCOTT try to update record 1 he gets the ora-00060 The update on record 1 fails
At this stage we still have
JIM with a lock on record1 and waiting on recor2
SCOTT with a lock on Record2
So SCOTT need to perform a commit or rollback to release lock on Record2 and allow JIM to continue (Answer B)
SCOTT need to re-run the last command on Record1 (Answer D)
easy to verify with two SQL+ sessions actually
I agree with Philippe.