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
You find out that a session opened by user JIM has a transaction that caused the deadlock. Which
two statements are true about SCOTT’s session in this scenario?
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 re-execute the last command in the transaction after he commits the transaction.
BD
CD
BD
BD
C is wrong because its statement which is rolled back not the session.
B is correct as after statement is failed due to deadlock follow below –
1. Issue COMMIT/ROLLBACK to allow other session to continue by releasing the locks.
2. Issue the failed statement again to do the work in new transaction.
C,D
Once user session get ORA-60, its implicitly rollbacked. Explicit Commit/Rollback is not required to continue other user session.
C ans should have stmt instead of session. still its best suited
Would also think C, D are the “best” answers.
Because if SCOTT receives the error, his transaction already got rolled back. He does not have the possibility to decide if he wants to COMMIT or to ROLLBACK.
Answer : BD
Learn :
https://www.youtube.com/watch?v=JitJRb5x9vI
ANSWER: C D
Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock.
IT IS NOT NECESSARY EXECUTE MANUAL COMMIT OR ROLLBACK , ORACLE MAKE IT AUTOMATICALLY
https://oracle-base.com/articles/misc/deadlocks
Oracle automatically detects and resolves deadlocks by rolling back the whole “transaction”.
Let me demonstrate ……
using an example similar to that provided in
https://oracle-base.com/articles/misc/deadlocks
(Many thanks to our good and respected friends at oracle-base)
I won’t repeat all what they used, the only difference is that I would run the following procedures to check what exactly is rolled back in the session that detects the deadlock.
— Run in session 1.
BEGIN
update deadlock_1 set id = 2;
DBMS_LOCK.sleep(30);
update deadlock_2 set id = 2;
commit;
END;
/
— Run in session 2.
BEGIN
update deadlock_2 set id = 3;
DBMS_LOCK.sleep(30);
update deadlock_1 set id = 3;
commit;
END;
/
Session 1 returned
PL/SQL procedure successfully completed.
SQL> select * from deadlock_1;
ID
———-
2
SQL> select * from deadlock_2;
ID
———-
2
Session 2 returned
BEGIN
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 8
SQL> select * from deadlock_1;
ID
———-
2
SQL> select * from deadlock_2;
ID
———-
2
Oracle automatically rolls back the entire transaction that detected the deadlock. An explicit Commit/Rollback is not required to continue session that rolls back.
One other observation …
Only one session in the deadlock actually gets the deadlock error, but either session could get the error. (source … oracle docs …which also has similar examples … see Table 9-5 Deadlock Example in http://docs.oracle.com/cd/E25054_01/server.1111/e25789/consist.htm)