Oracle server automatically detects dead locks. When a dead lock is detected, Oracle server will select a victim transaction, and fail its statement that is blocked in the dead lock to break the dead lock. The tutorial exercise below shows you an example of statements failed by Oracle server because of dead locks:

(session 1)

SQL> connect HR/globalguideline

SQL> UPDATE ggl_links

SET notes='Session 1'

WHERE id=101;

1 row updated.

       (session 2)

       SQL> connect HR/globalguideline

       SQL> UPDATE ggl_links 

       SET notes='Session 2' 

       WHERE id=110;

       1 row updated.

       SQL> UPDATE ggl_links 

       SET notes='Session 2' 

       WHERE id=101;

       (blocked by lock id=101)

SQL> UPDATE ggl_links 

 SET notes='Session 1' 

 WHERE id=110;

(blocked by lock on row id=110)

       ORA-00060: deadlock 

       detected w