Which two statements are true regarding the session of SCOTT in this scenario?

Eamon

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)

jean

Since any conversation can not complete the transaction, nor do the conflict resolution, JIM CANNOT continue with his transaction.