Lets first understand the deadlock in general -
What is DeadLock
A deadlock is a situation in which two tasks sharing the same resource are effectively preventing each other from accessing the resource, resulting in both tasks ceasing to function. Here we can assume task as thread or process -
Example
Task 1 requests resource A and receives it.
Task 2 requests resource B and receives it.
Task 1 requests resource B and is queued up, pending the release of B.
Task 2 requests resource A and is queued up, pending the release of A.
What is Deadlock in Oracle - (Src: http://www.oratechinfo.co.uk/deadlocks.html )
A deadlock is the situation where you have two, or more, Oracle "sessions" competing for mutually locked resources. Oracle deals with deadlocks pretty much immediately by raising an exception (ORA-00060) in one of the sessions.
There are a few reasons why your application may experience deadlocks, most of which are about application design. However, there are a few situations when, due to certain architectural design decisions, you may experience deadlocks simply due to the internal mechanisms of Oracle itself.
A "classic" deadlock
If session 1 is locking row 1, session 2 locks row 2, then session 1 attempts to lock row 2 (which will block since session 2 has the lock on that row), and then session 2 attempts to lock row 1 (which will block since session 1 has the lock on that row), then session 1 is waiting for session 2, and session 2 is waiting on session 1, which of course will never be resolved.
Here's an actual Oracle example. First of all, setup the environment :
SQL> CREATE TABLE t ( a varchar2(10) );
Table created.
SQL> insert into t values ( 'x' );
1 row created.
SQL> insert into t values ( 'y' );
1 row created.
SQL> commit;
Commit complete.
Now, session 1 attempts to delete row 'x', but doesn't commit :
SESS1> delete from t where a = 'x';
1 row deleted.
Okay, now session 2 attempts to delete row 'y', again, not committing :
SESS2> delete from t where a = 'y';
1 row deleted.
Now, since session 2 has not committed the delete against row 'y', session 1 can still "see" it (and attempt to delete it) :
SESS1> delete from t where a = 'y';
However, this will be blocked by session 2 (and hence wait), since row 'y' is locked, so session 1 is holding the lock on row 'x' but is waiting for row 'y'. Now, session 2 attempts to do it the "other way around", i.e. attempting to delete row 'x', while it retains the lock on row 'y' :
SESS2> delete from t where a = 'x';
this will be blocked by session 1 (and hence wait). So, the situation is tied, session 2 is waiting for session 1 and session 1 is waiting for session 2.
So, in reality, what happens in this situation? Well, obviously deadlocks just simply can't be allowed to occur, it's basically an infinite loop, the situation can't be resolved. So, Oracle steps in, automatically detecting the deadlock and resolving it by causing one of the sessions (can't actually guarantee which one) to receive an ORA-00060 error, i.e.
ORA-00060: deadlock detected while waiting for resource
