A common deadlock in ORA-00060

If there is a deadlock in the Oracle database, the database will report the error code of the ORA-00060, this deadlock phenomenon is usually caused by the application logic design error, and the oracle database design has nothing to do, now we use an experiment to simulate a deadlock.
 
Start two sessions and execute the following update order
 
Session 1: update the fields whose employee_id is 198.
HR @ prod> update employees set first_name = 'cj 'where employee_id = 198;
 
1 row updated.
 
Session 2: update the fields whose employee_id is 200.
HR @ prod> update employees set first_name = 'hh 'where employee_id = 200;
 
1 row updated.
 
Session 1: Execute the update of the field whose employee_id is 200. At this time, the statement has been hang and needs to wait for session 2 to send a commit or rollback action.
HR @ prod> update employees set first_name = 'cj 'where employee_id = 200;

Session 2: Once an update is executed, Session 1 will report an error immediately.
HR @ prod> update employees set first_name = 'sdf 'where Fig = 198;
 
Update employees set first_name = 'cj 'where employee_id = 200
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
 
Session 2 is still hang. An error is returned when querying alert logs:
ORA-00060: Deadlock detected. More info in file/u01/app/oracle/admin/prod/udump/prod_ora_4273.trc.
 
The HOLDING_SESSION field in the dba_blockers table can be used to query the ID of the hang session.
SYS @ prod> select * from dba_blockers;
 
HOLDING_SESSION
---------------
159
 
Use the v $ session view to obtain the sid and serial of the hang session #
SYS @ prod> select sid, serial #, username from v $ session where sid in
2 (select blocking_session from v $ session );
 
Sid serial # USERNAME
--------------------------------------------------
159 HR
 
Find the hang session and run the alter system command to kill the corresponding session:
SYS @ prod> alter system kill session '2017, 5' immediate;
 
System altered.
 
After execution, sessions in session 1 are automatically killed.
Session 1:
HR @ prod> select employee_id, first_name from employees where rownum
Select employee_id, first_name from employees where rownum
*
ERROR at line 1:
ORA-03135: connection lost contact
 
The query in session 2 shows that the change in session 2 takes effect.
HR @ prod> select employee_id, first_name from employees where rownum
 
EMPLOYEE_ID FIRST_NAME
-------------------------------
198 sdf
199 Douglas
200 hh
201 Michael
202 Pat
203 Susan
204 Hermann
205 Shelley
206 William
100 Steven
 
10 rows selected.
In fact, Oracle will unlock it after a period of time when a deadlock occurs. In this case, the following information is recorded in the alert Log:
ORA-00060: Deadlock detected. More info in file/u01/app/oracle/admin/ORCL/udump/orcl_ora_3173.trc.