Deadlocks in Oracle

Deadlock
==========

A deadlock occurs when two or more sessions are waiting for data locked by each other, resulting in all the sessions being blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock

session 1
=============

[oracle@pri ~]$ sqlplus hr/hr

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Feb 27 19:01:57 2019
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Wed Feb 27 2019 18:34:28 +05:30

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 Sarita
         2 Sarita
         3 Guggu

SQL> update test set name='Sarita' where id=3;

1 row updated.

SQL> update test set name='Guggu'  where id=1;



session 2
------------

[oracle@pri ~]$ sqlplus hr/hr

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Feb 27 19:02:03 2019
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Wed Feb 27 2019 19:01:58 +05:30

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 Sarita
         2 Sarita
         3 Guggu

SQL>  update test set name='Guggu'  where id=1;

1 row updated.

SQL> update test set name='Sarita' where id=3;
update test set name='Sarita' where id=3
                *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


SQL>

Query
---------


SELECT /*+ ORDERED */
  S.SID,
  S.USERNAME,
  S.PROGRAM,
  S.STATUS,
  SW.EVENT,
  SW.WAIT_TIME WT,
  SW.STATE,
  SW.SECONDS_IN_WAIT S_I_W,
  S.SQL_ID,
  S.SQL_ADDRESS,
  S.SQL_HASH_VALUE,
  S.SQL_CHILD_NUMBER,
  S.ROW_WAIT_OBJ# OBJ#,
  S.ROW_WAIT_FILE# FILE#,
  S.ROW_WAIT_BLOCK# BLOCK#,
  S.ROW_WAIT_ROW# ROW#,
  SW.P1,
  SW.P2,
  SW.P3
FROM
  V$SESSION_WAIT SW,
  V$SESSION S
WHERE
  S.USERNAME IS NOT NULL
  AND SW.SID=S.SID
  AND SW.EVENT NOT LIKE '%SQL*Net%'
  AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');



SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
  id1, id2, lmode, request, type
 FROM V$LOCK
WHERE (id1, id2, type) IN
 (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;