A common deadlock in ORA-00060

In Oracle Database deadlock, the database will report the ORA-00060 error code, this deadlock phenomenon is usually caused by application logic design errors, and o

In Oracle Database deadlock, the database will report the ORA-00060 error code, this deadlock phenomenon is usually caused by application logic design errors, and o

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 the alert Log:
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.