First of all, I have following table
|Figure 01 - Table NAMES|
At this point, I started two sessions,: session 01, in red, session 02 in green. Both make an update on two different rows
|Picture 02 - Session 01, update the row with ID=1|
|Picture 03 - Session 02, update the row with ID=10|
Now, without committing the previous operation, the red guy (session 01) update the row with ID=10
|Picture 04 - Session 01, update ID=10 and wait|
Because the row with ID=10 is already updated by the green guy (session 02 didn't commit), the session 01 has to wait. After, the green guy, update the row with ID=1
|Picture 05 - Session 02, update ID=1 and wait|
So, as you can see session 01 and session 02, are locking each other. Because of this stalemate, Oracle resolves it for everyone. It rollback the last statement of who (the session 01, in this case) started the deadlock.
|Picture 06 - Session 01 will rollback the last statement|
The session 01, the one that started the deadlock, is the victim.
|Picture 07 - Extract of the trace file (1)|
|Picture 08 - Extract of the trace file (2)|
What happens inside the db after rollback due to deadlock? I check V$SESSION and V$TRANSACTION, In order to see the active sessions. Also I check the V$SQL tho check the current statement.
|Picture 09 - Query the data dictionary|
Joining V$SESSION with V$SQL, you can see that the session 01 is just running the UPDATE with ID=1 (actually he run the "SELECT FOR UPDATE", that is, his first statement).
|Picture 10 - V$LOCK|
Both sessions make an exclusive lock (IMODE=6), but the green guy also requested an exclusive lock (REQUEST=6). As you can see, the REQUEST=6 of the session 02 is made on the same resource of the IMODE=6 made by session 01: ID1=262284 and ID2=3375 These values are the same in V$SESSION (P2 and P3).
In V$LOCK_TYPE view you can see the meaning of the LOCK TYPE.
|Picture 11 - Explaining the lock|
|Picture 12 - Doc ID 29787.1|
Pictures 07 and 08, show us an extract from a trace file of the Oracle 12c. The output of the previous version is different
|Picture 13 - The alert.log output in 11c is different than 12c|
In the alert log, you can read explicitally the "Deadlock detection". In order to understand the old output (Picture 12), you can read the Doc ID 62365.1 on MOS.
|Picture 14 - Extract from Doc ID 62365.1|