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.
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)
Deadlock is a special example of locking conflicts. Two or more sessions wait for data from the other side of the lock, a deadlock occurs.
Because each session is waiting for another session to release the lock, so any conversation can not complete the transaction, nor do the conflict resolution.
Appropriate approach to correct this error is to commit or rollback, to do so would release any other locked in a session so that another session can continue to complete its transaction.
Scott must be committed or rolled back, in order to correct the detected deadlock errors.
If you commit, you must resubmit the second update to complete the transaction. If you perform a rollback, it must resubmit both statements to complete the transaction.
Since any conversation can not complete the transaction, nor do the conflict resolution, JIM CANNOT continue with his transaction.
JIM was there first, causing the deadlock to SCOTT. And it won’t automatically rollback, nor automically end the session.
We must manually rollback / end the transaction, so JIM can continue first. Then SCOTT may reissue the transaction.