Deadlocks

This was tested in Oracle 11.2.

Imagine you have two sessions running concurrently called Session A and Session B. Session A cannot continue because Session B has something it needs. Session B needs something which Session A is using so it cannot do anything either. This is called a deadlock.

This is not really an Oracle problem. It is caused by poor application design.

Oracle detects deadlocks and issues an ORA-00060 to one of the sessions. It then cancels an SQL statement to allow the others to finish.

You should design your applications in such a way that the chances of having a deadlock are minimised. If you do get a deadlock, your application should detect it and repeat the failing statement. If this is not possible, the application must fail tidily.

The table below has two columns. The left hand column shows SQL*Plus Session A and the right hand column shows SQL*Plus Session B. Both sessions are updating the same rows in the EMP table at the same time. This is what I meant by poor application design. Session A is updating salaries and Session B is updating managers. The updates produce a deadlock. Oracle detects it in Session A. After both sessions have committed their changes, you can see that the only update which has been lost is the one directly affected by the deadlock:  

Session A SQL> show user USER is "ANDREW" SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1000.00 M01A E02  2000.00 M02A SQL> update emp   2  set salary = salary * 1.1   3  where id = 'E01'; 1 row updated. SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1100.00 M01A E02  2000.00 M02A SQL> Go to session B -> SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1100.00 M01A E02  2000.00 M02A SQL> update emp   2  set salary = salary * 1.1   3  where id = 'E02'; This session is now locked as Session B has not finished updating employee E02. Go to session B -> The deadlock is detected and the 2nd update fails: update emp        * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource SQL> Go to session B -> SQL> commit; Commit complete. SQL> Go to session B -> SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1100.00 M01B E02  2000.00 M02B SQL>

Session B SQL> show user USER is "ANDREW" SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1000.00 M01A E02  2000.00 M02A SQL> update emp   2  set manager = 'M02B'   3  where id = 'E02'; 1 row updated. SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1000.00 M01A E02  2000.00 M02B SQL> <- Go to session A. SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1000.00 M01A E02  2000.00 M02B SQL> update emp   2  set manager = 'M01B'   3  where id = 'E01'; This sets up the deadlock as the sessions are now waiting on each other. <- Go to session A. This session is still locked. <- Go to session A. Now that Session A has committed, Session B can do its update. 1 row updated. SQL> commit; Commit complete. SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1100.00 M01B E02  2000.00 M02B SQL> <- Go to session A.