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.
SCOTT has to reexecute the last command in the transaction after he commits the transaction.
B.
SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.
C.
It is terminated after receiving the error and JIM can continue with his transaction.
D.
The transaction in it is terminated after receiving the error and JIM can continue with his transaction.
Why, 3 and 4 not correct, Since Oracle automaticall handle deadlocks, why SCOTT has to COMMIT or ROLLBACk the transactions ?
At the moment the deadlock occurs we have JIM waiting because of a lock set on Record 1 (by uncommited trans) by SCOTT and SCOTT waiting because of lock set on Record 2 (by uncommited trans) by JIM.
When SCOTT is chosen as a victim and get the ora-00060 he still have a lock on record 1 (and JIM his waiting) so SCOTT has to commit or ROLLBACK his transaction (answer B) and he has to re-execute the last command (ANSWER A)